Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight

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

by ckbbkc (Novice)
on Sep 10, 2015 at 16:32 UTC ( #1141568=perlquestion: print w/replies, xml ) Need Help??

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


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";

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!!

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();
    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.
    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.

      Thank you very much! I tried your suggested solution. Unfortunately, explicitly declaring the type in the bind_param doesn't help.

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)) };

    or STRING and add the leading zeros in SQL

    my $insertMRNQuery = qq{ insert #mrns (PAT_MRN_ID) values (RIGHT(STRING('0000000000',?),10)) };

      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!!!

Re: Using perl dbi and Sybase IQ doing insert with placeholder getting datatype error
by herveus (Parson) on Sep 10, 2015 at 17:19 UTC

    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.


      Thank you for the explanation of the SQL Anywhere mention!

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 approachdon'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


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1141568]
Front-paged by Corion
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (3)
As of 2020-07-05 14:38 GMT
Find Nodes?
    Voting Booth?

    No recent polls found