Topic
  • 11 replies
  • Latest Post - ‏2010-05-27T07:59:14Z by SumitGoyal
SystemAdmin
SystemAdmin
1143 Posts

Pinned topic bigint/int8 bigserial/serial8 difference

‏2010-04-03T13:42:05Z |
Is bigint and int8 are the same data type and if not what's the difference. The same for bigserial and serial8.

Informix ODBC Driver does not return an ODBC standard data type for BIGINT and BIGSERIAL when driver option 'Report Stardard ODBC Types Only' is set in DSN setup Advanced tab. For example calling SQLGetTypeInfo I get the following info for these data types (I am assuming -114 is SQL_INFX_BIGINT)

type sql data type

-------------
BIGINT -114
BIGSERIAL -114
INT8 SQL_BIGINT
SERIAL8 SQL_BIGINT
Updated on 2010-05-27T07:59:14Z at 2010-05-27T07:59:14Z by SumitGoyal
  • SumitGoyal
    SumitGoyal
    7 Posts

    Re: bigint/int8 bigserial/serial8 difference

    ‏2010-04-05T09:58:13Z  
    As per informix release notes BIGINT and BIGSERIAL data types have the same range of values as INT8 and SERIAL8 data types. However, BIGINT and BIGSERIAL have advantages for storage and computation over INT8 and SERIAL8.

    I am also facing the same issue with Informix ODBC driver.
    But -114 is for SQL Unknown datatype.Please clarify the same.
    How to use BIGINT and BIGSERIAL in the application for fetching result set from Informix database using Informix ODBC driver.
  • SystemAdmin
    SystemAdmin
    1143 Posts

    Re: bigint/int8 bigserial/serial8 difference

    ‏2010-04-05T13:18:59Z  
    As per informix release notes BIGINT and BIGSERIAL data types have the same range of values as INT8 and SERIAL8 data types. However, BIGINT and BIGSERIAL have advantages for storage and computation over INT8 and SERIAL8.

    I am also facing the same issue with Informix ODBC driver.
    But -114 is for SQL Unknown datatype.Please clarify the same.
    How to use BIGINT and BIGSERIAL in the application for fetching result set from Informix database using Informix ODBC driver.
    I believe it is a bug in Informix ODBC driver to return data type as -114 (SQL_INFX_BIGINT) for bigint and bigserial (should return SQL_BIGINT) when driver option 'Report Stardard ODBC Types Only' is set in the DSN Advanced options tab.

    You can still handle the -114 by including infxcli.h in your project (see ODBC Driver Programmer's Manual) and binding bigint/bigserial columns as SQL_C_SBIGINT or SQL_C_UBIGINT (handle same as bigint).

    Another bug with the ODBC driver is that it reports BIGSERIAL data type is signed rather than unsigned data type in SQLGetTypeInfo and SQLColAttribute SQL_DESC_UNSIGNED.
  • SumitGoyal
    SumitGoyal
    7 Posts

    Re: bigint/int8 bigserial/serial8 difference

    ‏2010-04-15T09:50:19Z  
    Hi Farid,

    Thanks for the reply.

    I have come accross one more issue while implementing the functionality for fetching result using Informix Native Client 3.5 TC6.
    Since we are currently using informix sdk 2.6 it is returning datatype value for BIGSERIAL as 53 and BIGINT as 52 but when the user caches the meta data information using the informix.syscolumns table it returns datatype value for BIGSERIAL as 309 where as for BIGINT as 52 .
    So we have to catch the scenario for BIGSERIAL as well to have datatype value as 309. Seems to a problem with informix Native Client. Please check and confirm from your side too.

    The work around that you have provided by including infxcli.h in my project is not working as i cannot do that at present and binding bigint/bigserial columns as SQL_C_SBIGINT or SQL_C_UBIGINT (handle same as bigint). Please provide some other work around if you have.

    I have tried one work around that i am sharing with you is that for BIGSERIAL and BIGINT , ODBC SQLDescribeCol returns datatype column as typeInt64 and size as 8 so we can compare that and implement using the same.Please suggest if it is ok for this point of time.

    Regards,
    Sumit
  • andreasl
    andreasl
    401 Posts

    Re: bigint/int8 bigserial/serial8 difference

    ‏2010-04-15T11:18:24Z  
    Hi Farid,

    Thanks for the reply.

    I have come accross one more issue while implementing the functionality for fetching result using Informix Native Client 3.5 TC6.
    Since we are currently using informix sdk 2.6 it is returning datatype value for BIGSERIAL as 53 and BIGINT as 52 but when the user caches the meta data information using the informix.syscolumns table it returns datatype value for BIGSERIAL as 309 where as for BIGINT as 52 .
    So we have to catch the scenario for BIGSERIAL as well to have datatype value as 309. Seems to a problem with informix Native Client. Please check and confirm from your side too.

    The work around that you have provided by including infxcli.h in my project is not working as i cannot do that at present and binding bigint/bigserial columns as SQL_C_SBIGINT or SQL_C_UBIGINT (handle same as bigint). Please provide some other work around if you have.

    I have tried one work around that i am sharing with you is that for BIGSERIAL and BIGINT , ODBC SQLDescribeCol returns datatype column as typeInt64 and size as 8 so we can compare that and implement using the same.Please suggest if it is ok for this point of time.

    Regards,
    Sumit
    Hello Farid,

    309 = 53 + 256, or rather (53|0x100) ... and the 0x100 encodes SQLNONULL, so you really get back 53 if you mask what you get with 0x7f.

    HTH,
    Andreas
  • SystemAdmin
    SystemAdmin
    1143 Posts

    Re: bigint/int8 bigserial/serial8 difference

    ‏2010-04-15T13:43:35Z  
    Hi Farid,

    Thanks for the reply.

    I have come accross one more issue while implementing the functionality for fetching result using Informix Native Client 3.5 TC6.
    Since we are currently using informix sdk 2.6 it is returning datatype value for BIGSERIAL as 53 and BIGINT as 52 but when the user caches the meta data information using the informix.syscolumns table it returns datatype value for BIGSERIAL as 309 where as for BIGINT as 52 .
    So we have to catch the scenario for BIGSERIAL as well to have datatype value as 309. Seems to a problem with informix Native Client. Please check and confirm from your side too.

    The work around that you have provided by including infxcli.h in my project is not working as i cannot do that at present and binding bigint/bigserial columns as SQL_C_SBIGINT or SQL_C_UBIGINT (handle same as bigint). Please provide some other work around if you have.

    I have tried one work around that i am sharing with you is that for BIGSERIAL and BIGINT , ODBC SQLDescribeCol returns datatype column as typeInt64 and size as 8 so we can compare that and implement using the same.Please suggest if it is ok for this point of time.

    Regards,
    Sumit
    I am using SQLColAttribute/SQL_DESC_CONCISE_TYPE and have 'Report Stardard ODBC Types Only' option checked in driver DSN setup dialog and the only issue I had is with bigserial/serial8 returning -114. I normally do not use SQLDescribeCol at all.
  • SumitGoyal
    SumitGoyal
    7 Posts

    Re: bigint/int8 bigserial/serial8 difference

    ‏2010-04-29T07:46:19Z  
    Hi,

    We have come across a issue on IBM AIX where in we are facing the issue with BIGINT and BIGSERIAL implementation using Informix native client 3.5 UC6 and server as Informix IDS 11.5.
    While retrieving data from the table containing bigint and bigserial column. Native client is giving error as :- " The datatype (BIGINT/BIGSERIAL) is not supported in the current client server confirguration". The same implementation is working fine on Solaris and Windows with same client and server confirguration.

    Please guide us in resolving the issue.Is there some issue with native client ?

    Regards,
    Sumit
  • SumitGoyal
    SumitGoyal
    7 Posts

    Re: bigint/int8 bigserial/serial8 difference

    ‏2010-05-13T03:31:29Z  
    Hi,

    We have come across a issue on IBM AIX where in we are facing the issue with BIGINT and BIGSERIAL implementation using Informix native client 3.5 UC6 and server as Informix IDS 11.5.
    While retrieving data from the table containing bigint and bigserial column. Native client is giving error as :- " The datatype (BIGINT/BIGSERIAL) is not supported in the current client server confirguration". The same implementation is working fine on Solaris and Windows with same client and server confirguration.

    Please guide us in resolving the issue.Is there some issue with native client ?

    Regards,
    Sumit
    Everything is working fine on HPUX,SOLARIS and WINDOWS.

    AIX still has a problem.
    We are using client sdk 2.7. Please suggest if we need to take care of some extra point while implementing the changes on AIX.

    One thing that came to our notice is we are building the application using *.a libraries but with current client 3.5 UC1 and UC6 we are having *.so files but no .a files present.
  • SumitGoyal
    SumitGoyal
    7 Posts

    Re: bigint/int8 bigserial/serial8 difference

    ‏2010-05-17T07:47:39Z  
    Everything is working fine on HPUX,SOLARIS and WINDOWS.

    AIX still has a problem.
    We are using client sdk 2.7. Please suggest if we need to take care of some extra point while implementing the changes on AIX.

    One thing that came to our notice is we are building the application using *.a libraries but with current client 3.5 UC1 and UC6 we are having *.so files but no .a files present.
    Hi,

    I have created the sample program on AIX and everything seems to be working fine with that but the libraries that it is using is libif* . Our application is built using libth* that is thread libraries.

    I am able to compile my sample application using libth* libraries but i am getting the following error while executing in DB connection:-

    -406 Memory allocation failed.

    Sample application is executed as /isvdb/informix/sdk/2.90/bin/esql -lpthreads -lm -ldl -ltli_r -thread infPrepare.ec.

    and the db connection where i am getting the above sql error is

    EXEC SQL CONNECT TO :dbname USER :userid USING :password;

    Please suggest if i am missing some flag in compiling the sample code or i need to do some thing else on AIX for using thread libraries.
  • andreasl
    andreasl
    401 Posts

    Re: bigint/int8 bigserial/serial8 difference

    ‏2010-05-17T08:19:09Z  
    Hi,

    I have created the sample program on AIX and everything seems to be working fine with that but the libraries that it is using is libif* . Our application is built using libth* that is thread libraries.

    I am able to compile my sample application using libth* libraries but i am getting the following error while executing in DB connection:-

    -406 Memory allocation failed.

    Sample application is executed as /isvdb/informix/sdk/2.90/bin/esql -lpthreads -lm -ldl -ltli_r -thread infPrepare.ec.

    and the db connection where i am getting the above sql error is

    EXEC SQL CONNECT TO :dbname USER :userid USING :password;

    Please suggest if i am missing some flag in compiling the sample code or i need to do some thing else on AIX for using thread libraries.
    Hello,

    here's the 'esql' usage from a 3.50.FC6 sdk on AIX - not sure what 2.90 looks like exactly (don't have one handy), but "-thread" should be what you're after:

    
    Usage: esql [-e] [-thread] [-glu] [esqlcargs] [-cc] [-db2] [otherargs] [-o outfile] [-cp] [-onlycp] [-np] [-nup] [-libs] esqlfile.ec [othersrc.c...] [otherobj.o...] [-lyourlib...] -e         Preprocess only, no compilation or linking -thread    Multithread support -glu       Enable GLU (GLS 
    
    for Unicode) -db2       Enable Connectivity to DB2 esqlcargs: esqlc arguments (-g, -G, -nln, -Ipathname, -nowarn, -V, -ansi, -xopen, -local, -log, -EDname, -EUname, -icheck) -cc        Arguments after cc go to c compiler only otherargs: Other arguments are passed to cc -o         Next argument is program name -libs      Display the list of libraries used by esql at link time. -cp        Run C preprocessor before esqlc -onlycp    Run only the C preprocessor, no esqlc, compilation or linking -np        No protection of SQL keywords in SQL statements -nup       No unprotection of SQL keywords, forces -onlycp   Usage: esqlc [-thread] [-gG] [-nln] [-Ipathname] [-nowarn] [-V] [-ansi] [-
    
    static] [-xopen] [-local] [-log file] [-EDname[=val]] [-EUname] [-icheck] [-keepccomment] [-version] esqlfile.ec -thread     Multithread support -g          Number every line (debugging purposes) -G          No line number (debugging purposes; same as -nln) -nln        No line number (debugging purposes; same as -G) -Ipathname  Add pathname to include file search path -nowarn     Do not print warnings -
    
    static     Link with 
    
    static libraries -keepccomment Allow C style comments in SQL statements. -version    Displays build and version information. -V          Print preprocessor version information -ansi       Perform ANSI checking -xopen      Perform XOPEN checking -local      Make cursor/statement ids local to the file -log file   Log error and warning messages in file -EDname     Define specified preprocessor name flag [=val]     and set it equal to 
    'val' -EUname     Undefine specified preprocessor name flag -icheck     Check 
    
    for indicator variables
    


    HTH,
    -Andreas
  • SumitGoyal
    SumitGoyal
    7 Posts

    Re: bigint/int8 bigserial/serial8 difference

    ‏2010-05-25T08:07:35Z  
    Hi,

    I am able to fetch data using the thread libraries in my sample application but i am still getting the same error(-9272 in SQL PREPARE) in main application on AIX.

    Following is the scenario i am working on :-

    1. Creating a library using the following flags :-
    ==> Executing the .ec file --- esql -e -g -thread file.ec
    ==> Compiling the .c file --- xlC_r -qflag=w:w -qarch=com -qchars=signed -qoptimize=2 -qnolm -qlanglvl=ansi -DANSI -qlonglong -DIFX_THREAD -I/informixsdk/2.70/incl -I/informixsdk/2.70/incl/esql -c file.c -o file.o
    ==> Creating the archive file -- ar r libinf.a

    Please suggest if i have add any flag on AIX platform specific to bigint or bigserial or need to take care of something extra.

    Thanks in advance,

    Sumit
  • SumitGoyal
    SumitGoyal
    7 Posts

    Re: bigint/int8 bigserial/serial8 difference

    ‏2010-05-27T07:59:14Z  
    Hi,

    I am able to fetch data using the thread libraries in my sample application but i am still getting the same error(-9272 in SQL PREPARE) in main application on AIX.

    Following is the scenario i am working on :-

    1. Creating a library using the following flags :-
    ==> Executing the .ec file --- esql -e -g -thread file.ec
    ==> Compiling the .c file --- xlC_r -qflag=w:w -qarch=com -qchars=signed -qoptimize=2 -qnolm -qlanglvl=ansi -DANSI -qlonglong -DIFX_THREAD -I/informixsdk/2.70/incl -I/informixsdk/2.70/incl/esql -c file.c -o file.o
    ==> Creating the archive file -- ar r libinf.a

    Please suggest if i have add any flag on AIX platform specific to bigint or bigserial or need to take care of something extra.

    Thanks in advance,

    Sumit
    I am able to reproduce the problem on AIX with the sample program by creating a library with the following flags :-

    INFORMIX_ROOT = $(ISV_ROOT)/informixsdk/2.70

    INFORMIX_ESQLC = $(INFORMIX_ROOT)/bin/esql
    INFORMIX_CFLAGS = -I$(INFORMIX_ROOT)/incl -I$(INFORMIX_ROOT)/incl/esql

    1. on aix ###############################
    ifeq ($(shell uname), AIX)
    INFORMIX_LIB = -L$(INFORMIX_ROOT)/lib \
    -L$(INFORMIX_ROOT)/lib/esql \
    -lthsql -lthasf -lthgen -lthos -lifgls -lifglx \
    $(INFORMIX_ROOT)/lib/netstub.a \
    -lc_r -lmsaa_r -lbsd_r \
    $(INFORMIX_ROOT)/lib/esql/libifglx.a \
    -lpthreads -lm -ldl -ltli_r

    endif
    ifeq ($(shell uname), AIX)
    CC=/usr/vacpp/bin/xlc_r
    CXX=/usr/vacpp/bin/xlC_r
    ifeq ($(DEBUG), 1)
    CFLAGS=-g -qfuncsect -qflag=w:w -qmakedep -qchars=signed -qarch=com -bernotok -bnoipath
    else
    CFLAGS=-qoptimize -qfuncsect -qflag=w:w -qmakedep -qchars=signed -qarch=com -bernotok -bnoipath
    endif
    LD=/usr/vacpp/bin/makeC++SharedLib_r -L/usr/vac/lib
    LD+=-p5002
    LDEXE=/usr/vacpp/bin/xlC_r
    LDEXEFLAGS=-blibpath:.:/usr/lib:/lib
    LDFLAGS=-blibpath:.:/usr/lib:/lib
    OSTYPE=aix
    PICFLAG=
    EXT=so
    AR=/usr/ccs/bin/ar -clr
    endif
    The library that i am creating is informix.a
    While using this library through a c program , i am compiling the application as follows :-
    /usr/vacpp/bin/xlC_r -L/aix/informixsdk/2.70/lib -L/aix/informixsdk/2.70/lib/esql -lc_r -lmsaa_r -lbsd_r -lpthreads -lm -ldl -ltli_r -lthsql -lthasf -lthgen -lthos -lifgls -lifglx infMain.c ./lib/informix.a

    While executing the application foloowing env variables are set :-
    INFORMIXDIR=/informix_connect/3.5.0.uc6.2
    INFORMIXSERVER=inf115w2k3
    INFORMIXSQLHOSTS=/informix_connect/3.5.0.uc6.2/sqlhosts
    LIBPATH=.:/informix_connect/3.5.0.uc6.2/lib:/informix_connect/3.5.0.uc6.2/lib/esql

    With the above configuration i am getting the error number -9272 in EXEX SQL PREPARE .

    Please suggest if i have missed some flag or i am doing something wrong on AIX.

    Regards,
    Sumit