RE: Error handling in ODBC

> -----Original Message-----
> From: Dave Page
> Sent: 19 June 2001 09:49
> To: 'Hiroshi Inoue'; Kovacs Zoltan; Tibor Laszlo; Dave Page;
> Kristis Makris; Mister ics
> Cc: pgsql-odbc(at)postgresql(dot)org
> Subject: RE: [ODBC] Error handling in ODBC
>
>
>
>
> > -----Original Message-----
> > From: Hiroshi Inoue [mailto:Inoue(at)tpf(dot)co(dot)jp]
> > Sent: 19 June 2001 03:22
> > To: Kovacs Zoltan; Tibor Laszlo; Dave Page; Kristis Makris;
> Mister ics
> > Cc: pgsql-odbc(at)postgresql(dot)org
> > Subject: Re: [ODBC] Error handling in ODBC
> >
> >
> > Hi all,
> >
> > I've just committed a fix to cvs.
> >
> > Changed the error handling as follows.
> > 1) ERRORs causes an SQL_ERROR and the SQLSTATE='S1000'.
> > 2) NOTICEs causes an SQL_SUCCESS_WITH_INFO and the succeeding
> > SQLError() returns the NOTICE message.
> >
> > Remarks.
> > 1) RI violation ERRORs are reported properly.
> > 2) The NOTICE is the last NOTICE message and
> > is truncated to 4095 bytes.
> >
> > regards,
> > Hiroshi Inoue
>
> I owe you the drink of your choice Hiroshi - that worked a
> treat, many thanks. There's been a few updates recently, so
> if you agree and no-one objects shall we package 07.01.0006?
> If so, if you can update the version number, I'll do the
> build and add the various distros to the ftp site.

Perhaps I spoke too soon - a minor problem:

Executing a more complex query gives the following notice in the commlog:

NOTICE from backend during send_query: 'NOTICE: QUERY PLAN:

Hash Join (cost=844.62..860.22 rows=8 width=148)
-> Seq Scan on pg_type t (cost=0.00..6.30 rows=230 width=36)
-> Hash (cost=844.60..844.60 rows=8 width=112)
-> Nested Loop (cost=0.00..844.60 rows=8 width=112)
-> Seq Scan on pg_class c (cost=0.00..833.30 rows=1
width=58)
SubPlan
-> Seq Scan on pg_rewrite (cost=0.00..3.28 rows=1
width=32)
-> Index Scan using pg_attribute_relid_attnum_index on
pg_attribute a (cost=0.00..11.16 rows=11 width=54)

'

However, the driver is returning the following to my test prog:

NOTICE: QUERY PLAN:

Hash Join (cost=844.62..860.22 rows=8 width=148)
-> Seq Scan on pg_type t (cost=0.00..6.30 rows=230 width=36)
-> Hash (cost=844.60..844.60 rows=8 width=112)
-> Nested Loop (cost=0.00..844.60 rows=8 width=112)
-> Seq Scan on pg_class c (cost=0.00..833.30 rows=1
width=58)
SubPlan
-> Seq Scan on pg_rewrite (cost=0.00..3.28 rows=1
width=32)
-> Index Scan using pg_attribute_relid_attnum_index on p
00000 ????????????? ? ? ??
64 Query Plan

I couldn't see any obvious cause for this in the code, but as I've said
before, I'm not that familiar with it. I've included my test code below BTW
incase I'm doing something stupid.

On a slightly different note, I also noticed in connection.c that there is a
CC_send_function function that I didn't notice before. This has the same
sort of message processing loop in it as CC_send_query, but doesn't look
like it will process notices correctly either.

Regards, Dave.

Test Code (VB)
--------------

Private Sub Command1_Click()
Dim iStatus As Integer
Dim iSize As Integer
Dim iRet As Integer
Dim lEnv As Long
Dim lDBC As Long
Dim lStmt As Long
Dim sConnect As String
Dim sResult As String * 256
Dim lRet As Long
Dim lErrNo As Long
Dim iLen As Integer
Dim sSQLState As String * 1024
Dim sErrorMsg As String * 1024
Dim sMsg As String
Dim sSQL As String

'Initialisze the ODBC subsystem
If SQLAllocEnv(lEnv) <> 0 Then
MsgBox "Unable to initialize ODBC API drivers!"
Exit Sub
End If

