Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

Re: dbi odbc return string getting truncated

by talexb (Canon)
on Aug 07, 2019 at 13:24 UTC ( #11104095=note: print w/replies, xml ) Need Help??


in reply to dbi odbc return string getting truncated

It's really difficult to tell what's going on here without a self-contained example. Without understanding what the problem is, it's difficult to propose a solution.

I'll take a shot at it, though .. your select statement

my $sth = $dbh->prepare( "select CAST('STARTEND' AS String) as COLA, 'STARTEND' PEPSI") or die "prepare statement failed: $dbh->errstr()";
doesn't seem right. I expect the SQL parser would complain about the second column 'STARTEND' PEPSI. It could be that's the source of the 'STA', though.

Please update your post (leaving the original the way it is, or by replying to this post) with a clearer example of what's going on.

Alex / talexb / Toronto

Thanks PJ. We owe you so much. Groklaw -- RIP -- 2003 to 2013.

Replies are listed 'Best First'.
Re^2: dbi odbc return string getting truncated
by soonix (Canon) on Aug 07, 2019 at 14:07 UTC
    I was astonished at first, too, but "AS" probably is optional. Don't know about OP's database, but for SQLite, it is for the result column (not in CAST, though).

    My next question would be: what table or column definitions are used when there is no "FROM"?

      I was astonished at first, too, but "AS" probably is optional. Don't know about OP's database, but for SQLite, it is for the result column (not in CAST, though).

      Yes, that's standard SQL. The AS in column-or-expression AS alias is optional.

      My next question would be: what table or column definitions are used when there is no "FROM"?

      None.

      SELECT 42 AS RESULT

      is perfectly legal SQL. It returns a single row with - in this case - a single column with a type matching the type of the expression left of AS. Only Oracle insists on a table or view, and it has a dummy table called DUAL for exactly that purpose. So, Oracle wants

      SELECT 42 AS RESULT FROM DUAL

      See also DUAL_table, Selecting from the DUAL table.

      Alexander

      --
      Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
Re^2: dbi odbc return string getting truncated
by perlnewbiedewbie (Initiate) on Aug 08, 2019 at 12:33 UTC
    Thanks for the response. As Alexander said AS is optional and no FROM is like selecting single columns. I just want to keep example simple. I'm selecting single string column from the database and it's getting truncated, when I connect from isql to ODBC connection it's fine, so I assume that the problem is with DBI/perl. I don't have enough knowledge about perl to debug this - is there any tracing available for DBI?
      is there any tracing available for DBI?

      Yes, there is. Just open up the DBI docs and search for tracing.

        - is there any tracing available for DBI?

      Yes! There is tracing built in to DBI. It's right there in the documentation.

      Alex / talexb / Toronto

      Thanks PJ. We owe you so much. Groklaw -- RIP -- 2003 to 2013.

        Tracing gives me that:
        <- fetchrow_arrayref= ( [ 'S...T...A.........' ] ) [1 items] row1 at + test.perl line 36 STA
        Which is strange, because when I execute it on PostgreSQL it gives:
        <- fetchrow_arrayref= ( [ 'STARTEND' ] ) [1 items] row1 at pgtest.pe +rl line 36 STARTEND
        Is it a Unicode problem?

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://11104095]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (2)
As of 2021-10-25 04:50 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My first memorable Perl project was:







    Results (89 votes). Check out past polls.

    Notices?