Inserting to a bigint data type in Sybase using prepare statements

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;
[download]

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,?) )";` [download] poj	[reply] [d/l]
Re^2: Inserting to a bigint data type in Sybase using prepare statements by j_ochoa (Novice) on Jul 07, 2017 at 13:23 UTC
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	[reply]
Re^3: Inserting to a bigint data type in Sybase using prepare statements by chacham (Prior) on Jul 07, 2017 at 14:18 UTC
How about using INSERT...SELECT instead of INSERT...VALUES? It might be legal in that statement instead.	[reply]
Re^3: Inserting to a bigint data type in Sybase using prepare statements by poj (Abbot) on Jul 07, 2017 at 14:29 UTC
What version of Sybase, Perl and DBD::Sybase are you using ?	[reply]
Re^4: Inserting to a bigint data type in Sybase using prepare statements by j_ochoa (Novice) on Jul 10, 2017 at 06:36 UTC
Re^5: Inserting to a bigint data type in Sybase using prepare statements by poj (Abbot) on Jul 10, 2017 at 07:01 UTC
Some notes below your chosen depth have not been shown here
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.	[reply] [d/l]
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` [download] OR: `$int = int($int); # converted` [download] OR: `$int = sprintf("%d", $int); # converted` [download] Looking forward to your update, BR. Seeking for Perl wisdom...on the process of learning...not there...yet!	[reply] [d/l] [select]
Re^2: Inserting to a bigint data type in Sybase using prepare statements by j_ochoa (Novice) on Jul 07, 2017 at 13:15 UTC
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	[reply]
Re^3: Inserting to a bigint data type in Sybase using prepare statements by chexmix (Hermit) on Nov 26, 2018 at 20:29 UTC
I know this is an old thread but ... I am running into this same problem right now. G	[reply]
Re: Inserting to a bigint data type in Sybase using prepare statements by mpeppler (Vicar) on Feb 03, 2023 at 15:14 UTC
This was a bug in DBD::Sybase. And at the time I wasn't really using it much anymore, so it sort of fell by the way-side. This issue was fixed about 2 years ago, and the issue with binding unsigned int was fixed today. The number of folks still using Sybase (and DBD::Sybase) today is of course much lower now - but being retired now gives me a bit of time to take care of some loose ends... Michael	[reply]
Re: Inserting to a bigint data type in Sybase using prepare statements by Anonymous Monk on Jul 08, 2017 at 02:07 UTC
Ah, good times. Takes me down the memory lane (when I tried to use UNSIGNED INT).	[reply]