INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Invoke SQL script within TCL

Invoke SQL script within TCL

(OP)
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

RE: Invoke SQL script within TCL

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.  

RE: Invoke SQL script within TCL

(OP)
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

RE: Invoke SQL script within TCL

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

db_example.tcl

CODE

##########################################
# Reading records and fields from database
##########################################

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

### Using ADO
package require tcom

# Connection String
set connection_string \
  [format "PROVIDER=IBMDA400;DATA SOURCE=%s; USER ID=%s; PASSWORD=%s" \
  $csebk $userid $pass]

# Open ADO connection
set connection [::tcom::ref createobject ADODB.Connection]
$connection Open $connection_string

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

# Create and populate an array of field names
set fields_count [[$recordset Fields] Count]
set fields {}
tcom::foreach field [$recordset Fields] {
  lappend fields [$field Name]
}

set csv_file_name "mesta.csv"
# Open CSV file
puts [format "Now writing data to CSV file '%s'.." $csv_file_name]
set csv_file [open $csv_file_name "w"]

# Header line
set line [join $fields ";"]
puts $csv_file $line

# Data lines
while {![$recordset EOF]} {
  set line_list {}
  tcom::foreach fld [$recordset Fields] {
    lappend line_list [$fld Value]
  }
  set line [join $line_list ";"]
  puts $csv_file $line
  $recordset MoveNext
}
puts "..done."

# Close CSV file
close $csv_file
# Close RecordSet
$recordset Close
# Close Connection
$connection Close
For entering passwords I have made the following module, which is used in the script above:
getpass.tcl

CODE

proc getpass {prompt} {
  # Required package is Expect. It could be installed using teacup:
  # teacup install Expect
  package require Expect
  set oldmode [stty -echo -raw]
  send_user "$prompt"
  set timeout -1
  expect_user -re "(.*)\n"
  send_user "\n"
  eval stty $oldmode
  return $expect_out(1,string)
}

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

CODE

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

CODE

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 smile

RE: Invoke SQL script within TCL

(OP)
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

RE: Invoke SQL script within TCL

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
 

RE: Invoke SQL script within TCL

(OP)
Thanks. I will check :)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close