Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

dbi odbc return string getting truncated

by perlnewbiedewbie (Initiate)
on Aug 07, 2019 at 08:03 UTC ( #11104086=perlquestion: print w/replies, xml ) Need Help??

perlnewbiedewbie has asked for the wisdom of the Perl Monks concerning the following question:

I'm trying to retrieve some string value from a clickhouse database. My script is:
use DBI qw':sql_types'; use utf8; use open ':encoding(utf8)', ':std'; use Encode; my $dbh = DBI->connect( 'dbi:ODBC:clickhouse' ); if ($DBI::errstr) { die <<ERR; Could not connect to database $DBI::errstr ERR } $dbh->{PrintError} = 1; $dbh->{RaiseError} = 1; # long truncated DBI attribute LongTruncOk not set and/or LongReadLen +too small # for fetching milion of rows $dbh->{LongReadLen} = 255 * 255; $dbh->{LongTruncOk} = 1; $dbh->{pg_enable_utf8} = 1; $dbh->{odbc_utf8_on} = 1; # prepare SQL statement # my $sth = $dbh->prepare("$_[0]") my $sth = $dbh->prepare("select CAST('STARTEND' AS String) as COLA, 'S +TARTEND' PEPSI") or die "prepare statement failed: $dbh->errstr()"; $sth->execute() or die "execution failed: ".$dbh->errstr(); # get column list definition #my @cols = @{$sth->{NAME}}; my @row; print "Fields: $sth->{NUM_OF_FIELDS}\n"; print "Params: $sth->{NUM_OF_PARAMS}\n\n"; print join("\t\t", @{$sth->{NAME}}), "\n\n"; while($row = $sth->fetchrow_arrayref) { print join("\t\t", @$row), "\n"; } $sth = undef;
When I execute it return two columns but the values of them are "STA". If the return string is wider then it always truncating last few characters. I don't know what is happening here. Is that an ODBC driver issue?

Replies are listed 'Best First'.
Re: dbi odbc return string getting truncated
by talexb (Canon) on Aug 07, 2019 at 13:24 UTC

    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.

      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". ;-)
      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.

Re: dbi odbc return string getting truncated
by jcb (Pilgrim) on Aug 09, 2019 at 23:38 UTC

    Check: you might actually have a database issue.

    I had not heard of ClickHouse before, and after looking it up, found that it supposedly uses an "SQL-like" language. Try retrieving some actual data. Returning literals from the query might not be supported.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://11104086]
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (5)
As of 2019-08-23 13:47 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?