Query Long Varchar via ODBC

Hi Verban -
I just tested using this perl script:

#!/usr/bin/perl
print "DROP TABLE IF EXISTS lv;\n";
print "CREATE TABLE lv(lv LONG VARCHAR);\n";
print "INSERT INTO lv VALUES(\n'";
for($i=0;$i<8192;$i++) {
  printf("%07i:%s\n", $i,"12345678912345678912345678912345678912345678912345678912345678912345678912345678912345678912345678900000000000123456789");
}
print "');\nCOMMIT;\n";

I called the script mkdata.pl, then made it executable using chmod a+x mkdata.pl, then I ran: ./mkdata.pl > test_lv.sql, and finally, I ran: vsql -f test_lv.sql .

Then, I ran: vsql -c "select * from lv" - and, indeed, it showed me all of the single column in my lv table.

Then, I ran my own ODBC client with select * from lv, and got all of that row. Also using odb, I got the same result.

It must be that isql deliberately reduces the length of LONG VARCHAR-s, at least if you use the very same approach I did here.

Also usinig this query:

SELECT 
  SUBSTRING(lv,0,301)   AS sub_300
, SUBSTRING(lv,0,302)   AS sub_302
, LEFT(lv::VARCHAR,300) AS left_300
, LEFT(lv::VARCHAR,301) AS left_301
FROM lv;

.. I got data in all 4 columns of the select ....

What did you do differently?

Cheers -

Hi, can you post a sample PHP code? Are you using PHP handler "longreadlen" for SQL long varchar/varbinary as shown here: https://www.php.net/manual/en/function.odbc-longreadlen.php

Hi, your sample code works for me, the output is "100000" as expected for both values.
I am running PHP 5.4.16 on lighttpd, Vertica 9.2.1-1, ODBC driver 9.2.1-1. I am not using any extra settings in odbc.ini or my php odbc_connect.
Can you check your /etc/odbc.ini and php.ini or phpinfo() to ensure there are no extra odbc settings? I think the relevant setting is "defaultbinmode" = "return as is". Also check "odbc.defaultlrl", Handling of LONG fields, specifies the number of bytes returned to variables.

@Bryan_H Thanks bro, it was fixed

but i don't know why result was 65535 bytes instead of 4096
i checked odbc.ini and php code again, there is no setting about defaultlrl!