Query Long Varchar via ODBC

verban

I want to query a table's content that has a 'Long Varchar' data type.
I tested via vsql and it fetched whole data. but isql cannot fetch more than 300 bytes!!!

Also tested via PHP but it fetched only 65535 bytes!!

I guess this is an odbc limitation, Is there any configuration for it?
How can i fetch the field completely in PHP?

---

Vertica version: 9.2
Vertica Driver: 9.2
unixodbc version: 2.2.14p2-5ubuntu5
Php5-odbc version: 5.5.9

marcothesane

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 -

Bryan_H

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

verban

@marcothesane,

this is what id did:

-- Create Table
CREATE TABLE public.Raw (
    id int,
    value long varchar(500000)
);

-- Insert a new record with 100K length
insert into Raw(id, value)
select 1, REPEAT('a'::LONG VARCHAR, 100000);

-- Query the result
SELECT LENGTH(value) val, value, LEFT(VALUE::VARCHAR, 300), LEFT(VALUE::VARCHAR, 301), SUBSTRING(VALUE, 0, 300), SUBSTRING(VALUE, 0, 302) from Raw WHERE id=1;

Result is Ok in dbeaver:

and result in isql is missed (it returns only 300bytes fields not others) :

and this is my PHP Code:
<?php $conn = odbc_connect('VerticaDB1', '', ''); $stmt = odbc_prepare($conn, 'SELECT LENGTH(value) len, value from Raw WHERE id=1;'); $res = odbc_execute($stmt); while ($row = odbc_fetch_object($stmt)) { echo $row->len; echo '
'; echo strlen($row->value); } ?>

But PHP returns only 65535 bytes of record (white space is the result in browser)!!

@Bryan_H , Thanks your reply.
I checked the longreadlen handler, but doesn't change any thing.
would you please check it in your Lab?

Bryan_H

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.

verban

@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!