Invoke SQL script within TCL 1

[birdy1980]

Hi
Is there any inbuilt functionality of TCL where I can invoke and run an .sql script on a selected Target schema and then return control back to the TCL script? SOmething like

set orauser scott
set orapwd tiger
set SID orcl

# execution of SQL script by some logic using above params

I found people asking to use oraTCL or nstcl. But I am unsure whether I need to install these. Is there any inbuilt functionality for this?

I am using OWB's(Oracle warehouse builder) OMB scripting which uses TCL to run its commands.

Any help will be appreciated. Thanks

Birdy

 

[mikrom]

If you are on MS Windows you can access the database using ADO with help of the tcl package tcom

I have a simple example, which access DB2 UDB on IBM iSeries, selects some data from a table and writes them to a CSV-file.
If you are interested I can post the code here.

 

[birdy1980]

Definitely Mikrom

If you could please post what you have.

As I am not a guru in TCL, anything that is related to the solution will help my cause.

Thanks
Birdy

 

[mikrom]

Hi birdy1980,
So, here is my example of using tcom & ADO to process database:

db_example.tcl

[COLOR=#0000ff]##########################################[/color]
[COLOR=#0000ff]# Reading records and fields from database[/color]
[COLOR=#0000ff]##########################################[/color]

[COLOR=#0000ff]### Enter Login-Data[/color]
[COLOR=#804040][b]puts[/b][/color] -nonewline stdout [COLOR=#ff00ff]"AS/400 name : "[/color]; [COLOR=#804040][b]flush[/b][/color] stdout; [COLOR=#804040][b]set[/b][/color] csebk  [[COLOR=#804040][b]gets[/b][/color] stdin] 
[COLOR=#804040][b]puts[/b][/color] -nonewline stdout [COLOR=#ff00ff]"User Id     : "[/color]; [COLOR=#804040][b]flush[/b][/color] stdout; [COLOR=#804040][b]set[/b][/color] userid [[COLOR=#804040][b]gets[/b][/color] stdin]
[COLOR=#0000ff]# Using getpass for getting password[/color]
[COLOR=#804040][b]source[/b][/color] getpass.tcl
[COLOR=#804040][b]set[/b][/color] pass  [getpass [COLOR=#ff00ff]"Password    : "[/color]]
[COLOR=#804040][b]puts[/b][/color] {}

[COLOR=#0000ff]### Using ADO[/color]
[COLOR=#804040][b]package[/b][/color] require tcom

[COLOR=#0000ff]# Connection String[/color]
[COLOR=#804040][b]set[/b][/color] connection_string [COLOR=#ff0000]\[/color]
  [[COLOR=#804040][b]format[/b][/color] [COLOR=#ff00ff]"PROVIDER=IBMDA400;DATA SOURCE=%s; USER ID=%s; PASSWORD=%s"[/color] [COLOR=#ff0000]\[/color]
  [COLOR=#008080]$csebk[/color] [COLOR=#008080]$userid[/color] [COLOR=#008080]$pass[/color]]

[COLOR=#0000ff]# Open ADO connection[/color]
[COLOR=#804040][b]set[/b][/color] connection [::tcom::ref createobject ADODB.Connection]
[COLOR=#008080]$connection[/color] Open [COLOR=#008080]$connection_string[/color] 

[COLOR=#0000ff]# Create an instance of an ADO Recordset[/color]
[COLOR=#804040][b]set[/b][/color] recordset [::tcom::ref createobject ADODB.Recordset]
[COLOR=#804040][b]set[/b][/color] sql_stmt [COLOR=#ff00ff]"select *\[/color]
[COLOR=#ff00ff]              from IBPDDB.P001800V\[/color]
[COLOR=#ff00ff]              where OBEC LIKE '%Mesto%'"[/color]
[COLOR=#0000ff]# Open the recordset, using an SQL statement and the existing ADO connection[/color]
[COLOR=#008080]$recordset[/color] Open [COLOR=#008080]$sql_stmt[/color] [COLOR=#008080]$connection[/color] [COLOR=#ff00ff]1[/color] [COLOR=#ff00ff]3[/color]

[COLOR=#0000ff]# Create and populate an array of field names[/color]
[COLOR=#804040][b]set[/b][/color] fields_count [[[COLOR=#008080]$recordset[/color] Fields] Count]
[COLOR=#804040][b]set[/b][/color] fields {}
tcom::[COLOR=#804040][b]foreach[/b][/color] field [[COLOR=#008080]$recordset[/color] Fields] {
  [COLOR=#804040][b]lappend[/b][/color] fields [[COLOR=#008080]$field[/color] Name]
}

[COLOR=#804040][b]set[/b][/color] csv_file_name [COLOR=#ff00ff]"mesta.csv"[/color]
[COLOR=#0000ff]# Open CSV file[/color]
[COLOR=#804040][b]puts[/b][/color] [[COLOR=#804040][b]format[/b][/color] [COLOR=#ff00ff]"Now writing data to CSV file '%s'.."[/color] [COLOR=#008080]$csv_file_name[/color]]
[COLOR=#804040][b]set[/b][/color] csv_file [[COLOR=#804040][b]open[/b][/color] [COLOR=#008080]$csv_file_name[/color] [COLOR=#ff00ff]"w"[/color]]

[COLOR=#0000ff]# Header line[/color]
[COLOR=#804040][b]set[/b][/color] line [[COLOR=#804040][b]join[/b][/color] [COLOR=#008080]$fields[/color] [COLOR=#ff00ff]";"[/color]]
[COLOR=#804040][b]puts[/b][/color] [COLOR=#008080]$csv_file[/color] [COLOR=#008080]$line[/color]

[COLOR=#0000ff]# Data lines [/color]
[COLOR=#804040][b]while[/b][/color] {![[COLOR=#008080]$recordset[/color] EOF]} {
  [COLOR=#804040][b]set[/b][/color] line_list {}
  tcom::[COLOR=#804040][b]foreach[/b][/color] fld [[COLOR=#008080]$recordset[/color] Fields] {
    [COLOR=#804040][b]lappend[/b][/color] line_list [[COLOR=#008080]$fld[/color] Value]
  }
  [COLOR=#804040][b]set[/b][/color] line [[COLOR=#804040][b]join[/b][/color] [COLOR=#008080]$line_list[/color] [COLOR=#ff00ff]";"[/color]]
  [COLOR=#804040][b]puts[/b][/color] [COLOR=#008080]$csv_file[/color] [COLOR=#008080]$line[/color]
  [COLOR=#008080]$recordset[/color] MoveNext
}
[COLOR=#804040][b]puts[/b][/color] [COLOR=#ff00ff]"..done."[/color]

[COLOR=#0000ff]# Close CSV file[/color]
[COLOR=#804040][b]close[/b][/color] [COLOR=#008080]$csv_file[/color]
[COLOR=#0000ff]# Close RecordSet[/color]
[COLOR=#008080]$recordset[/color] Close
[COLOR=#0000ff]# Close Connection[/color]
[COLOR=#008080]$connection[/color] Close

For entering passwords I have made the following module, which is used in the script above:
getpass.tcl

[COLOR=#804040][b]proc[/b][/color] getpass {prompt} {
[COLOR=#0000ff]  # Required package is Expect. It could be installed using teacup:[/color]
[COLOR=#0000ff]  # teacup install Expect[/color]
  [COLOR=#804040][b]package[/b][/color] require Expect
  [COLOR=#804040][b]set[/b][/color] oldmode [stty -echo -raw]
  send_user [COLOR=#ff00ff]"[/color][COLOR=#008080]$prompt[/color][COLOR=#ff00ff]"[/color]
  [COLOR=#804040][b]set[/b][/color] timeout -[COLOR=#ff00ff]1[/color]
  expect_user -re [COLOR=#ff00ff]"(.*)[/color][COLOR=#6a5acd]\n[/color][COLOR=#ff00ff]"[/color]
  send_user [COLOR=#ff00ff]"[/color][COLOR=#6a5acd]\n[/color][COLOR=#ff00ff]"[/color]
  [COLOR=#804040][b]eval[/b][/color] stty [COLOR=#008080]$oldmode[/color]
  [COLOR=#804040][b]return[/b][/color] [COLOR=#008080]$expect_out[/color]([COLOR=#ff00ff]1[/color],[COLOR=#804040][b]string[/b][/color])
}

Now the session running this script looks like this - You enter server name, login and password and the script generates the CSV output:

C:\_mikrom\Work>tclsh db_example.tcl
AS/400 name : ibptest
User Id     : romanaps
Password    :

Now writing data to CSV file 'mesta.csv'..
..done.

The result is a CSV-file specified by SQL-select:
mesta.csv

OBEC;CAST;OKRES;PSC;POSTA;KODOKR;KRAJ
Košice - Staré Mesto;0;Košice I;040 01;Košice 1;802;KI
Kysucké Nové Mesto;0;Kysucké Nové Mesto;024 01;Kysucké Nové Mesto 1;504;ZI
Kysucké Nové Mesto;0;Kysucké Nové Mesto;024 04;Kysucké Nové Mesto 4;504;ZI
Nitra - Staré Mesto;0;Nitra;949 01;Nitra 1;403;NI
Nové Mesto nad Váhom;0;Nové Mesto n.Váhom;915 01;Nové Mesto nad Váhom;304;TC
Prievidza - Staré Mesto;0;Prievidza;971 01;Prievidza 1;307;TC
Slanské Nové Mesto;0;Košice-okolie;044 18;Kalša;806;KI
Slovenské Nové Mesto;0;Trebišov;076 33;Slovenské Nové Mesto;811;KI

That's all

 

[birdy1980]

Thanks

This is a lot for me to look at given my knowledge of TCL. But definitely helpful. Will try to see what I can do for Unix as I just found out that the test env is unix based here where I work.

I got some suggestions to use OraTCL and nstcl but am not sure whether they are pre requisites.

I tried something very simple like


set ORACLE_SQLPLUS_HOME /u01/app/oracle/product/owbtst/bin

if {[catch {set executeResult [exec $ORACLE_SQLPLUS_HOME/sqlplus -L -S scott/tiger@orcl]} errmsg]} {
puts "$errmsg"
} else {
puts "NOT HERE1"
puts "$executeResult"
}

But it seems to take ages to connect, i.e. hangs

 

[mikrom]

Accessing Oracle database from MS Windows using tcom + ADO should be similar to DB2 on iSeries, I only have to change connection string in the example above (and eventually schema and table name).

I'm sorry to say, but I have no experience with OraTCL.
Try to google for examples how to use it - I found these:

http://wiki.tcl.tk/12147

http://docs.activestate.com/activetcl/8.5/oratcl/oratcl.html

http://www.idevelopment.info/data/Oracle/DBA_tips/Programming/PROGRAMMING_1.shtml

 

[birdy1980]

Thanks. I will check