Thread: Help: Change the SQL query length

Help: Change the SQL query length

From
Marcos Cruz
Date:
HI,

I'm changing a database from Access'97 to PostgreSQL 7.1.1 and
found a query limit when a I have large text field into the SQL
insert.

The returned msg is like " query too long, length > 8191 octets"

The conection is done through the ODBC driver, with:
     text as LongVarChar
    Cache size = 100 (default)
     max VarChar = 254 (default)
     max LongVarChar = 65534 (changed)

The postmaster was running with default values.

If I use the psql utility into the server machine, I can insert the
record
successfully.

Has the postgresql_odbc or psqlodbc_07_01_005 any limitation with
regards
the query length?

The system frontend is running in Windows, and I need use ODBC to
connect
to the Linux server.

Thanks for any help!

Marcos Cruz
ITI - Instituto Nacional de Tecnologia da Informacao
Campinas - SP
Brazil

Re: Help: Change the SQL query length

From
Tom Lane
Date:
Marcos Cruz <Marcos.Cruz@iti.gov.br> writes:
> I'm changing a database from Access'97 to PostgreSQL 7.1.1 and
> found a query limit when a I have large text field into the SQL
> insert.

> The returned msg is like " query too long, length > 8191 octets"

Hm, I notice

#define TEXT_FIELD_SIZE             8190        /* size of text fields
                                                 * (not including null
                                                 * term) */

in src/interfaces/odbc/psqlodbc.h ... does changing that help?

            regards, tom lane

Re: [ODBC] Re: Help: Change the SQL query length

From
Hiroshi Inoue
Date:
Tom Lane wrote:
>
> Marcos Cruz <Marcos.Cruz@iti.gov.br> writes:
> > I'm changing a database from Access'97 to PostgreSQL 7.1.1 and
> > found a query limit when a I have large text field into the SQL
> > insert.
>
> > The returned msg is like " query too long, length > 8191 octets"

Which version of psqlodbc driver are you using ?
The max query length is 65536 in the latest driver.
However

>
> Hm, I notice
>
> #define TEXT_FIELD_SIZE             8190        /* size of text fields
>                                                  * (not including null
>                                                  * term) */
>
> in src/interfaces/odbc/psqlodbc.h ...

the limit of a text field is 8190 as above.
It seems that the size must be set via psqlodbc driver option
setting but it doesn't seem to work properly now.

regards,
Hiroshi Inoue