If SQLAllocConnect(lEnv, lDBC) <> 0 Then
MsgBox "Could not allocate memory for connection Handle!"
'Free the Environment
iStatus = SQLFreeEnv(lEnv)
If iStatus = SQL_ERROR Then
MsgBox "Error Freeing Environment From ODBC Drivers"
End If
Exit Sub
End If

sConnect = "DSN=Helpdesk-Local;UID=postgres;PWD="
iRet = SQLDriverConnect(lDBC, Me.hWnd, sConnect, Len(sConnect), sResult,
Len(sResult), iSize, 1)
If iRet <> SQL_SUCCESS Then
MsgBox "Could not establish connection to ODBC driver!" & vbCrLf &
"Error: " & iRet
Exit Sub
End If

SQLGetInfoString lDBC, SQL_DBMS_VER, sResult, Len(sResult), vbNull
MsgBox sResult

If SQLAllocStmt(lDBC, lStmt) <> 0 Then
MsgBox "Could not allocate memory for a statement handle!"
Exit Sub
End If

sSQL = "EXPLAIN SELECT " & _
" c.oid AS table_oid, c.relname AS table_name,
pg_get_userbyid(c.relowner) AS table_owner, c.relacl AS table_acl, " & _
" CASE WHEN c.relhasindex = TRUE THEN 'Yes'::text ELSE 'No'::text END AS
table_has_indexes, CASE WHEN c.relhasrules = TRUE THEN 'Yes'::text ELSE
'No'::text END AS table_has_rules, CASE WHEN c.relisshared = TRUE THEN
'Yes'::text ELSE 'No'::text END AS table_is_shared, CASE WHEN c.relhaspkey
= TRUE THEN 'Yes'::text ELSE 'No'::text END AS table_has_primarykey, CASE
WHEN c.reltriggers > 0 THEN 'Yes'::text ELSE 'No'::text END AS
table_has_triggers, " & _
" table_rows(pgadmin_get_rows(c.oid)) AS table_rows,
pgadmin_get_desc(c.oid) AS table_comments, a.oid AS column_oid, a.attname AS
column_name, a.attnum AS column_position, t.typname As column_type, " & _
" CASE WHEN ((a.attlen = -1) AND ((a.atttypmod)::int4 = (-1)::int4)) THEN
(0)::int4 ELSE CASE WHEN a.attlen = -1 THEN " & _
" CASE WHEN ((t.typname = 'bpchar') OR (t.typname = 'char') OR (t.typname
= 'varchar')) THEN (a.atttypmod -4)::int4 ELSE (a.atttypmod)::int4 END " & _
" ELSE (a.attlen)::int4 END END AS column_length, " & _
" CASE WHEN a.attnotnull = TRUE THEN 'Yes'::text ELSE 'No'::text END AS
column_not_null, CASE WHEN a.atthasdef = TRUE THEN 'Yes'::text ELSE
'No'::text END AS column_has_default, " & _
" CASE WHEN (pgadmin_get_col_def(c.oid, a.attnum) NOTNULL) THEN
pgadmin_get_col_def(c.oid, a.attnum) ELSE '' END AS column_default,
pgadmin_get_desc(a.oid) AS column_comments " & _
"FROM pg_attribute a, pg_type t, pg_class c " & _
"WHERE a.atttypid = t.oid AND a.attrelid = c.oid AND (((c.relkind::char =
'r'::char) OR (c.relkind::char = 's'::char)) AND (NOT (EXISTS (SELECT
pg_rewrite.rulename FROM pg_rewrite WHERE ((pg_rewrite.ev_class = c.oid) AND
(pg_rewrite.ev_type::char = '1'::char))))))"

If SQLExecDirect(lStmt, sSQL, Len(sSQL)) = SQL_SUCCESS_WITH_INFO Then
lRet = SQLError(lEnv, lDBC, lStmt, sSQLState, lErrNo, sErrorMsg, 1024,
iLen)
Debug.Print Left$(sErrorMsg, iLen), vbInformation, "Query Plan"
End If

If lDBC <> 0 Then
iStatus = SQLDisconnect(lDBC)
If iStatus = SQL_ERROR Then
MsgBox "Error logging out of data source!"
End If
End If

'Release memory associated with the connections:
iStatus = SQLFreeConnect(lDBC)
If iStatus = SQL_ERROR Then
MsgBox "Error unloading ODBC drivers!"
End If

'Release memory for the ODBC environment
If lEnv <> 0 Then
iStatus = SQLFreeEnv(lEnv)
If iStatus = SQL_ERROR Then
MsgBox "Error Freeing Environment From ODBC Drivers"
End If
End If

End Sub