PauerSchell Bernd joined SQLPSX

Hello database and PowerShell folks,

some days ago Chad Miller released SQLIse and soon afterwards Steven Murawski joined the SQLPSX project.

From the SQLIse project you can learn a lot about using PowerShell modules and building GUI with WPK. SQLIse uses System.Data.SqlClient, that is Ado.NET to access databases.

In the past I did some unpublished work using System.Data.OracleClient aka ODL on the database side.
In the old CTP3 days I joined the PowerShell ISE-Cream project, which did suffer a bit by the incompatible changes, which came with the PowerShell V2 RTM version.

But motivated by the execellent postings of Ravikanth Chaganti about PowerShell remoting, we began to revive that project and I learned a lot about working with multiple tabs (= PowerShell Sessions) and tuning the addons-menu.

Now I joined SQLPSX too.

I'm going to add the Connect and Invoke-ExecuteSql functions adapted to Oracle the next days.

The interface to procedure calls will take a bit longer as the coresponding SQL-Server functions aren't yet called by menu in the current version.

And as far as I know there is nothing comparable to SMO for Oracle.

One of the features I miss most on the current Oracle tools, is the fact, that they do help to display multiline fields in a wellformatted manner. Grid views tend to show only the first line and text views loose alignment. With PowerShell's Format-List cmdlet and Steves 'Result to variable' option, we can do better.

That's for now database folks, I have to do some PowerShell basics.

Bernd

And now using the data to generate sql INSERT-statements

Before SQL-Serve 2008 sql bulks of SQL Insert-Statements are so redundant, that they are not readable.
Of corse, if you are too clever you ommit the column list after INSERT INTO.
Try it and learn it the hard way.

One of my common tasks is generating Insert Statements. Perhaps this way:

$list = (             
    ( 1, 'PS',      'PowerShell is the most inovative scripting language'),            
    ( 2, 'Python',  'has the most concise formating'),            
    ( 3, 'Algol60', 'the mother of quoted identifiers'),            
    ( 4, 'Algol68', 'the root of array slicing'),            
    ( 5, 'c#',      'if only you were caseinsentitiv casesensitivity is ORACLE''s greates fault'),            
    ( 6, 'SQL',     'has an Insert syntax which is worse than Street Basic''s Data-Statement 
                        (where we put the assembler code you remember)')             
)            
            
foreach ($item in $list)            
{            
    $id, $code, $note = $item            
    $note = $note -replace "'", "''"  # you have to double embedded apostrophes            
    "insert into Computer_Languages ( Id, Code, Note) values ( $id, '$code', '$note')"            
}

poshcodegen

The sql-server part of poshcodegen is nearly complete. And I'm using it in daily work.

The oracle part is a bit harder. Revisiting some old code I stumbled too often about #ToDo Comments. So its going on a little slower than expected.

Thinking that you prefer viewing at some real code, I post one of the Orcale test functions in Poshcode. Perhaps someone finds it there, just as I found the orginal http://poshcode.org/1011 from Steven Murawksi.

If your daily job is running ad hoc queries and stored procedure calls against SQL-Server Databases you may find something useful.

If you know how to access another RDBMS please join the project.

If you find a bug please send feedback. This is a complex area and it was too easy to introduce faults. We don't have automatic testing yet, but there is a rather complete set of test stored procedures for SQL-Server and for Oracle.

The second pitfall: too often the functions run perfectly too hours, but starting a fresh PowerShell I get errors. Often this is causes by the need to keep our own connection strings secret.

But now something useful, as far as you use ORCLE:



download | new post

ISE and Read-Host why not read from Commandpane.Editor

I really hope, that in the final PowerShell V2 there is a way to use Read-Host reading from Commandpane.Editor without any Prompt-Dialog.



Without this many interessting Custum Menue Extensions like this one
are slowed down by some additional GUI-Elements.



I do not want to isolate the prompt input from the command history.



I want to be able to clone ISQL/w or SQL Querry Analyzer or 1000 other 'inconceivable' things within ISE.



I want some jobs be done while xx-Studio still is loading.


This refers To Visual-Studio as well as Toad or Power-GUI or SQL-Management Studio.


Dont't panic.



Bernd

New ideas to (mis-?) use ISE

I asked Jeffrey Snover whether ISE is Powershell specific. His answer is very encouraging, but goes not as far as to use it for SQL-scripts.



One of my office duties is manually converting stored procedures form SQL-Server to ORACLE.



Yesterday poetter posted ISE-Snippets. The way to use a prompt dialog to select a choice doesn't please me. I would rather select depending on a selection or the characters to the left of the carret. Isn't that rather similar to the task of tab-completion ?



I find, the best way would be typ some beginning and using some hot-key cycle through the possible snippets.



During the night emerged the idea of using this technic to convert SQL-snippets from SQL-Server to Oracle.

Just tried to load one of my sql-scripts into ISE. Loading ok, no syntax-coloring, that's ok.



I believe todays ISE (CTP3) has the aptitude to fullfil this task.

Concerning the performance, I expect the final V2 release to be powerfull enough.