Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

Inserting to a bigint data type in Sybase using prepare statements

by j_ochoa (Novice)
on Jul 07, 2017 at 10:59 UTC ( #1194470=perlquestion: print w/replies, xml ) Need Help??

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

Hi all,

I have a problem trying to insert into a column with data type as bigint and any help is much appreciated.

Database: Sybase

The error I get from the database is this:

Couldn't prepare statement: Server message number=257 severity=16 state=1 line=0 server=DEVEL1 procedure=DBD3 text=Implicit conversion from datatype 'VARCHAR' to 'BIGINT' is not allowed. Use the CONVERT function to run this query.

token_id column is a bigint data type

The conflicting line is where binding parameter 7

The code looks like this:

my $insert_sth = "INSERT INTO some_table (transaction_id, ccn_masked, cardholder_name, exp_month, exp_year, + card_type, token_id ) VALUES(?, ?, ?, ?, ?, ?, ? )"; my $q_result = $dbh->prepare($insert_sth) or die "Couldn't prepare sta +tement: " . $dbh->errstr; $q_result->bind_param(1, $txId, { TYPE => SQL_INTEGER } ); $q_result->bind_param(2, $add_data{'maskedccn'}); $q_result->bind_param(3, getCardholderName($self)); $q_result->bind_param(4, getExpMonth($self), { TYPE => SQL_INTEGER + }); $q_result->bind_param(5, getExpYear($self), { TYPE => SQL_INTEGER +}); $q_result->bind_param(6, getCardType($self)); $q_result->bind_param(7, 5114780000000000271, { TYPE => SQL_BIGINT + } ); $q_result->execute()or die "Couldn't prepare statement: " . $dbh->errs +tr;
  • Comment on Inserting to a bigint data type in Sybase using prepare statements
  • Download Code

Replies are listed 'Best First'.
Re: Inserting to a bigint data type in Sybase using prepare statements
by poj (Abbot) on Jul 07, 2017 at 12:26 UTC
    Implicit conversion from datatype 'VARCHAR' to 'BIGINT' is not allowed. Use the CONVERT function to run this query.

    Try an explicit conversion

    my $insert_sth = " INSERT INTO some_table ( transaction_id, ccn_masked, cardholder_name, exp_month, exp_year,card_type, token_id ) VALUES (?, ?, ?, ?, ?, ?, CONVERT(BIGINT,?) )";
    poj

      Hi poj,

      I have also tried that, but in that case I was getting this error

      <Couldn't prepare statement: Server message number=12828 severity=16 state=1 line=1 server=DEVEL1 procedure=DBD3 text=The datatype of a parameter marker used in the dynamic prepare statement could not be resolved.

      BR

      JO

        How about using INSERT...SELECT instead of INSERT...VALUES? It might be legal in that statement instead.

        What version of Sybase, Perl and DBD::Sybase are you using ?

Re: Inserting to a bigint data type in Sybase using prepare statements
by runrig (Abbot) on Jul 07, 2017 at 15:21 UTC

    I have nothing but trouble using Sybase's 'extended' datatypes (bigint is not a 'standard' datatype for Sybase, and e.g., I run into trouble when someone accidentally defines a column as 'date' instead of 'datetime'...we also use Oracle where 'date' is normal).

    I'd redefine the column as 'decimal', it has up to 38 digits of precision (so 'decimal(38,0)' for max integer precision), and you won't need all the { TYPE => ... } declarations with the separate bind_param's, you can just bind on the execute() statement.

    And, the trouble happens not only in perl, but also in Sybase's 'bcp' utility, which to me is a sign that you should not use these datatypes.

Re: Inserting to a bigint data type in Sybase using prepare statements
by thanos1983 (Parson) on Jul 07, 2017 at 12:17 UTC

    Hello j_ochoa,

    Welcome to the monastery. From what I see from your error message: Couldn't prepare statement: Server message number=257 severity=16 state=1 line=0 server=DEVEL1 procedure=DBD3 text=Implicit conversion from datatype 'VARCHAR' to 'BIGINT' is not allowed. Use the CONVERT function to run this query. it looks like the db is configured to accept 'VARCHAR' on this field that you are trying to insert the data.

    Update: Sorry wrong answer. I did not see the token_id column is a bigint data type.

    I do not have a sysbase db on my box so I can replicate and verify the error. Can you export the db so we can see the fields, or simply check the field if it is 'VARCHAR'?

    You can try to convert it fellow monk poj posted bellow. Or try to convert it something like:

    my $int = '123'; $int += 0; # convert to int not string any more

    OR:

    $int = int($int); # converted

    OR:

    $int = sprintf("%d", $int); # converted

    Looking forward to your update, BR.

    Seeking for Perl wisdom...on the process of learning...not there...yet!

      Hello thanos,

      Thank you for taking a look into this.

      Sorry for not posting the db table but as you can imagine it is a bit sensitive info.

      However, I have double checked and the database column is set up as BIGINT and allow nulls. I also don't see any other constraints

      To me it looks like Perl DBI is sending a VARCHAR to Sybase engine even though I am using SQL_BIGINT in the bind_param method

      BR JO
        I know this is an old thread but ... I am running into this same problem right now.

        G

Re: Inserting to a bigint data type in Sybase using prepare statements
by Anonymous Monk on Jul 08, 2017 at 02:07 UTC

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (2)
As of 2022-10-03 01:24 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My preferred way to holiday/vacation is:











    Results (13 votes). Check out past polls.

    Notices?