Skip site navigation (1) Skip section navigation (2)

Re: [Q] specifying bigint

From: "V S P" <toreason(at)fastmail(dot)fm>
To: pgsql-odbc(at)postgresql(dot)org
Subject: Re: [Q] specifying bigint
Date: 2009-03-28 03:14:31
Message-ID: 1238210071.3027.1307725503@webmail.messagingengine.com (view raw or flat)
Thread:
Lists: pgsql-odbc
HI,
I did some more tracing, and it looks like the data is sent to ODBC
correctly  (bigint, char, bigint)
and it is somewhere within ODBC driver or after, bigints are converted
to strings
Wanted to ask if there is may be a workaround of some sort to keep
bigints
as number not strings 


here is the ODBC trace of the offending update (Ascii driver on windows)
the bigint appear to be 'announced' as bigints 
(prb_bigint is my program name)

The error indicates, however that somewhere bigint is converted to a
string
in 'f3=t.f3"

	DIAG [42883] ERROR: operator does not exist: bigint = text;
Error while executing the query (7) 


------------------------
prb_bigint      d98-110 ENTER SQLPrepare 
		HSTMT               00921C28
		UCHAR *             0x009078F0 [      -3] "UPDATE test_tab SET f2= t.f2,f3=t.f3 from(VALUES( ?          , ?            , ?          ))as t(f1,f2,f3) WHERE test_tab.f1=t.f1\ 0"
		SDWORD                    -3

prb_bigint      d98-110 EXIT  SQLPrepare  with return code 0
(SQL_SUCCESS)
		HSTMT               00921C28
		UCHAR *             0x009078F0 [      -3] "UPDATE test_tab SET f2= t.f2,f3=t.f3 from(VALUES( ?          , ?            , ?          ))as t(f1,f2,f3) WHERE test_tab.f1=t.f1\ 0"
		SDWORD                    -3

prb_bigint      d98-110 ENTER SQLBindParameter 
		HSTMT               00921C28
		UWORD                        1 
		SWORD                        1 <SQL_PARAM_INPUT>
		SWORD                      -25 <SQL_C_SBIGINT>
		SWORD                       -5 <SQL_BIGINT>
		SQLULEN                    8
		SWORD                        0 
		PTR                0x00907D78
		SQLLEN                     8
		SQLLEN *            0x00907D68

prb_bigint      d98-110 EXIT  SQLBindParameter  with return code 0
(SQL_SUCCESS)
		HSTMT               00921C28
		UWORD                        1 
		SWORD                        1 <SQL_PARAM_INPUT>
		SWORD                      -25 <SQL_C_SBIGINT>
		SWORD                       -5 <SQL_BIGINT>
		SQLULEN                    8
		SWORD                        0 
		PTR                0x00907D78
		SQLLEN                     8
		SQLLEN *            0x00907D68 (8)

prb_bigint      d98-110 ENTER SQLBindParameter 
		HSTMT               00921C28
		UWORD                        2 
		SWORD                        1 <SQL_PARAM_INPUT>
		SWORD                        1 <SQL_C_CHAR>
		SWORD                       12 <SQL_VARCHAR>
		SQLULEN                   31
		SWORD                        0 
		PTR                0x00907E00
		SQLLEN                    32
		SQLLEN *            0x00907980

prb_bigint      d98-110 EXIT  SQLBindParameter  with return code 0
(SQL_SUCCESS)
		HSTMT               00921C28
		UWORD                        2 
		SWORD                        1 <SQL_PARAM_INPUT>
		SWORD                        1 <SQL_C_CHAR>
		SWORD                       12 <SQL_VARCHAR>
		SQLULEN                   31
		SWORD                        0 
		PTR                0x00907E00
		SQLLEN                    32
		SQLLEN *            0x00907980 (-3)

prb_bigint      d98-110 ENTER SQLBindParameter 
		HSTMT               00921C28
		UWORD                        3 
		SWORD                        1 <SQL_PARAM_INPUT>
		SWORD                      -25 <SQL_C_SBIGINT>
		SWORD                       -5 <SQL_BIGINT>
		SQLULEN                    8
		SWORD                        0 
		PTR                0x00907A08
		SQLLEN                     8
		SQLLEN *            0x00907A18

prb_bigint      d98-110 EXIT  SQLBindParameter  with return code 0
(SQL_SUCCESS)
		HSTMT               00921C28
		UWORD                        3 
		SWORD                        1 <SQL_PARAM_INPUT>
		SWORD                      -25 <SQL_C_SBIGINT>
		SWORD                       -5 <SQL_BIGINT>
		SQLULEN                    8
		SWORD                        0 
		PTR                0x00907A08
		SQLLEN                     8
		SQLLEN *            0x00907A18 (8)

prb_bigint      d98-110 ENTER SQLExecute 
		HSTMT               00921C28

prb_bigint      d98-110 EXIT  SQLExecute  with return code -1
(SQL_ERROR)
		HSTMT               00921C28

		DIAG [42883] ERROR: operator does not exist: bigint = text;
Error while executing the query (7) 

