Postgres Pro
Downloads
Home   >   mailing lists

Thread: Insert with bound columns

Insert with bound columns

From
Andreas Pflug
Date:
Did anybody succeed in inserting empty string columns using bound columns?

INSERT INTO testtab (keyCol, strCol) VALUES  (1, '')  or (1, NULL)
succeeds, but

INSERT INTO testtab (keyCol, strCol) VALUES (?,?) does not.

I debugged the driver and found that SQLNumParams will return 2 for the
bound column version,which is obviously correct. Unfortunately, MSDASQL
seems to expect 1 (I checked  by changing it with debugger), but that
will make the ODBC driver unhappy. I changed the database access class
to replace all empty strings with a one-space string. This made MSDASQL
happy, but will not be reasonable for production.

I'm using native OLEDB, thus MSDASQL is my provider using ODBC 7.2.5
from source.
Feedback from ADO users is welcome, they're using OLEDB implicitely.

I checked MSDN and Technet for this,and traced the output of PGadmin II
which is not using bound columns so it's not helpful to me.

Any hints?

Andreas

Re: Insert with bound columns

From
Andreas Pflug
Date:
After experimenting heavily, I found out some more.
MSDASQL doesn't like a NULL-pointer as string in the buffer to be
written. Instead, a SysAllocString(L"") is needed. This is acceptable,
if an zero length string should be inserted. If the status field of the
buffer ist not set to DBSTATUS_S_OK indicating valid data to be written,
but to DBSTATUS_S_ISNULL for a NULL to be inserted, MSDASQL will fail
with "Parameter type cannot be determined for at leas one variant
parameter". Actually, it's no variant given, but a DBTYPE_BSTR, and this
information is put correctly into the DBBINDING structure. Still, I
believe ADO users should be unable to insert a NULL string into a
VARCHAR column (if not omitted in the query).

The same (OLEDB side) code works with SQLSRV32, and MSDASQL blows as
soon as it gets to know the number of parameters, so it seems that for
SQLSRV32 MSDASQL can determine the parameter type from the SQL server
driver, but not with PostgreSQL. Does this switch on a light for
anybody, saying "ah now I know what SQLxxyy is for"? If pointed towards
some topic, I could try to implement it.

Regards,

Andreas

Re: Insert with bound columns

From
Hiroshi Inoue
Date:
Andreas Pflug wrote:
>
> After experimenting heavily, I found out some more.
> MSDASQL doesn't like a NULL-pointer as string in the buffer to be
> written. Instead, a SysAllocString(L"") is needed. This is acceptable,
> if an zero length string should be inserted. If the status field of the
> buffer ist not set to DBSTATUS_S_OK indicating valid data to be written,
> but to DBSTATUS_S_ISNULL for a NULL to be inserted, MSDASQL will fail
> with "Parameter type cannot be determined for at leas one variant
> parameter". Actually, it's no variant given, but a DBTYPE_BSTR, and this
> information is put correctly into the DBBINDING structure. Still, I
> believe ADO users should be unable to insert a NULL string into a
> VARCHAR column (if not omitted in the query).

I can insert a '' or NULL using ADO.Parameter in VB.

> The same (OLEDB side) code works with SQLSRV32, and MSDASQL blows as
> soon as it gets to know the number of parameters, so it seems that for
> SQLSRV32 MSDASQL can determine the parameter type from the SQL server
> driver, but not with PostgreSQL.

The psqlodbc driver couldn't determine the parameter type
unless the upper application specifies it explicitly.

> Does this switch on a light for
> anybody, saying "ah now I know what SQLxxyy is for"? If pointed towards
> some topic, I could try to implement it.

Maybe SQLDescribeParam should be supported though I'm
not sure.

regards,
Hiroshi Inoue
    http://www.geocities.jp/inocchichichi/psqlodbc/