Query Long Varchar via ODBC

verbanverban Vertica Customer

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



  • Options
    marcothesanemarcothesane - Select Field - Administrator

    Hi Verban -
    I just tested using this perl script:

    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:

      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 -

  • Options
    Bryan_HBryan_H Vertica Employee Administrator

    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

  • Options
    verbanverban Vertica Customer


    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?

  • Options
    Bryan_HBryan_H Vertica Employee Administrator
    edited July 2019

    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.

  • Options
    verbanverban Vertica Customer
    edited July 2019

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file