Bind Parameter is Too Big
I support a system where staff use a PowerBuilder 2019R3 app that enters data into a Postgres 12 database, generates SQL statements for the data and saves them as text files. Staff uses another PB2019R3 app to populate a Sybase ASE 15.7 database with the generated SQL statement files. Other staff then uses a PB2019R3 app to pipe the data to a master Sybase ASE 15.7 database. At the beginning of each week that data is piped via PB2019R3 app to another Sybase ASE 15.7 database. The inspectors then update their Postgres database with all inspectors' data via another PB2019R3 app that pipes the data from Sybase to Postgres.
* Yes, I understand how absurd the system is. I just inherited it and am supporting it until we can replace it.
I recently updated two comment columns in two tables, both on the Sybase and Postgres sides, from varchar (255) to varchar(1000). The app can succesfully input data with up to 1000 characters to the updated columns in Postgres. The generated SQL statement files can insert the data into the first Sybase database, the data successfully pipes to the master Sybase database, and then successfully pipes that to the other Sybase database . However, when trying to pipe the data from Sybase to Postgres I get the error "Bind Parameter for value :9 is Too Big (2000)." The comment column is the 9th column.
I checked the data and verified it isn't over the 1000 character limit. I tried deleting out 13 of the 1000 characters, and the error this time was "Bind Parameter for value :9 is Too Big (1976)."
Via Appeon's support forums I worked out getting the pipe to work in the PowerBuilder IDE by enabling staticbind and disablebind. In this view, I can get a connection string to use in the app's configuration file. However, using this connection string in the app's configuration file still doesn't work when running the pipe in the app.
Connection string is: DBParm=ConnectString='DSN=WM_LT_User;UID=user;PWD=password',PBCatalogOwner='user',DelimitIdentifier='Yes',DisableBind=1
Appeon is claiming it is a Postgres issue and so I am inquiring here. I am hoping someone can help steer me in the right direction to get this pipe working.
Thanks
Matt Kluzak
Technology Director
North Dakota Public Service Commission
600 E Boulevard Ave
Dept 408 13th Floor
Bismarck, ND 58505-0480
Phone: 701-328-4075
________________________________
This transmission, email and any files transmitted with it, may be: (1) subject to the Attorney-Client Privilege, (2) an attorney work product, or (3) strictly confidential under federal or state law. If you are not the intended recipient of this message, you may not use, disclose, print, copy or disseminate this information. If you have received this transmission in error, notify the sender (only) and delete the message. This message may also be subject to disclosure under the North Dakota Open Records Laws.
On 9/1/22 1:34 PM, Kluzak, Matthew C. wrote:
I support a system where staff use a PowerBuilder 2019R3 app that enters
data into a Postgres 12 database, generates SQL statements for the data
and saves them as text files. Staff uses another PB2019R3 app to
populate a Sybase ASE 15.7 database with the generated SQL statement
files. Other staff then uses a PB2019R3 app to pipe the data to a master
Sybase ASE 15.7 database. At the beginning of each week that data is
piped via PB2019R3 app to another Sybase ASE 15.7 database. The
inspectors then update their Postgres database with all inspectors' data
via another PB2019R3 app that pipes the data from Sybase to Postgres.* Yes, I understand how absurd the system is. I just inherited it and
am supporting it until we can replace it.
The .gov domain was explanation enough.
However, when trying to pipe the data from Sybase to Postgres
I get the error "Bind Parameter for value :9 is Too Big (2000)." The
comment column is the 9th column.I checked the data and verified it isn't over the 1000 character limit.
I tried deleting out 13 of the 1000 characters, and the error this time
was "Bind Parameter for value :9 is Too Big (1976)."
What is the actual code that pipes the data?
What is the encoding/character set in Sybase vs Postgres?
Via Appeon�s support forums I worked out getting the pipe to work in the
PowerBuilder IDE by enabling staticbind and disablebind. In this view, I
can get a connection string to use in the app�s configuration file.
However, using this connection string in the app�s configuration file
still doesn�t work when running the pipe in the app.Connection string is:
DBParm=ConnectString='DSN=WM_LT_User;UID=user;PWD=password',PBCatalogOwner='user',DelimitIdentifier='Yes',DisableBind=1Appeon is claiming it is a Postgres issue and so I am inquiring here. I
am hoping someone can help steer me in the right direction to get this
pipe working.Thanks
Matt Kluzak
Technology Director
North Dakota Public Service Commission
600 E Boulevard Ave
Dept 408 13^th Floor
Bismarck, ND� 58505-0480
Phone: 701-328-4075
------------------------------------------------------------------------
This transmission, email and any files transmitted with it, may be: (1)
subject to the Attorney-Client Privilege, (2) an attorney work product,
or (3) strictly confidential under federal or state law. If you are not
the intended recipient of this message, you may not use, disclose,
print, copy or disseminate this information. If you have received this
transmission in error, notify the sender (only) and delete the message.
This message may also be subject to disclosure under the North Dakota
Open Records Laws.
--
Adrian Klaver
adrian.klaver@aklaver.com
"Kluzak, Matthew C." <mckluzak@nd.gov> writes:
I recently updated two comment columns in two tables, both on the Sybase and Postgres sides, from varchar (255) to varchar(1000). The app can succesfully input data with up to 1000 characters to the updated columns in Postgres. The generated SQL statement files can insert the data into the first Sybase database, the data successfully pipes to the master Sybase database, and then successfully pipes that to the other Sybase database . However, when trying to pipe the data from Sybase to Postgres I get the error "Bind Parameter for value :9 is Too Big (2000)." The comment column is the 9th column.
There is no such string in the Postgres source code (it doesn't even
meet our message style guidelines). So this is certainly not coming
from the Postgres server. You didn't specify what's the software
stack on the client side, but it has to be coming from one or another
layer there.
regards, tom lane
Hi Adrian,
The Sybase databases use
Character Set = 1, iso_1
ISO 8859-1 (Latin-1) - Western European 8-bit character set.
Sort Order = 50, bin_iso_1
Binary ordering, for the ISO 8859/1 or Latin-1 character set (
iso_1).
The Postgres database uses UTF8
Matt Kluzak
Technology Director
North Dakota Public Service Commission
600 E Boulevard Ave
Dept 408 13th Floor
Bismarck, ND 58505-0480
Phone: 701-328-4075
-----Original Message-----
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Thursday, September 01, 2022 3:46 PM
To: Kluzak, Matthew C. <mckluzak@nd.gov>; pgsql-general@lists.postgresql.org
Subject: Re: Bind Parameter is Too Big
***** CAUTION: This email originated from an outside source. Do not click links or open attachments unless you know they are safe. *****
On 9/1/22 1:34 PM, Kluzak, Matthew C. wrote:
I support a system where staff use a PowerBuilder 2019R3 app that
enters data into a Postgres 12 database, generates SQL statements for
the data and saves them as text files. Staff uses another PB2019R3 app
to populate a Sybase ASE 15.7 database with the generated SQL
statement files. Other staff then uses a PB2019R3 app to pipe the data
to a master Sybase ASE 15.7 database. At the beginning of each week
that data is piped via PB2019R3 app to another Sybase ASE 15.7
database. The inspectors then update their Postgres database with all
inspectors' data via another PB2019R3 app that pipes the data from Sybase to Postgres.* Yes, I understand how absurd the system is. I just inherited it and
am supporting it until we can replace it.
The .gov domain was explanation enough.
However, when trying to pipe the data from Sybase to Postgres I get
the error "Bind Parameter for value :9 is Too Big (2000)." The comment
column is the 9th column.I checked the data and verified it isn't over the 1000 character limit.
I tried deleting out 13 of the 1000 characters, and the error this
time was "Bind Parameter for value :9 is Too Big (1976)."
What is the actual code that pipes the data?
What is the encoding/character set in Sybase vs Postgres?
Via Appeon's support forums I worked out getting the pipe to work in
the PowerBuilder IDE by enabling staticbind and disablebind. In this
view, I can get a connection string to use in the app's configuration file.
However, using this connection string in the app's configuration file
still doesn't work when running the pipe in the app.Connection string is:
DBParm=ConnectString='DSN=WM_LT_User;UID=user;PWD=password',PBCatalogO
wner='user',DelimitIdentifier='Yes',DisableBind=1Appeon is claiming it is a Postgres issue and so I am inquiring here.
I am hoping someone can help steer me in the right direction to get
this pipe working.Thanks
Matt Kluzak
Technology Director
North Dakota Public Service Commission
600 E Boulevard Ave
Dept 408 13^th Floor
Bismarck, ND 58505-0480
Phone: 701-328-4075
----------------------------------------------------------------------
-- This transmission, email and any files transmitted with it, may be:
(1) subject to the Attorney-Client Privilege, (2) an attorney work
product, or (3) strictly confidential under federal or state law. If
you are not the intended recipient of this message, you may not use,
disclose, print, copy or disseminate this information. If you have
received this transmission in error, notify the sender (only) and
delete the message.
This message may also be subject to disclosure under the North Dakota
Open Records Laws.
--
Adrian Klaver
adrian.klaver@aklaver.com
On 9/1/22 1:51 PM, Kluzak, Matthew C. wrote:
Hi Adrian,
The Sybase databases use
Character Set = 1, iso_1
ISO 8859-1 (Latin-1) - Western European 8-bit character set.
Sort Order = 50, bin_iso_1
Binary ordering, for the ISO 8859/1 or Latin-1 character set (
iso_1).The Postgres database uses UTF8
And the code that pipes the data?
Matt Kluzak
Technology Director
North Dakota Public Service Commission
600 E Boulevard Ave
Dept 408 13th Floor
Bismarck, ND 58505-0480
Phone: 701-328-4075
--
Adrian Klaver
adrian.klaver@aklaver.com
"Kluzak, Matthew C." <mckluzak@nd.gov> writes:
The Sybase databases use
Character Set = 1, iso_1
ISO 8859-1 (Latin-1) - Western European 8-bit character set.
Sort Order = 50, bin_iso_1
Binary ordering, for the ISO 8859/1 or Latin-1 character set (
iso_1).
The Postgres database uses UTF8
Hmmm ... it's plausible that something somewhere is figuring that
ISO-8859-1 conversion to UTF8 could expand the data at most 2X,
and that's where the 1000-to-2000 multiplier is coming from.
I wonder if it would help to set things up so that the client
side is sending ISO-8859-1 to Postgres (ie, client_encoding = latin1)
and the encoding conversion happens on the server side.
In principle it shouldn't matter where the conversion happens,
but you might be dealing with some bug or underdocumented limitation
in whatever is doing that conversion on the client side.
regards, tom lane
After working between Appeon for Powerbuilder and SAP for Sybase ASE 15.7, I finally figured out the issue.
In the ODBC Driver Administrator, if I configure the postgres ODBC driver and go to Datasource, the Max Varchar limit is 255 by default. I changed this to 1000 and now everything works.
I will note, during my testing with Appeon, I discovered via DBtrace that all of my varchar columns report with twice the length of the actual amount a characters in them, when piping from Sybase ASE 15.7 to Postgres. Appeon experienced the same thing, but didn't have an explanation. SAP didn't have an explanation either.
-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Thursday, September 01, 2022 4:03 PM
To: Kluzak, Matthew C. <mckluzak@nd.gov>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org
Subject: Re: Bind Parameter is Too Big
***** CAUTION: This email originated from an outside source. Do not click links or open attachments unless you know they are safe. *****
"Kluzak, Matthew C." <mckluzak@nd.gov> writes:
The Sybase databases use
Character Set = 1, iso_1
ISO 8859-1 (Latin-1) - Western European 8-bit character set.
Sort Order = 50, bin_iso_1
Binary ordering, for the ISO 8859/1 or Latin-1 character set (
iso_1).
The Postgres database uses UTF8
Hmmm ... it's plausible that something somewhere is figuring that
ISO-8859-1 conversion to UTF8 could expand the data at most 2X, and that's where the 1000-to-2000 multiplier is coming from.
I wonder if it would help to set things up so that the client side is sending ISO-8859-1 to Postgres (ie, client_encoding = latin1) and the encoding conversion happens on the server side.
In principle it shouldn't matter where the conversion happens, but you might be dealing with some bug or underdocumented limitation in whatever is doing that conversion on the client side.
regards, tom lane
________________________________
This transmission, email and any files transmitted with it, may be: (1) subject to the Attorney-Client Privilege, (2) an attorney work product, or (3) strictly confidential under federal or state law. If you are not the intended recipient of this message, you may not use, disclose, print, copy or disseminate this information. If you have received this transmission in error, notify the sender (only) and delete the message. This message may also be subject to disclosure under the North Dakota Open Records Laws.