Обсуждение: ADO and ODBC: More
We've done some more investigation into the ODBC/ADO issue involving column
names with embedded spaces.
For the benefit of those that don't know anything about ADO, it makes up its
own SQL statements behind the scenes, so this can't be fixed simply by
changing our SQL query syntax.
For those that do, we're doing something on the lines of
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Postgres"
Set RS = Server.CreateObject("ADODB.RecordSet")
SQL = "SELECT ""child beds"" FROM ""dmsbookings"""
RS.Open SQL, Conn, 2, 3
RS("child beds") = 33
RS.Update
The SELECT query runs fine, since that gets passed as is through to the
database. When the recordset is updated, ADO creates its own update query.
As far as we can tell, the following sequence of activity goes on in ODBC:
**** SQLAllocStmt: hdbc = 41250488, stmt = 41295024
CC_add_statement: self=41250488, stmt=41295024
SQLSetStmtOption: entering...
SetStmtOption: SQL_QUERY_TIMEOUT, vParam = 30
SQLGetInfo: entering...fInfoType=29
SQLGetInfo: p='"', len=0, value=0, cbMax=4
SQLGetInfo: entering...fInfoType=41
SQLGetInfo: p='', len=0, value=0, cbMax=4
SQLGetInfo: entering...fInfoType=30
SQLGetInfo: p='<NULL>', len=2, value=32, cbMax=2
SQLGetInfo: entering...fInfoType=34
SQLGetInfo: p='<NULL>', len=2, value=0, cbMax=2
SQLGetInfo: entering...fInfoType=32
SQLGetInfo: p='<NULL>', len=2, value=0, cbMax=2
SQLGetInfo: entering...fInfoType=35
SQLGetInfo: p='<NULL>', len=2, value=32, cbMax=2
SQLColAttributes: entering...
colAttr: col 0 field_type = 23
SQLColAttributes: entering...
colAttr: col 0 field_type = 23
SQLColAttributes: entering...
colAttr: col 0 field_type = 23
SQLColAttr: TABLE_NAME = 'dmsbookings'
SQLColAttributes: entering...
colAttr: col 0 field_type = 23
SQLColAttr: COLUMN_NAME = 'child beds'
SQLColAttributes: entering...
colAttr: col 0 field_type = 23
SQLFreeStmt: entering...hstmt=41295024, fOption=3
SC_free_params: ENTER, self=41295024
SQLFreeStmt: entering...hstmt=41295024, fOption=0
recycle statement: self= 41295024
SQLPrepare: entering...
**** SQLPrepare: STMT_ALLOCATED, copy
preparing stmt: UPDATE "dmsbookings" SET child beds=? WHERE (child beds=? )
SQLBindParameter: entering...
SQLBindParamater: ipar=0, paramType=1, fCType=-16, fSqlType=4, cbColDef=10,
ibScale=0, rgbValue=1337704, *pcbValue = 4, data_at_exec = 0
SQLBindParameter: entering...
SQLBindParamater: ipar=1, paramType=1, fCType=-16, fSqlType=4, cbColDef=10,
ibScale=0, rgbValue=1337708, *pcbValue = 4, data_at_exec = 0
SQLExecute: entering...
SQLExecute: clear errors...
SQLExecute: copying statement params: trans_status=1, len=59, stmt='UPDATE
"dmsbookings" SET child beds=? WHERE (child beds=? )'
copy_statement_with_params: from(fcType)=-16, to(fSqlType)=4
copy_statement_with_params: from(fcType)=-16, to(fSqlType)=4 stmt_with_params = 'UPDATE "dmsbookings" SET child
beds=33WHERE (child
beds=0 )' it's NOT a select statement: stmt=41295024
send_query(): conn=41250488, query='UPDATE "dmsbookings" SET child beds=33
WHERE (child beds=0 )'
conn=41250488, query='UPDATE "dmsbookings" SET child beds=33 WHERE (child
beds=0 )'
send_query: done sending query
send_query: got id = 'Z'
read 49, global_socket_buffersize=4096
send_query: got id = 'E'
send_query: 'E' - ERROR: parser: parse error at or near "beds"
ERROR from backend during send_query: 'ERROR: parser: parse error at or
near "beds"'
I basically don't know anything about ODBC, but it looks to me like
ADO/OLEDB is either getting misled about whether it needs to quote the
column name containing spaces, or it is just not doing it right. I'm
assuming that the previous activity is ADO/OLEDB trying to get the correct
column/table names to build the SQL. I did wonder if changing SQLColAttr to
return a quoted column name would do the trick, but I'm relucant in my
ignorance to do something that might break something else! It's presumably
far too late to try to fix this at the SQLPrepare stage, as the SQL
statement is effectively already unparseable.
Tim
"NTB Technical Support" <techsupport@ntb.org.uk> writes:
> I basically don't know anything about ODBC, but it looks to me like
> ADO/OLEDB is either getting misled about whether it needs to quote the
> column name containing spaces, or it is just not doing it right.
Curious that it quotes the table name (which doesn't need it) and then
doesn't quote the column name (which does). Since SQLColAttributes
isn't returning a pre-quoted table name, clearly ADO has heard of the
idea that it ought to quote names. It's just omitting to do it for
the column name.
I'd write it off as broken code in ADO, and program around it by not
using column names that require quoting. Ugly answer, but it's not
clear that you have an alternative (short of waiting for an ADO fix).
I don't like the idea of changing what SQLColAttributes returns ...
that seems certain to break other applications.
But first, a wild stab in the dark --- what happens if you writeRS("""child beds""") = 33
ie provide the quotes at the application level?
regards, tom lane
> I'd write it off as broken code in ADO, and program around it by not
> using column names that require quoting. Ugly answer, but it's not
> clear that you have an alternative (short of waiting for an ADO fix).
I just don't fancy modifying all those hundreds of pages of code... And it
works with SQL Server,
so that would have to be broken in a compatible fashion!
> I don't like the idea of changing what SQLColAttributes returns ...
> that seems certain to break other applications.
That was, my guess too.
> But first, a wild stab in the dark --- what happens if you write
> RS("""child beds""") = 33
> ie provide the quotes at the application level?
Ah. Well. The recordset is a "collection" (like an associative array) whose
contents is created by ADO from the field names of the most-recently
executed query - so """child beds""" would be a non-existent member of the
collection...
Tim
"NTB Technical Support" <techsupport@ntb.org.uk> writes:
>> I'd write it off as broken code in ADO, and program around it by not
>> using column names that require quoting. Ugly answer, but it's not
>> clear that you have an alternative (short of waiting for an ADO fix).
> I just don't fancy modifying all those hundreds of pages of
> code... And it works with SQL Server, so that would have to be broken
> in a compatible fashion!
It does eh? Hmm, that suggests that there is *some* way to make the
right thing happen. Anyone know how Microsoft's version of
SQLColAttributes acts with such names?
regards, tom lane
Tom Lane wrote: > > "NTB Technical Support" <techsupport@ntb.org.uk> writes: > >> I'd write it off as broken code in ADO, and program around it by not > >> using column names that require quoting. Ugly answer, but it's not > >> clear that you have an alternative (short of waiting for an ADO fix). > > > I just don't fancy modifying all those hundreds of pages of > > code... And it works with SQL Server, so that would have to be broken > > in a compatible fashion! > > It does eh? Hmm, that suggests that there is *some* way to make the > right thing happen. Anyone know how Microsoft's version of > SQLColAttributes acts with such names? > ADO doesn't need ODBC essentially and M$ SQL Server has its own OLE DB provider. PostgreSQL doesn't have its OLE DB provider implementation yet and has to use M$ OLD DB provider for ODBC. I don't know why the table name is quoted but the column names aren't. There are many applications which quote column/table names properly(unconditionally?). So it seems very hard to change SQLxxxx. Another way is to implement driver cursors. Actually I plan to implement static/keyset-driven cursors. However I'm not sure I can do it and couldn't guarantee when it is ready. regards, Hiroshi Inoue
> > >> I'd write it off as broken code in ADO, and program around it by not > > >> using column names that require quoting. Ugly answer, but it's not > > >> clear that you have an alternative (short of waiting for an ADO fix). Thanks for the various inputs - looks like this is what we'll have to do. > ADO doesn't need ODBC essentially and M$ SQL Server has > its own OLE DB provider. PostgreSQL doesn't have its OLE > DB provider implementation yet Just out of curiosity, I ran the ATL OLE DB Provider wizard in VC++ to see what might be involved in doing this; soon thought better of it.... Tim