DBD-ODBC - Running stored procs, can't bind default values.

[Pablo S]

Hello!

I've been using DBD::ODBC and Openlink's MSSQL ODBC driver to run
stored procs on a remote windows server, and it is working. I can get
my return values back just fine (-IF- I use the ODCB RPC syntax and
not the TSQL syntax), but I can't seem to run a stored proc and use
the 'default' keyword to not have to define a parameter.

e.g.:

$sth=$gpdbh->prepare("{ CALL sp_foo(?, ?, ?, ?, default, ?, default,
default, default, ?, default, default, default, default, default,
default, default, default, default, default, default, default,
default, default, default, default, default, default, default,
default, default, default, default, default, default, default,
default, default, default, default, default, default, default,
default, default, default, default, default, default, default, ?, ?,
?, default, default, default, default, default, default, default,
default, default, default, default, default, default, default,
default, default, default, default, default, default, default,
default, default, default, 1, default, default, default, default,
default, default, default, default, default, default, default, ?, ?)
}");


Instead of having unquoted strings (e.g. exec sp_foo
default,default,default) it looks like this on query analyzer:

declare @P1 char(255)
set @P1='1523 '
declare @P2 varchar(4000)
set @P2='6232 3407 3441 2221 2273 3442 5313 928 929 930 908 909 910
913 914 915 179 245 927 106 110 3420 870 1523 '
exec taSopLineIvcInsert '3 ', 'IVC005000', 'DBLAZE', '10/12/04',
'default', '100XLG', 0, 0, 0, '34 ', 0, 'default', 'default',
'default', 'default', 'default', 0, 'default', 'default', 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 'default', 'default', 'default', 'default', 0,
'default', 'default', 'default', 'default', 'default', 'default',
'default', 'default', 'default', 'default', 'default', 'default',
'default', 'default', 'default', 0, '8/12/04 ', '10/12/04 ',
'10/12/04', 'default', 'default', 'default', 'default', 'default',
'default', 'default', 'default', 'default', 0, 0, 'default', 0,
'default', 0, 0, 0, 0, 0, 0, 0, 0, 'default', 0, 1, 0, 'default',
'default', 0, 0, 0, 'default', 'default', 'default', 'default',
'default', @P1 output, @P2 output
select @P1, @P2

Note the single quotes!! Very bad! What happens is that the default
parameter gets quoted for nvarchar fields and gets put in as 0 for int
fields. Weird!!

If I use the {call sp_foo(,,?,,?,,,,,)} syntax, I only get the sp run
with the args I specify. It looks like this in query analyzer:

declare @P1 varchar(4000)
set @P1=''
declare @P2 varchar(4000)
set @P2=''
exec taSopLineIvcInsert '3 ', 'IVC005000', 'DBLAZE', '8/12/04 ',
'100XLG', '34', '10/12/04 ', '10/12/04', '10/12/04', 1, @P1 output,
@P2 output
select @P1, @P2

Which obviously does not fly. What can I do? Do you think this is an
ODBC driver issue, a DBI issue, or a DBD::ODBC issue?

Thanks in advance for any ideas!!

 

[Rhesa Rozendaal]

Hello!

I've been using DBD::ODBC and Openlink's MSSQL ODBC driver to run
stored procs on a remote windows server, and it is working. I can get
my return values back just fine (-IF- I use the ODCB RPC syntax and
not the TSQL syntax), but I can't seem to run a stored proc and use
the 'default' keyword to not have to define a parameter.

What I do is this:

$sth=$gpdbh->prepare('exec sp_foo @p1=?, @p2=?');
$sth->execute('value1', 'value2');

HTH,
Rhesa