Re: dbi odbc return string getting truncated

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

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.

Comment on Re: dbi odbc return string getting truncated Select or Download Code

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"?	[reply]
Re^3: dbi odbc return string getting truncated by afoken (Chancellor) on Aug 07, 2019 at 18:42 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). 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` [download] 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` [download] 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". ;-)	[reply] [d/l] [select]
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?	[reply]
Re^3: dbi odbc return string getting truncated by hippo (Bishop) on Aug 08, 2019 at 12:51 UTC
is there any tracing available for DBI? Yes, there is. Just open up the DBI docs and search for tracing.	[reply]
Re^3: dbi odbc return string getting truncated by talexb (Chancellor) on Aug 08, 2019 at 14:41 UTC
- 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.	[reply]
Re^4: dbi odbc return string getting truncated by perlnewbiedewbie (Initiate) on Aug 09, 2019 at 10:16 UTC
Tracing gives me that: `<- fetchrow_arrayref= ( [ 'S...T...A.........' ] ) [1 items] row1 at + test.perl line 36 STA` [download] 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` [download] Is it a Unicode problem?	[reply] [d/l] [select]
Re^5: dbi odbc return string getting truncated by erix (Prior) on Aug 09, 2019 at 10:51 UTC

In Section Seekers of Perl Wisdom