Oratcl4.3 and more than one bind variable...

[Volker Hetzer]

Hi!
I have trouble using more than one bind variable in a query.
Here is my script:
package require Oratcl 4.3

#Table
#create table CVC_OUTLINES
#(
#object_name varchar(64) not null,
#IsTop number(1) not null,
#primary key (object_name)
#);
set C [oralogon test_hetzer/test_hetzer@ecadbib]

set S [oraopen $C]
oraconfig $S utfmode 1
oraparse $S "select object_name,istop from cvc_outlines where istop = :i and object_name = :o"
for {set Index 1} {$Index<=5} {incr Index} \
{
orabind $S :i 0 :o X$Index
if {[catch {oraexec $S}]} \
{
set Msg [oramsg $S all]
oraclose $S
oraroll $C
oralogoff $C
error $Msg
}
set Result [list]
set Row [list]
orafetch $S -datavariable Row
while {[llength $Row]>0} \
{
lappend Result $Row
set Row [list]
orafetch $S -datavariable Row
}
}
oraclose $S
exit

It gives an ora-01460 whenever more than one bind variable is used.
(The table contains one varchar and one number(1) column.)
The database is 9.2.0.6, character set AL32UTF8.
The utfmode setting doesn't make any difference, exchanging the two
parameter always results in the ora-1460, having just one bind parameter
works fine.

Any ideas?

Lots of Greetings!
Volker

[Leopold Gerlinger]

Hi Volker,

I'm trying something similar which works for me. Having a table PROPERTY
with columns PROPERTY_KEY,
PROPERTY_VAL as VARCHAR2 NOT NULL the following worked just now:

(SIECAMSClient) 49 % package require Oratcl
4.3
(SIECAMSClient) 50 % set db [oralogon ramses/mse_dba@mseltdb]
oratcl0
(SIECAMSClient) 51 % set s [oraopen $db]
oratcl0.0
(SIECAMSClient) 52 % oraparse "select count(*) from property where
property_key like :key and property_val like :val"
wrong # args: should be "oraparse select count(*) from property where
property_key like :key and property_val like :val stm_handle sql_str"
(SIECAMSClient) 53 % oraparse $s "select count(*) from property where
property_key like :key and property_val like :val"
0
(SIECAMSClient) 54 % orabind $s :key Admin :val 1
0
(SIECAMSClient) 55 % oraexec $s
0
(SIECAMSClient) 56 % orafetch $s -datavariable row
0
(SIECAMSClient) 57 % set row
0
(SIECAMSClient) 58 % orabind $s :key %Admin% :val 1
0
(SIECAMSClient) 59 % oraexec $s
0
(SIECAMSClient) 60 % orafetch $s -datavariable row
0
(SIECAMSClient) 61 % set row
43
(SIECAMSClient) 62 %

I'm not really sure what your problem is but definitely not the two binding
variables in principle. Maybe you
should also try your code 'manually' to locate your problem.

Regards - Leo
SIEMENS Austria PSE AS SB

[thel...@gmail.com]

Volker.

first a comment on efficiency. I know it doesn't work for you at all,
but you are making your code work to hard.

This::

set Result [list]
set Row [list]
orafetch $S -datavariable Row
while {[llength $Row]>0} \
{
lappend Result $Row
set Row [list]
orafetch $S -datavariable Row
}
}

Could be coded as this:::

set Result [list]
set Row [list]

while {[orafetch $S -datavariable Row] == 0} {
lappend Result $Row
}
# row will automatically be reinitialized after each fetch
# This style also avoids accidental differences between the first fetch
and the 2nd.
# I really need to write an 'Effective Programming in Oratcl 4 book" :)

As to your bind question.

Are you using Oracle 8i client libraries?
There is a specific Oracle metalink about 8.1.7 connecting to 9i with
AL32UTF8 throwing this bug.

Do you have access to oratcl source? We may need to add some debugging
statements as I do not have access to a platform in your NLS_LANG :)

Feel free to email me, you can find my email address at the sourceforge
site.

[Volker Hetzer]