prb_bigint      d98-110 ENTER SQLGetDiagRecW 
		SQLSMALLINT                  3 
		SQLHANDLE           00921C28
		SQLSMALLINT                  1 
		SQLWCHAR *          0x00128420 (NYI) 
 		SQLINTEGER *        0x00128D18
		SQLWCHAR *          0x00922358 (NYI) 
 		SQLSMALLINT                511 
		SQLSMALLINT *       0x001284A8

prb_bigint      d98-110 EXIT  SQLGetDiagRecW  with return code 0
(SQL_SUCCESS)
		SQLSMALLINT                  3 
		SQLHANDLE           00921C28
		SQLSMALLINT                  1 
		SQLWCHAR *          0x00128420 (NYI) 
 		SQLINTEGER *        0x00128D18 (7)
		SQLWCHAR *          0x00922358 (NYI) 
 		SQLSMALLINT                511 
		SQLSMALLINT *       0x001284A8 (78)

prb_bigint      d98-110 ENTER SQLFreeHandle 
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           00921C28

prb_bigint      d98-110 EXIT  SQLFreeHandle  with return code 0
(SQL_SUCCESS)
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           00921C28

prb_bigint      d98-110 ENTER SQLEndTran 
		SQLSMALLINT                  2 <SQL_HANDLE_DBC>
		SQLHANDLE           009215E8
		SQLSMALLINT                  0 

prb_bigint      d98-110 EXIT  SQLEndTran  with return code 0
(SQL_SUCCESS)
		SQLSMALLINT                  2 <SQL_HANDLE_DBC>
		SQLHANDLE           009215E8
		SQLSMALLINT                  0 

prb_bigint      d98-110 ENTER SQLDisconnect 
		HDBC                009215E8

prb_bigint      d98-110 EXIT  SQLDisconnect  with return code 0
(SQL_SUCCESS)
		HDBC                009215E8

prb_bigint      d98-110 ENTER SQLFreeHandle 
		SQLSMALLINT                  2 <SQL_HANDLE_DBC>
		SQLHANDLE           009215E8

prb_bigint      d98-110 EXIT  SQLFreeHandle  with return code 0
(SQL_SUCCESS)
		SQLSMALLINT                  2 <SQL_HANDLE_DBC>
		SQLHANDLE           009215E8

prb_bigint      d98-110 ENTER SQLFreeHandle 
		SQLSMALLINT                  1 <SQL_HANDLE_ENV>
		SQLHANDLE           00921540

prb_bigint      d98-110 EXIT  SQLFreeHandle  with return code 0
(SQL_SUCCESS)
		SQLSMALLINT                  1 <SQL_HANDLE_ENV>
		SQLHANDLE           00921540












--------------------------

















On Fri, 27 Mar 2009 16:20 -0400, pgsql-odbc(at)postgresql(dot)org wrote:
> Hello,
> wanted to ask what is the apropriate way to specify
> bind variables for 'bigint' columns
> 
> 
> I am using OTL library to abstract ODBC details out
> and in there
> 
> every bigint is converted to
> 'string version'  of long long 
> 
> This makes the following SQL statement invalid
> 
> 
> 
> 
> UPDATE test_tab SET f2= t.f2,f3=t.f3 from(VALUES( '10'
>   , 'Name changed'            , '9999'          ))as
>   t(f1::bigint,f2,f3::bigint)
>  WHERE test_tab.f1=t.f1
> 
> 
> ----------
> test_tab declared as  f1 bigint, f2 char(31), f3 bigint.
> 
> 
> 
> The error is in    "f3=t.f3"
> 
> because Postgres assumes that the t.f3 is of type string -- probably
> because
> the '9999' is string at it automatically derives the type.
> 
> So I am thinking that I either must tell postgres, that
> t.t3 is also bigint
> 
> or do f3=t.f3::bigint
> 
> 
> However, adding the bigint typecast is actually difficult for me now,
> because I created an ActiveRecord-like framework for C++ (where C++
> variables
> contain enough information to be used as 'Fields' that belong to
> 'database-aware'
> Active record)
> 
> 
> so handling an exception like this for bigints is difficult.
> 
> 
> So  I wanted to find out
> a) is it necessary in Pg odbc on windows and Unix to use strings to
> pass data in and to  read in  for bigints?
> Is there may be another way (they are 'long long' c++ data types)
> 
> b) is there another way (I am using this type of update to do 'bulk'
> update
> of several rows at the same time
> 
> 
> 
> thank  you in advance,
> Vlad
> 
> 
> 
> -- 
>   V S P
>   toreason(at)fastmail(dot)fm
> 
> -- 
> http://www.fastmail.fm - One of many happy users:
>   http://www.fastmail.fm/docs/quotes.html
> 
-- 
  V S P
  toreason(at)fastmail(dot)fm

-- 
http://www.fastmail.fm - A fast, anti-spam email service.


Responses

pgsql-odbc by date

Next:From: Hiroshi InoueDate: 2009-03-28 11:26:05
Subject: Re: [Q] specifying bigint
Previous:From: Hiroshi SaitoDate: 2009-03-27 17:38:49
Subject: Re: Release plan

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group