Purpose: '''Accumulate simple examples demonstrating the use of the [[ [Oratcl] ]] database library''' ---- * [ora2txt] performs a select and outputs the results as tab delimited text from a command-line * [Oratcl Logon Dialog] is a program extract showing a logon routine ---- The following examples are using a simple option table called PROPERTY. The table contains only two columns KEY and VALUE, each of them are VARCHAR2 types. The variables 'lda' and 'sth' contain the connection to the database and the statement handle for executing queries, inserts etc. A simple logon routine package require Oratcl set lda [oralogon user/pass@dbname -async] set sth [oraopen $lda] if {[oramsg $sth rc] == 0} { puts "Successfully connected" } else { puts "Unable to connect to the database." } oralogoff $lda Inserting into a database Not that big problem. Let's assume we have a button called .b and want to save all of its options in our database table. We use foreach and configure to get 'em and put them in the PROPERTY table using simple insert statements. foreach opt [.b configure] { orasql $sth "INSERT INTO PROPERTY KEY, VALUE VALUES ('[lindex $opt 0]', '[lindex $opt 4]')" } oracommit $lda As we know that both - key and value - are VARCHAR2 types we use single quotes in the value section. This can be avoided using bindings (explained later). Note also that no semicolon is used at the end of the SQL command. This is different to using SQLPlus for example where a semicolon terminates the command and forces the SQL code to be executed. Using Oratcl a simple parameter is assumed and parsing rules are only applied by the Tcl interpreter. Don't forget to commit the data, otherwise nobody else would be able to read the contents in the table and you will loose your work after ending your session. Deleting from a database For sure it won't make sense to save all of the options, e.g. -textvariable will be set by the application itself when it creates the button. Let's delete it. orasql $lda "DELETE FROM PROPERTY WHERE KEY = '-textvariable'" oracommit $lda Changing the values We want to change some of the options of our button directly in the database. No problem using an update statement. orasql $sth "UPDATE PROPERTY SET VALUE = 'red' WHERE KEY = '-foreground'" oracommit $lda Retrieving data from a database Now the interesting part. We try to reload our settings from the database into another button '.b2'. orasql $sth "SELECT KEY, VALUE FROM PROPERTY" while {1} { orafetch $stmt -datavariable row if {[oramsg $stmt rc] != 0} break .b2 configure [lindex $row 0] [lindex $row 1] } The orafetch reads exactly one row from the result set in the variable 'row'. This variable contains a list of column contents in the order specified by the SELECT statement. If no more rows are available we will get an ORACLE error (return code = 1403 - No more data) and we break the statement. Binding # code goes here PL/SQL # code goes here Cleanup Be tidy - my mother said. So do I and gracefully close the connection to the database after work. oraclose $sth oralogoff $lda Pitfalls Oh gosh - how often did thid thing came up - regional settings. I work in Austria, so my PC is setup with a german version of ORACLE. No problems I thought until I selected some rows from a table and tried to insert it into another one afterwards. All double values were dumped using a comma instead of a decimal point. BUT a comma is also the field separator in SQL so ORACLE got confused when trying to use the information in an insert statement. So I reccomend to change the language settings after oralogon. This can be done using a simle SQL statement: orasql $sth {alter session set NLS_NUMERIC_CHARACTERS =". "} orasql $sth {alter session set NLS_DATE_FORMAT ="YYYY.MM.DD HH24:MI:SS"} The second statement also alters the default date format which is also very convenient for me. I put this two statements just after my logon routine to be not confused later. Anything else you want to see regarding Oratcl... [RLH] - Besides the above, I would like to see some stuff about pulling data out and displaying it via [CGI]. ---- ''[Todd Helfter] wrote on comp.lang.tcl:'' Here are two examples. package require Oratcl # should see '4.3' # open logon handle set lda [oralogon tmh/password@db] # should see 'oratcl0' # open statement handle % set cur [oraopen $lda] # should see 'oratcl0.0' # parse sql statement oraparse $cur {select table_name from user_tables} # should see '0' :: 0 == OCI_SUCCESS # execute the statement handle oraexec $cur # should see '0' # fetch the results (loop until return code != OCI_SUCCESS) while {[orafetch $cur -datavariable row] == 0} { puts $row } # I got back table name 'STACKS' # describe a table oradesc $lda stacks # I got back {SID 22 NUMBER 0 -127 1} {EBE 3 CHAR 0 0 1} {PART 20 CHAR 0 0 1} # example with a bind. Only return rows with sid = 6 oraparse $cur {select * from stacks where sid = :sid} orabind $cur :sid 6 oraexec $cur oracols $cur # I got back SID EBE PART oracols $cur all # I got back {SID 22 NUMBER 0 -127 1} {EBE 3 CHAR {} {} 1} {PART 20 CHAR {} {} 1} while {[orafetch $cur -datavariable row] == 0} { puts $row } # 1st row: 6 TMH {} # 2nd row: 6 TMH {} oraclose $cur oralogoff $lda ---- [RLH] - Thank you very much! ---- [[[Category Example]|[Category Database]]]