"Leopold Gerlinger" <leopold....@siemens.com> schrieb im Newsbeitrag news:d355co$5q2$1...@news.siemens.at...

> Hi Volker,
>
> I'm trying something similar which works for me. Having a table PROPERTY
> with columns PROPERTY_KEY,
> PROPERTY_VAL as VARCHAR2 NOT NULL the following worked just now:

[interesting script deleted]

> I'm not really sure what your problem is but definitely not the two binding
> variables in principle. Maybe you
> should also try your code 'manually' to locate your problem.

I agree it's probably not the binding per se but maybe a subtle memory bug.

I've got a new testcase with more puzzling results:
package require Oratcl 4.3

#Requires
#create table tst (col1 number, col2 number);
#insert into tst (col1,col2) values (15,0);
set C [oralogon test_hetzer/test_hetzer@ecadbib]

#That one works all right, we use it lots of times
set Query "select col1,col2 from tst where col1 = 15 and col2 = 0"
set S [oraopen $C]
orasql $S $Query
orafetch $S -dataarray Row
puts "Orasql:"
parray Row
oraclose $S

unset Row
set Query "select col1,col2 from tst where col1 = :arg1 and col2 = :arg2"
set S [oraopen $C]
#Playing with utfmode doesn't change a thing
#oraconfig $S utfmode 1
oraparse $S $Query
orabind $S :arg1 15 :arg2 0
#This gives an error! With error number 0, i.e. no error!!
if {[catch oraexec $S]} {puts "Error: [oramsg $S all]"}
#orafetch mixes up the column order :-(
orafetch $S -dataarray Row
puts "Oraparse: "
parray Row
exit

The output is
Orasql:
Row(COL1) = 15
Row(COL2) = 0
Error: {0 {} 0 0 4 1}
Oraparse:
Row(COL1) = 0
Row(COL2) = 15

What database version do you use?
What caracter set?
You can find out the character set by querying:
select value from nls_database_parameters where PARAMETER = 'NLS_CHARACTERSET';

I've got AL32UTF8.
I've got the database running on Linux RH ES 3.0 and the client is Linux too. Tcl is 8.4.7.

Lots of Greetings and thanks!
Volker

[thel...@gmail.com]

Volker

what do you mean orafetch messes up the column order??

Tcl Arrays are not ordered

Also, you should not orafetch if oraexec fails :)

set S [oraopen $C]

oraparse $S $Query
orabind $S :arg1 15 :arg2 0

oraexec $S
while {[orafetch $S -datavariable Row] == 0} {puts [array get Row]}

gives me

COL2 0 COL1 15

If you want them in order do this::: :)

oraexec $S
set cl [oracols $S name]

while {[orafetch $S -datavariable Row] == 0} {

foreach c $cl "puts Row($c) == $Row($c)"
}

P.S.

This is on Oracle 10.1.0.4.0, characterset WE8ISO8859P1...

-Todd

[Volker Hetzer]

<thel...@gmail.com> schrieb im Newsbeitrag news:1112965503.7...@o13g2000cwo.googlegroups.com...

> Volker
>
> what do you mean orafetch messes up the column order??
>
> Tcl Arrays are not ordered
>
> Also, you should not orafetch if oraexec fails :)

Hi!
I answered Leopolds posting before I read yours, sorry.
I agree that we can do this more efficient by email and was
(still am) preparing one, only some completely unrelated
10g installation issue popped up just now and kept me
busy for a while.

As for the column order, if you look at the output I gave in
that posting you will see that
when querying without bind variables col1=15 and col2=0
where when querying with bind variables col1=0 and col2=15.

Email coming soon...

Lots of Greetings!
Volker

[Volker Hetzer]

<thel...@gmail.com> schrieb im Newsbeitrag news:1112963977....@f14g2000cwb.googlegroups.com...

> Volker.
>
> first a comment on efficiency. I know it doesn't work for you at all,
> but you are making your code work to hard.

> set Result [list]
> set Row [list]
> while {[orafetch $S -datavariable Row] == 0} {
> lappend Result $Row
> }

Very nice!
Will do so henceforth! :-)

Lots of Greetings!
Volker