sybperl-l Archive

From: Nohez <nohez at cmie dot com>
Subject: DATE datatype retrieval
Date: Feb 9 2007 8:29AM

We are currently facing a problem while using Sybase::CTlib when
the table has a "DATE" datatype. I have attached a sample program
below.


    use Sybase::CTlib;
    my($sybptr) = new Sybase::CTlib "selector";
    die "Sybase Server Not Available\n" if ($sybptr == -1);

    my($str);

    # the order has to be date first, 
    # followed by atleast 2 char fields of any length
    # followed by a date or a datetime field

    $str= "
	create table #t
	(
	id         int         NULL,
	dt1        date        NULL,
	char_fld1  varchar(1)  NULL,
	char_fld2  varchar(1)  NULL,
	dt2        datetime    NULL
	)";

    &proc($sybptr, $str);
    &proc($sybptr, "insert into #t(id, dt1, char_fld1, char_fld2, dt2) values(1, '20070210', 'N',  'N',  getdate())");
    &proc($sybptr, "insert into #t(id, dt1, char_fld1, char_fld2, dt2) values(2, '20070210', 'N',  'N',  null)");
    &proc($sybptr, "insert into #t(id, dt1, char_fld1, char_fld2, dt2) values(3, '20070210', 'N',  null, getdate())");
    &proc($sybptr, "insert into #t(id, dt1, char_fld1, char_fld2, dt2) values(4, '20070210', null, 'N',  getdate())");
    &proc($sybptr, "insert into #t(id, dt1, char_fld1, char_fld2, dt2) values(5, null,       'N',  'N',  getdate())");

    $sybptr->ct_execute("select * from #t");
    while($sybptr->ct_results($restype) == CS_SUCCEED)
	{
	my(@data);
	if($restype == CS_CMD_FAIL)
	    {
	    warn "Command failed!";
	    next;
	    }
	next unless $sybptr->ct_fetchable($restype);
	while(@data = $sybptr->ct_fetch)
	    {
	    print join("|", @data), "\n";
	    };
	};

    sub proc
	{
	my($syb, $str)= @_;
	$syb->ct_sql($str);
	};


On execution of the above script we get the following error message:


    Open Client Message:
    Message number: LAYER = (1) ORIGIN = (4) SEVERITY = (1) NUMBER = (132)
    Message String: ct_fetch(): user api layer: internal common library error: The bind of result set item 2 resulted in truncation.
    1|Feb 1+|N|N|Feb  9 2007 12:40PM

    Open Client Message:
    Message number: LAYER = (1) ORIGIN = (4) SEVERITY = (1) NUMBER = (132)
    Message String: ct_fetch(): user api layer: internal common library error: The bind of result set item 2 resulted in truncation.
    2|Feb 1+|N|N|

    Open Client Message:
    Message number: LAYER = (1) ORIGIN = (4) SEVERITY = (1) NUMBER = (132)
    Message String: ct_fetch(): user api layer: internal common library error: The bind of result set item 2 resulted in truncation.
    3|Feb 1+|N||Feb  9 2007 12:40PM

    Open Client Message:
    Message number: LAYER = (1) ORIGIN = (4) SEVERITY = (1) NUMBER = (132)
    Message String: ct_fetch(): user api layer: internal common library error: The bind of result set item 2 resulted in truncation.
    4|Feb 1+||N|Feb  9 2007 12:40PM
    5||N|N|Feb  9 2007 12:40PM



We tried the same with DBLIB.



    use Sybase::DBlib;
    my($sybptr) = new Sybase::DBlib "selector";
    die "Sybase Server Not Available\n" if ($sybptr == -1);

    my($str);

    # the order has to be date first, 
    # followed by atleast 2 char fields of any length
    # followed by a date or a datetime field

    $str= "
	create table #t
	(
	id         int         NULL,
	dt1        date        NULL,
	char_fld1  varchar(1)  NULL,
	char_fld2  varchar(1)  NULL,
	dt2        datetime    NULL
	)";

    &proc($sybptr, $str);
    &proc($sybptr, "insert into #t(id, dt1, char_fld1, char_fld2, dt2) values(1, '20070210', 'N',  'N',  getdate())");
    &proc($sybptr, "insert into #t(id, dt1, char_fld1, char_fld2, dt2) values(2, '20070210', 'N',  'N',  null)");
    &proc($sybptr, "insert into #t(id, dt1, char_fld1, char_fld2, dt2) values(3, '20070210', 'N',  null, getdate())");
    &proc($sybptr, "insert into #t(id, dt1, char_fld1, char_fld2, dt2) values(4, '20070210', null, 'N',  getdate())");
    &proc($sybptr, "insert into #t(id, dt1, char_fld1, char_fld2, dt2) values(5, null,       'N',  'N',  getdate())");

    $sybptr->dbcmd("select * from #t");
    $sybptr->dbsqlexec;
    while($sybptr->dbresults != &NO_MORE_RESULTS)
	{
	my(@data);
	while(@data = $sybptr->dbnextrow)
	    {
	    print join("|", @data), "\n";
	    };
	};

    sub proc
	{
	my($syb, $str)= @_;
	$syb->dbcmd($str);
	$syb->dbsqlexec;
	};



    1|Feb 10 2007 12:00:00:000AM|N|N|Feb  9 2007 12:41:48:840PM
    2|Feb 10 2007 12:00:00:000AM|N|N|
    3|Feb 10 2007 12:00:00:000AM|N||Feb  9 2007 12:41:48:840PM
    4|Feb 10 2007 12:00:00:000AM||N|Feb  9 2007 12:41:48:840PM
    Msg 247, Level 16, State 2
    Server 'SYBASE_SEC', Line 1
	    Arithmetic overflow during implicit conversion of DATE value 'Apr 16 /0++' to a DATETIME field .
    DB-Library error:
	    General SQL Server error: Check messages from the SQL Server.


I checked the FAQ question 3.1 which says that Sybperl 2.15 and later
supports DATE columns provided Open Client supports these features.
I have yet to verify that OpenClient supports this.  The SQL in
the script above works as expected when we execute them using isql.
BCP in/out also works correctly. Is it ok to assume that since isql &
bcp works correctly then openclient also supports it ?

Does any one have a clue on what is wrong ?

Our environment: Linux, Perl: v5.8.8 built for x86_64-linux-thread-multi with Sybperl 2.18
@@version =  Adaptive Server Enterprise/12.5.3/EBF 13339 ESD#7/P/x86_64/Enterprise Linux/ase 1253/1951/64-bit/OPT/Fri Mar 24 00:39:11 2006

Thanks

Nohez