Using perl dbi and Sybase IQ doing insert with placeholder getting datatype error

ckbbkc has asked for the wisdom of the Perl Monks concerning the following question:

Hello,

I have some code that I have run against MS SQL Server and Sybase ASE successfully. When I try to run it against a Sybase IQ server, there is a problem.

A temp table is created with a single varchar(25) field. Then a list of numbers are read from a file, leading zero's are pre-pended and I attempt to insert them one by one into the temp table.

The error returned is:

DBD::Sybase::st execute failed: Server message number=21 severity=14 state=0 line=0 text=SQL Anywhere Error -1000187: Unable to implicitly convert column 'PAT_MRN_ID' to datatype (varchar) from datatype (integer).

The code is:

my $crTmpMRN = qq{create table #mrns (PAT_MRN_ID varchar(25))};
$dbh->do($crTmpMRN);

my $mrn;
my $name;
my $fileRowCount=0;
my $dbRowCount=0;
my $distinctMRNCount = 0;
#for some reason SybaseIQ is trying to insert $mrn as an integer
my $insertMRNQuery = qq{insert #mrns (PAT_MRN_ID) values (?)};
my $sth=$dbh->prepare($insertMRNQuery);
open (MRNFILE,"$baseDir$mrn_file") or die "Can't read file $baseDir$mr
+n_file [$!]\n";
while (<MRNFILE>) {
    chomp ($_);
    ($mrn,$name)=split('\t',$_);
    #uses 10 character MRNs
    $mrn = substr '0000000000'.$mrn, -10;
    $sth->execute($mrn);
    $fileRowCount ++;
    print qq{'$mrn'\n};
}
close (MRNFILE);
print '$fileRowCount: '.$fileRowCount."\n";
[download]

I don't know much about the odbc drivers - I think it is odd that the error says "SQL Anywhere", but I am using the connection information that I was given. I have figured out a workaround - creating two columns in the temp table, inserting the numbers converted to varchar, then updating the values to the second column in the temp table. But really, I want to understand why it is attempting to insert $mrn as a numeric rather than a string.

Any help would be greatly appreciated!!

Comment on Using perl dbi and Sybase IQ doing insert with placeholder getting datatype error Select or Download Code

Replies are listed 'Best First'.
Re: Using perl dbi and Sybase IQ doing insert with placeholder getting datatype error by runrig (Abbot) on Sep 10, 2015 at 17:12 UTC
You could try making the type explicit: `use DBI qw(SQL_VARCHAR); $sth->bind_param(1, $mrn, SQL_VARCHAR); $sth->execute();` [download] Though it would be odd if it does work, because SQL_VARCHAR is supposed to be the default. Update: The execute() docs say this: `If any arguments are given, then "execute" will effectively call "bind_param" for each value before executing the statement. Values bound in this way are usually treated as "SQL_VARCHAR" types unless the driver can determine the correct type (which is rare), or unless "bind_param" (or "bind_param_inout") has already been used to specify the type.` [download] So that gives some credibility to explicitly declaring the type...maybe the driver is trying to be smart and interpreting your values as numbers, and while Sybase ASE might be able to handle that, perhaps Sybase IQ cannot.	[reply] [d/l] [select]
Re^2: Using perl dbi and Sybase IQ doing insert with placeholder getting datatype error by ckbbkc (Novice) on Sep 10, 2015 at 18:11 UTC
Thank you very much! I tried your suggested solution. Unfortunately, explicitly declaring the type in the bind_param doesn't help.	[reply]
Re: Using perl dbi and Sybase IQ doing insert with placeholder getting datatype error by poj (Abbot) on Sep 10, 2015 at 19:48 UTC
You could try `CAST` `my $insertMRNQuery = qq{ insert #mrns (PAT_MRN_ID) values (CAST(? AS VARCHAR)) };` [download] or `STRING` and add the leading zeros in SQL `my $insertMRNQuery = qq{ insert #mrns (PAT_MRN_ID) values (RIGHT(STRING('0000000000',?),10)) };` [download] poj	[reply] [d/l] [select]
Re^2: Using perl dbi and Sybase IQ doing insert with placeholder getting datatype error by ckbbkc (Novice) on Sep 10, 2015 at 22:03 UTC
Thank you! These solutions work. I particularly like the (RIGHT(STRING('0000000000',?,10)). The cast will remove the leading zeroes on $mrn. I think the prepare just isn't getting the column datatype from Sybase IQ whereas it was with MSSQL Server and Sybase ASE. Thank you very much!!!	[reply]
Re: Using perl dbi and Sybase IQ doing insert with placeholder getting datatype error by herveus (Prior) on Sep 10, 2015 at 17:19 UTC
Howdy! IQ is built on top of SQL Anywhere; that's where you get the SQL Anywhere mention. I found some discussion on a SAP board that got this error while inserting a null as a datetime. Apparently, nulls get counted as int, but it's not clear that this bears on your problem. Darn. I suspect the problem may be with the ODBC driver. Not a whole lot of help. yours, Michael	[reply]
Re^2: Using perl dbi and Sybase IQ doing insert with placeholder getting datatype error by ckbbkc (Novice) on Sep 10, 2015 at 18:13 UTC
Thank you for the explanation of the SQL Anywhere mention!	[reply]
Re: Using perl dbi and Sybase IQ doing insert with placeholder getting datatype error by ww (Archbishop) on Sep 10, 2015 at 17:31 UTC
This is a question better addressed to a SYBASE (IQ server) oriented group. <insert> If the insights from </insert> runrig <insert> and Herveus </insert> ~~has offered a possible approach~~don't nail the explanation, then you may be dealing ~~but even that deals~~ with a SYBASE issue. And, in the big picture, creating your temp table with a single (varchar(25)) field for your output looks a lot like byte-paring or premature optimization. You've said as much, in effect, in describing your workaround -- a temp table with two fields. So, while your hope of understanding your error is highly commendable, I suspect <insert> that if the replies above don't explain adequately, </insert> the root issue is within SYBASE, not Perl. EDITS above: after seeing Herveus' note which tends to undermine my notion that the problem may be with the server s/ware. `++$anecdote ne $data` --	[reply] [d/l]

Back to Seekers of Perl Wisdom