I need to run a stored procedure in the MSSQL 2005 server from my linux
machine. It takes a parameter which is timestamp datatype. However, when
I try to use bind_parameter, I get an error:

my $sp_qry = "EXEC MY_STORED_PROC ?" ;
my $sth = $dbh->prepare($sp_qry) ;
$sth->bind_param(1, $row[0], SQL_TIMESTAMP);

Error:

Bareword "SQL_TIMESTAMP" not allowed while "strict subs" in use at
track_changes.pl line 34.
Execution of track_changes.pl aborted due to compilation errors.

If I take out the strict:
DBI::st=HASH(0x9fe7388)->bind_param(...): attribute parameter
'SQL_TIMESTAMP' is not a hash ref at myscript.pl line 32.

line 32 is $sth->bind_param(1, $row[0], SQL_TIMESTAMP);

SQL_TIMESTAMP is not the right type? not supported?

--s

Quoth Sharif Islam <mislam [at] spam.uiuc.edu>:
> I need to run a stored procedure in the MSSQL 2005 server from my linux
> machine. It takes a parameter which is timestamp datatype. However, when
> I try to use bind_parameter, I get an error:
>
> my $sp_qry = "EXEC MY_STORED_PROC ?" ;
> my $sth = $dbh->prepare($sp_qry) ;
> $sth->bind_param(1, $row[0], SQL_TIMESTAMP);
>
> Error:
>
> Bareword "SQL_TIMESTAMP" not allowed while "strict subs" in use at
> track_changes.pl line 34.
> Execution of track_changes.pl aborted due to compilation errors.

The DBI documentation suggests you need

use DBI qw/:sql_types/;

to import the type constants.

> If I take out the strict:

Don't do that. Unless you already understand what you're doing and why
it is the right thing to do, it never helps.

Ben

Ben Morrow wrote:
> Quoth Sharif Islam <mislam [at] spam.uiuc.edu>:
>> I need to run a stored procedure in the MSSQL 2005 server from my linux
>> machine. It takes a parameter which is timestamp datatype. However, when
>> I try to use bind_parameter, I get an error:
>>
>> my $sp_qry = "EXEC MY_STORED_PROC ?" ;
>> my $sth = $dbh->prepare($sp_qry) ;
>> $sth->bind_param(1, $row[0], SQL_TIMESTAMP);
>>
>> Error:
>>
>> Bareword "SQL_TIMESTAMP" not allowed while "strict subs" in use at
>> track_changes.pl line 34.
>> Execution of track_changes.pl aborted due to compilation errors.
>
> The DBI documentation suggests you need
>
> use DBI qw/:sql_types/;
>
> to import the type constants.

Thanks. But it seems now the datatype is getting set to VARCHAR:

DBD::Sybase::st execute failed: Server message number=257 severity=16
state=3 line=0 server=MSSQL procedure=MY_STORED_PROC text=Implicit
conversion from data type char to timestamp is not allowed. Use the
CONVERT function to run this query. at myscript.pl line 34.


I get the parameter from another query and I save the timestamp value in
a variable, $row[0]:

$qry = SELECT min_active_rowversion()
....
$sth->bind_param(1, $row[0], SQL_TIMESTAMP);


But bind_param suppose set the type to the correct datatype?

--s

in message <ft0trj$iou$1 [at] news.acm.uiuc.edu>,
wrote Sharif Islam ...

> Thanks. But it seems now the datatype is getting set to VARCHAR:
>
> DBD::Sybase::st execute failed: Server message number=257 severity=16
> state=3 line=0 server=MSSQL procedure=MY_STORED_PROC text=Implicit
> conversion from data type char to timestamp is not allowed. Use the
> CONVERT function to run this query. at myscript.pl line 34.

Seems like the problem is with the stored procedure (either in
accepting the parameter or in the query sent to db) as the error
message is coming from the database server.


- parv

--