ADO and ODBC: More - Mailing list pgsql-interfaces

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