[s.hetze@linux-ag.de: PostgreSQL integration Visual Basic, SQLProcedureColumns]

From: Michael Meskes <meskes(at)postgresql(dot)org>
To: PostgreSQL Hacker <pgsql-hackers(at)postgresql(dot)org>, pgsql-odbc(at)postgresql(dot)org
Subject: [s.hetze@linux-ag.de: PostgreSQL integration Visual Basic, SQLProcedureColumns]
Date: 2002-09-20 19:26:01
Message-ID: 20020920192601.GA19082@feivel.fam-meskes.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-odbc

Hi,

here's the latest mail from Sebastian explaining the problem we ran
into. As far as I understand the problem, the application uses stored
procedures for each and every select statement. Thus he needs his
procedures to return the whole query result, which is not doable with
our functions.

I offered to replace his procedures by the corresponding select
statements as I question the design anyway, but due to the amount of
work, this isn't a likely solution. Instead they are considering using
SAP-DB instead of PostgreSQL.

Any ideas how to get PostgreSQL used are more than appreciated. Of
course mails that prove me right are also appreciated, but I'd love to
find a solution. :-)

Thanks.

Michael

P.S.: The attached diff should patch cleanly into the current cvs, but
I'd prefer if Hiroshi or someone else who knows the odbc stuff better
than I do takes a look at it before committing.

----- Forwarded message from Sebastian Hetze <s(dot)hetze(at)linux-ag(dot)de> -----

Date: Fri, 20 Sep 2002 17:37:03 +0200
From: Sebastian Hetze <s(dot)hetze(at)linux-ag(dot)de>
To: michael(dot)meskes(at)credativ(dot)de
Cc: dpage(at)vale-housing(dot)co(dot)uk
Subject: PostgreSQL integration Visual Basic, SQLProcedureColumns

Hi *!

In our current project, we are trying to migrate a database application
written in Visual Basic 6.0 SP5 from Microsoft SQL6.5 to postgresql 7.2.
We have about 150 stored procedures in MS-SQL that return result sets
(multiple rows of multiple columns, just like a select does).
The application makes heavy use of ADO/OLEDB methods to access these
procedures.
It goes without saying that we do not want to rewrite the whole
application, so we want to get this ADO thing working the same with
postgresql.

Here is the summary of our findings so far:

1. There is a OLE DB provider for ODBC drivers that bridges the ADO
interface to the common ODBC interface.
When using ADO methods to integrate stored procedures (SP) into the
Visual Basic (VB) IDE, we try to set up the DataEnvironment to
contain these procedures.

2. When including a new SP into the DataEnvironment, the VB IDE first
calls SQLProcedures to get a list of the available SP from
postgresql.
Once the SP to include has been selected VB calls SQLProcedureColumns
to find out about arguments to call the SP with.

3. psqlodbc returns 'not implemented' for SQLProcedureColumns.
We have implemented it as far as the actual information to be
returned is available. Patch included.
There appears to be no way to get the actual description of the
result set columns. (we simulated that by introducing a new
system table holding all sorts of information about arguments
and result set columns for SQLProcedureColumns)

4. postgresql functions are not really the same thing as stored
procedures. Functions always return one value, that might be a
integer, a single row (array) or a cursor. postgresql functions
are SELECTed, not CALLed.

5. We rewrote the SP from MSSQL to return cursors in PL/pgSQL.

6. Unlike SAPDB, where cursors returned by SP are actually used
to fetch data by the VB IDE and application, we did not get
these postgresql cursors to work.

We have experimented quite a while with all different sorts of
declaration of SQL_RETURN_VALUE, SQL_RESULT_COL and SQL_PARAM_OUTPUT
for the SQLProcedureColumns results. We did not see any effect on
the behaviour of the ADO DataEnvironment. Finally, we got the
impression that VB ignores all result set information from
SQLProcedureColumns and tries to prepare the CALL/SELECT statement
instead. With postgresql this preparation appearently does not lead
to any useful results.

This is where we are stuck now.

Any hints or suggestions what we could do to solve this riddle?

Thanx alot!

Sebastian
--
Sebastian Hetze Linux Information Systems AG
Fon +49 (0)30 72 62 38-0 Ehrenbergstr. 19
S(dot)Hetze(at)Linux-AG(dot)com Fax +49 (0)30 72 62 38-99 D-10245 Berlin
Linux is our Business. ____________________________________ www.Linux-AG.com __

diff -Nur psqlodbc-dist/info.c psqlodbc-neu/info.c
--- psqlodbc-dist/info.c 2002-09-21 16:23:48.000000000 +0200
+++ psqlodbc-neu/info.c 2002-09-21 17:12:46.000000000 +0200
@@ -972,11 +972,16 @@
pfExists[SQL_API_SQLNUMPARAMS] = TRUE;
pfExists[SQL_API_SQLPARAMOPTIONS] = TRUE;
pfExists[SQL_API_SQLPRIMARYKEYS] = TRUE;
- pfExists[SQL_API_SQLPROCEDURECOLUMNS] = FALSE;
if (PG_VERSION_LT(conn, 6.5))
+ {
pfExists[SQL_API_SQLPROCEDURES] = FALSE;
+ pfExists[SQL_API_SQLPROCEDURECOLUMNS] = FALSE;
+ }
else
+ {
pfExists[SQL_API_SQLPROCEDURES] = TRUE;
+ pfExists[SQL_API_SQLPROCEDURECOLUMNS] = TRUE;
+ }
pfExists[SQL_API_SQLSETPOS] = TRUE;
pfExists[SQL_API_SQLSETSCROLLOPTIONS] = TRUE; /* odbc 1.0 */
pfExists[SQL_API_SQLTABLEPRIVILEGES] = TRUE;
@@ -1148,7 +1153,7 @@
*pfExists = TRUE;
break;
case SQL_API_SQLPROCEDURECOLUMNS:
- *pfExists = FALSE;
+ *pfExists = TRUE;
break;
case SQL_API_SQLPROCEDURES:
if (PG_VERSION_LT(conn, 6.5))
@@ -4146,27 +4151,667 @@
}


-RETCODE SQL_API
-PGAPI_ProcedureColumns(
- HSTMT hstmt,
- UCHAR FAR * szProcQualifier,
- SWORD cbProcQualifier,
- UCHAR FAR * szProcOwner,
- SWORD cbProcOwner,
- UCHAR FAR * szProcName,
- SWORD cbProcName,
- UCHAR FAR * szColumnName,
- SWORD cbColumnName)
+RETCODE SQL_API
+PGAPI_ProcedureColumns(HSTMT hstmt,
+ UCHAR FAR * szSchemaName,
+ SWORD cbSchemaName,
+ UCHAR FAR * szProcOwner,
+ SWORD cbProcOwner,
+ UCHAR FAR * szProcName,
+ SWORD cbProcName,
+ UCHAR FAR * szColumnName, SWORD cbColumnName)
{
- static char *func = "PGAPI_ProcedureColumns";
- StatementClass *stmt = (StatementClass *) hstmt;
+ static char *func = "PGAPI_ProcedureColumns";
+ StatementClass *stmt = (StatementClass *) hstmt;
+ ConnectionClass *conn = SC_get_conn(stmt);
+ QResultClass *res;
+ char columns_query[INFO_INQUIRY_LEN];
+ SQLRETURN result;
+ SQLHSTMT hcol_stmt;
+ StatementClass *col_stmt;
+ Int2 result_cols;
+ char proc_owner[MAX_INFO_STRING],
+ proc_name[MAX_INFO_STRING],
+ colname[255];
+ SWORD proc_nargs,
+ proc_retset;
+ Int4 proc_rettype;
+ Int2 i;
+ Oid proc_oid;
+ Int4 dlen;
+
+ /*
+ * This is a copy from pg_config.h
+ */
+#define INDEX_MAX_KEYS 16
+ Oid proc_argtypes[INDEX_MAX_KEYS];

- mylog("%s: entering...\n", func);
+ ConnInfo *ci;
+ TupleNode *row;

+ mylog("%s: entering...\n", func);
+
+ if (PG_VERSION_LT(conn, 6.3))
+ {
stmt->errornumber = STMT_NOT_IMPLEMENTED_ERROR;
- stmt->errormsg = "not implemented";
- SC_log_error(func, "Function not implemented", stmt);
+ stmt->errormsg = "Version is too old";
+ SC_log_error(func, "Function not implemented",
+ (StatementClass *) hstmt);
+ return SQL_ERROR;
+ }
+
+ if (!SC_recycle_statement(stmt))
+ return SQL_ERROR;
+
+ stmt->manual_result = TRUE;
+ stmt->errormsg_created = TRUE;
+
+ conn = (ConnectionClass *) (stmt->hdbc);
+ ci = &stmt->hdbc->connInfo;
+
+ /*
+ * This statement is far from elegant. I simply have no idea how to
+ * get this oidvector thing any other way... FIXME if you can...
+ */
+ /*
+ * columns_query is set up to read everything we know about the
+ * procedures out of pg_proc. There is nothing else we can tell
+ * about the functions / procs with the current implementation of
+ * the postgresql system tables. You might want to introduce a new
+ * system table to hold argument names, possibly return or result
+ * set name + type information and such things. Sometime in the
+ * future....
+ */
+ strcpy(columns_query,
+ "select u.usename, p.proname, p.pronargs, p.proretset, p.prorettype, p.proargtypes[0] as arg1, p.proargtypes[1] as arg2, p.proargtypes[2] as arg3, p.proargtypes[3] as arg4, p.proargtypes[4] as arg5, p.proargtypes[5] as arg6, p.proargtypes[6] as arg7, p.proargtypes[7] as arg8, p.proargtypes[8] as arg9, p.proargtypes[9] as arg10, p.proargtypes[10] as arg11, p.proargtypes[11] as arg12, p.proargtypes[12] as arg13, p.proargtypes[13] as arg14, p.proargtypes[14] as arg15, p.proargtypes[15] as arg16, p.oid FROM pg_proc p, pg_user u WHERE p.prorettype <> 0 and (p.pronargs = 0 or oidvectortypes(p.proargtypes) <> '') and p.proowner = u.usesysid");
+ my_strcat(columns_query, " and u.usename like '%.*s'", szSchemaName,
+ cbSchemaName);
+ my_strcat(columns_query, " and p.proname like '%.*s'", szProcName,
+ cbProcName);
+ strcat(columns_query, " ORDER BY p.proowner, p.proname");
+
+ result = SQLAllocStmt(stmt->hdbc, &hcol_stmt);
+ if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
+ {
+ stmt->errornumber = STMT_NO_MEMORY_ERROR;
+ stmt->errormsg =
+ "Couldn't allocate statement for SQLProcedureColumns result.";
+ SC_log_error(func, "", stmt);
+ return SQL_ERROR;
+ }
+
+ col_stmt = (StatementClass *) hcol_stmt;
+ result =
+ SQLExecDirect(hcol_stmt, columns_query, strlen(columns_query));
+ if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
+ {
+ stmt->errormsg = SC_create_errormsg(hcol_stmt);
+ stmt->errornumber = col_stmt->errornumber;
+ SC_log_error(func, "", stmt);
+ SQLFreeStmt(hcol_stmt, SQL_DROP);
+ return SQL_ERROR;
+ }
+
+ /*
+ * Now that the statement is executed we have to set up buffers to
+ * hold the values for all columns in each fetch loop ...
+ * long statement need a lot of bindings. It would be much easier if
+ * we could bind the oidvector thing as a whole. FIXME: how can this
+ * be done??
+ */
+
+ /*
+ * u.usename
+ */
+ result = SQLBindCol(hcol_stmt, 1, SQL_CHAR,
+ proc_owner, MAX_INFO_STRING, NULL);
+
+ if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
+ {
+ stmt->errormsg = col_stmt->errormsg;
+ stmt->errornumber = col_stmt->errornumber;
+ SC_log_error(func, "", stmt);
+ SQLFreeStmt(hcol_stmt, SQL_DROP);
+ return SQL_ERROR;
+ }
+
+ /*
+ * p.proname
+ */
+ result = SQLBindCol(hcol_stmt, 2, SQL_CHAR,
+ proc_name, MAX_INFO_STRING, NULL);
+
+ if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
+ {
+ stmt->errormsg = col_stmt->errormsg;
+ stmt->errornumber = col_stmt->errornumber;
+ SC_log_error(func, "", stmt);
+ SQLFreeStmt(hcol_stmt, SQL_DROP);
+ return SQL_ERROR;
+ }
+
+ /*
+ * p.pronargs
+ */
+ result = SQLBindCol(hcol_stmt, 3, SQL_SMALLINT,
+ &proc_nargs, sizeof(SWORD), NULL);
+
+ if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
+ {
+ stmt->errormsg = col_stmt->errormsg;
+ stmt->errornumber = col_stmt->errornumber;
+ SC_log_error(func, "", stmt);
+ SQLFreeStmt(hcol_stmt, SQL_DROP);
+ return SQL_ERROR;
+ }
+
+ /*
+ * p.proretset
+ */
+ result = SQLBindCol(hcol_stmt, 4, SQL_BIT,
+ &proc_retset, sizeof(SWORD), NULL);
+
+ if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
+ {
+ stmt->errormsg = col_stmt->errormsg;
+ stmt->errornumber = col_stmt->errornumber;
+ SC_log_error(func, "", stmt);
+ SQLFreeStmt(hcol_stmt, SQL_DROP);
return SQL_ERROR;
+ }
+
+ /*
+ * p.prorettype
+ */
+ result = SQLBindCol(hcol_stmt, 5, SQL_INTEGER,
+ &proc_rettype, sizeof(Int4), NULL);
+
+ if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
+ {
+ stmt->errormsg = col_stmt->errormsg;
+ stmt->errornumber = col_stmt->errornumber;
+ SC_log_error(func, "", stmt);
+ SQLFreeStmt(hcol_stmt, SQL_DROP);
+ return SQL_ERROR;
+ }
+
+ /*
+ * p.proargtypes , column by column, 16 times ...
+ */
+ result =
+ SQLBindCol(hcol_stmt, 6, SQL_INTEGER, &proc_argtypes[0], 4, NULL);
+
+ if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
+ {
+ stmt->errormsg = col_stmt->errormsg;
+ stmt->errornumber = col_stmt->errornumber;
+ SC_log_error(func, "", stmt);
+ SQLFreeStmt(hcol_stmt, SQL_DROP);
+ return SQL_ERROR;
+ }
+
+ result =
+ SQLBindCol(hcol_stmt, 7, SQL_INTEGER, &proc_argtypes[1], 4, NULL);
+
+ if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
+ {
+ stmt->errormsg = col_stmt->errormsg;
+ stmt->errornumber = col_stmt->errornumber;
+ SC_log_error(func, "", stmt);
+ SQLFreeStmt(hcol_stmt, SQL_DROP);
+ return SQL_ERROR;
+ }
+
+ result =
+ SQLBindCol(hcol_stmt, 8, SQL_INTEGER, &proc_argtypes[2], 4, NULL);
+
+ if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
+ {
+ stmt->errormsg = col_stmt->errormsg;
+ stmt->errornumber = col_stmt->errornumber;
+ SC_log_error(func, "", stmt);
+ SQLFreeStmt(hcol_stmt, SQL_DROP);
+ return SQL_ERROR;
+ }
+
+ result =
+ SQLBindCol(hcol_stmt, 9, SQL_INTEGER, &proc_argtypes[3], 4, NULL);
+
+ if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
+ {
+ stmt->errormsg = col_stmt->errormsg;
+ stmt->errornumber = col_stmt->errornumber;
+ SC_log_error(func, "", stmt);
+ SQLFreeStmt(hcol_stmt, SQL_DROP);
+ return SQL_ERROR;
+ }
+
+ result =
+ SQLBindCol(hcol_stmt, 10, SQL_INTEGER, &proc_argtypes[4], 4, NULL);
+
+ if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
+ {
+ stmt->errormsg = col_stmt->errormsg;
+ stmt->errornumber = col_stmt->errornumber;
+ SC_log_error(func, "", stmt);
+ SQLFreeStmt(hcol_stmt, SQL_DROP);
+ return SQL_ERROR;
+ }
+
+ result =
+ SQLBindCol(hcol_stmt, 11, SQL_INTEGER, &proc_argtypes[5], 4, NULL);
+
+ if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
+ {
+ stmt->errormsg = col_stmt->errormsg;
+ stmt->errornumber = col_stmt->errornumber;
+ SC_log_error(func, "", stmt);
+ SQLFreeStmt(hcol_stmt, SQL_DROP);
+ return SQL_ERROR;
+ }
+
+ result =
+ SQLBindCol(hcol_stmt, 12, SQL_INTEGER, &proc_argtypes[6], 4, NULL);
+
+ if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
+ {
+ stmt->errormsg = col_stmt->errormsg;
+ stmt->errornumber = col_stmt->errornumber;
+ SC_log_error(func, "", stmt);
+ SQLFreeStmt(hcol_stmt, SQL_DROP);
+ return SQL_ERROR;
+ }
+
+ result =
+ SQLBindCol(hcol_stmt, 13, SQL_INTEGER, &proc_argtypes[7], 4, NULL);
+
+ if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
+ {
+ stmt->errormsg = col_stmt->errormsg;
+ stmt->errornumber = col_stmt->errornumber;
+ SC_log_error(func, "", stmt);
+ SQLFreeStmt(hcol_stmt, SQL_DROP);
+ return SQL_ERROR;
+ }
+
+ result =
+ SQLBindCol(hcol_stmt, 14, SQL_INTEGER, &proc_argtypes[8], 4, NULL);
+
+ if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
+ {
+ stmt->errormsg = col_stmt->errormsg;
+ stmt->errornumber = col_stmt->errornumber;
+ SC_log_error(func, "", stmt);
+ SQLFreeStmt(hcol_stmt, SQL_DROP);
+ return SQL_ERROR;
+ }
+
+ result =
+ SQLBindCol(hcol_stmt, 15, SQL_INTEGER, &proc_argtypes[9], 4, NULL);
+
+ if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
+ {
+ stmt->errormsg = col_stmt->errormsg;
+ stmt->errornumber = col_stmt->errornumber;
+ SC_log_error(func, "", stmt);
+ SQLFreeStmt(hcol_stmt, SQL_DROP);
+ return SQL_ERROR;
+ }
+
+ result = SQLBindCol(hcol_stmt, 16, SQL_INTEGER,
+ &proc_argtypes[10], 4, NULL);
+
+ if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
+ {
+ stmt->errormsg = col_stmt->errormsg;
+ stmt->errornumber = col_stmt->errornumber;
+ SC_log_error(func, "", stmt);
+ SQLFreeStmt(hcol_stmt, SQL_DROP);
+ return SQL_ERROR;
+ }
+
+ result = SQLBindCol(hcol_stmt, 17, SQL_INTEGER,
+ &proc_argtypes[11], 4, NULL);
+
+ if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
+ {
+ stmt->errormsg = col_stmt->errormsg;
+ stmt->errornumber = col_stmt->errornumber;
+ SC_log_error(func, "", stmt);
+ SQLFreeStmt(hcol_stmt, SQL_DROP);
+ return SQL_ERROR;
+ }
+
+ result = SQLBindCol(hcol_stmt, 18, SQL_INTEGER,
+ &proc_argtypes[12], 4, NULL);
+
+ if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
+ {
+ stmt->errormsg = col_stmt->errormsg;
+ stmt->errornumber = col_stmt->errornumber;
+ SC_log_error(func, "", stmt);
+ SQLFreeStmt(hcol_stmt, SQL_DROP);
+ return SQL_ERROR;
+ }
+
+ result = SQLBindCol(hcol_stmt, 19, SQL_INTEGER,
+ &proc_argtypes[13], 4, NULL);
+
+ if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
+ {
+ stmt->errormsg = col_stmt->errormsg;
+ stmt->errornumber = col_stmt->errornumber;
+ SC_log_error(func, "", stmt);
+ SQLFreeStmt(hcol_stmt, SQL_DROP);
+ return SQL_ERROR;
+ }
+
+ result = SQLBindCol(hcol_stmt, 20, SQL_INTEGER,
+ &proc_argtypes[14], 4, NULL);
+
+ if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
+ {
+ stmt->errormsg = col_stmt->errormsg;
+ stmt->errornumber = col_stmt->errornumber;
+ SC_log_error(func, "", stmt);
+ SQLFreeStmt(hcol_stmt, SQL_DROP);
+ return SQL_ERROR;
+ }
+
+ result = SQLBindCol(hcol_stmt, 21, SQL_INTEGER,
+ &proc_argtypes[15], 4, NULL);
+
+ if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
+ {
+ stmt->errormsg = col_stmt->errormsg;
+ stmt->errornumber = col_stmt->errornumber;
+ SC_log_error(func, "", stmt);
+ SQLFreeStmt(hcol_stmt, SQL_DROP);
+ return SQL_ERROR;
+ }
+
+ result = SQLBindCol(hcol_stmt, 22, SQL_INTEGER, &proc_oid, 4, NULL);
+
+ if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
+ {
+ stmt->errormsg = col_stmt->errormsg;
+ stmt->errornumber = col_stmt->errornumber;
+ SC_log_error(func, "", stmt);
+ SQLFreeStmt(hcol_stmt, SQL_DROP);
+ return SQL_ERROR;
+ }
+
+ /*
+ * Now we can set up the manual result set and fill in everything we
+ * can tell...
+ */
+
+ if (res = QR_Constructor(), !res)
+ {
+ stmt->errormsg =
+ "Couldn't allocate memory for SQLProcedureColumns result.";
+ stmt->errornumber = STMT_NO_MEMORY_ERROR;
+ SC_log_error(func, "", stmt);
+ SQLFreeStmt(hcol_stmt, SQL_DROP);
+ return SQL_ERROR;
+ }
+
+ SC_set_Result(stmt, res);
+
+ /*
+ * There are 6 additional columns returned by SQLProcedureColumns
+ * with ODBC Version 3.0.
+ */
+#if (ODBCVER >= 0x0300)
+ result_cols = 19;
+#else
+ result_cols = 13;
+#endif
+ extend_column_bindings(SC_get_ARD(stmt), result_cols);
+
+ QR_set_num_fields(res, result_cols);
+ QR_set_field_info(res, 0, "PROCEDURE_CAT", PG_TYPE_TEXT,
+ MAX_INFO_STRING);
+ QR_set_field_info(res, 1, "PROCEDURE_OWNER", PG_TYPE_TEXT,
+ MAX_INFO_STRING);
+ QR_set_field_info(res, 2, "PROCEDURE_NAME", PG_TYPE_TEXT,
+ MAX_INFO_STRING);
+ QR_set_field_info(res, 3, "COLUMN_NAME", PG_TYPE_TEXT,
+ MAX_INFO_STRING);
+ QR_set_field_info(res, 4, "COLUMN_TYPE", PG_TYPE_INT2, 2);
+ QR_set_field_info(res, 5, "DATA_TYPE", PG_TYPE_INT2, 2);
+ QR_set_field_info(res, 6, "TYPE_NAME", PG_TYPE_TEXT, MAX_INFO_STRING);
+ QR_set_field_info(res, 7, "COLUMN_SIZE", PG_TYPE_INT4, 4);
+ QR_set_field_info(res, 8, "BUFFER_LENGTH", PG_TYPE_INT4, 4);
+ QR_set_field_info(res, 9, "DECIMAL_DIGITS", PG_TYPE_INT2, 2);
+ QR_set_field_info(res, 10, "NUM_PREC_RADIX", PG_TYPE_INT2, 2);
+ QR_set_field_info(res, 11, "NULLABLE", PG_TYPE_INT2, 2);
+ QR_set_field_info(res, 12, "REMARKS", PG_TYPE_TEXT, 254);
+#if (ODBCVER >= 0x0300)
+ QR_set_field_info(res, 13, "COLUMN_DEF", PG_TYPE_INT4, 254);
+ QR_set_field_info(res, 14, "SQL_DATA_TYPE", PG_TYPE_INT2, 2);
+ QR_set_field_info(res, 15, "SQL_DATETIME_SUB", PG_TYPE_INT2, 2);
+ QR_set_field_info(res, 16, "CHAR_OCTET_LENGTH", PG_TYPE_INT2, 2);
+ QR_set_field_info(res, 17, "ORDINAL_POSITION", PG_TYPE_INT4, 4);
+ QR_set_field_info(res, 18, "IS_NULLABLE", PG_TYPE_TEXT, 254);
+#endif
+
+ /*
+ * now we start filling each row for the result set of
+ * SQLProcedureColumns. The documentation says, we have to build one
+ * row for each return value, argument and result set column - in
+ * that order
+ */
+ result = PGAPI_Fetch(hcol_stmt);
+
+ if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO))
+ {
+ stmt->errormsg = col_stmt->errormsg;
+ stmt->errornumber = col_stmt->errornumber;
+ SC_log_error(func, "", stmt);
+ SQLFreeStmt(hcol_stmt, SQL_DROP);
+ return SQL_ERROR;
+ }
+
+
+#define UNKNOWNS_AS_LONGEST 2
+
+ while ((result == SQL_SUCCESS) || (result == SQL_SUCCESS_WITH_INFO))
+ {
+ mylog("%s: While loop pn: %d...\n", func, proc_nargs);
+ for (i = 0; i <= proc_nargs; i++)
+ {
+ mylog("%s: For loop %d...\n", func, i);
+ row =
+ (TupleNode *) malloc(sizeof(TupleNode) +
+ (result_cols -
+ 1) * sizeof(TupleField));
+
+ set_tuplefield_null(&row->tuple[0]); /* ProcedureCat */
+ set_nullfield_string(&row->tuple[1], proc_owner); /* ProcedureOwner */
+ set_tuplefield_string(&row->tuple[2], proc_name); /* ProcedureName */
+
+ /*
+ * Index 0 is for the return value. I assume we always have
+ * one. Don't know if this is really the case... FIXME if
+ * you know better
+ */
+ if (i == 0)
+ {
+ set_tuplefield_string(&row->tuple[3], "RETURN_VALUE"); /* Column Name */
+ set_tuplefield_int2(&row->tuple[4], SQL_RETURN_VALUE); /* Column / Argument Type */
+ set_tuplefield_int2(&row->tuple[5], /* SQL data type for that column */
+ pgtype_to_sqldesctype(stmt,
+ proc_rettype));
+ set_tuplefield_string(&row->tuple[6], /* name for that data type, driver specific */
+ pgtype_to_name(stmt, proc_rettype));
+ dlen =
+ pgtype_desclength(stmt, proc_rettype, 0,
+ UNKNOWNS_AS_LONGEST);
+ set_tuplefield_int4(&row->tuple[7], dlen); /* lenght of that data type */
+ set_tuplefield_int4(&row->tuple[8], dlen); /* buffer size for that argument */
+ }
+ /*
+ * now we have to construct one row for each argument
+ * required to call our function
+ */
+ else if (i <= proc_nargs)
+ {
+ sprintf(colname, "argument%d", i);
+ set_tuplefield_string(&row->tuple[3], colname);
+ set_tuplefield_int2(&row->tuple[4], SQL_PARAM_INPUT);
+ set_tuplefield_int2(&row->tuple[5],
+ pgtype_to_sqldesctype(stmt,
+ proc_argtypes[i -
+ 1]));
+ set_tuplefield_string(&row->tuple[6],
+ pgtype_to_name(stmt,
+ proc_argtypes[i -
+ 1]));
+ dlen =
+ pgtype_desclength(stmt, proc_argtypes[i - 1], 0,
+ UNKNOWNS_AS_LONGEST);
+ set_tuplefield_int4(&row->tuple[7], dlen);
+ set_tuplefield_int4(&row->tuple[8], dlen);
+ }
+ else
+ {
+ /*
+ * This actually does not happen. Anyway, here we
+ * could start to construct rows to descripe each
+ * columnd of the result set. Until now, we do not
+ * have any information about what our function /
+ * procedure might return.
+ */
+ set_tuplefield_string(&row->tuple[3], "unknown");
+ set_tuplefield_int2(&row->tuple[4],
+ SQL_PARAM_TYPE_UNKNOWN);
+ set_tuplefield_int2(&row->tuple[5], 0);
+ set_tuplefield_string(&row->tuple[6], "");
+ set_tuplefield_int4(&row->tuple[7], 0);
+ set_tuplefield_null(&row->tuple[8]);
+ }
+
+ /*
+ * we do not know much about the argument types, do we?
+ * These are just reasonable defaults. FIXME if you know
+ * better
+ */
+ set_tuplefield_null(&row->tuple[9]); /* DEC DIGITS */
+ set_tuplefield_null(&row->tuple[10]); /* PREC RADIX */
+ set_tuplefield_int2(&row->tuple[11], SQL_NULLABLE_UNKNOWN);
+
+
+ set_tuplefield_string(&row->tuple[12],
+ "prodedure column remark");
+#if (ODBCVER >= 0x0300)
+ /*
+ * Lots of reasonable defaults follow.
+ */
+ set_tuplefield_null(&row->tuple[13]);
+
+ if (i == 0)
+ {
+ if ((proc_rettype == PG_TYPE_DATE)
+ || (proc_rettype == PG_TYPE_TIME)
+ || (proc_rettype == PG_TYPE_TIME_WITH_TMZONE)
+ || (proc_rettype == PG_TYPE_DATETIME)
+ || (proc_rettype == PG_TYPE_ABSTIME)
+ || (proc_rettype == PG_TYPE_TIMESTAMP_NO_TMZONE)
+ || (proc_rettype == PG_TYPE_TIMESTAMP))
+ {
+ set_tuplefield_int2(&row->tuple[14], SQL_DATETIME);
+ set_tuplefield_int2(&row->tuple[15],
+ pgtype_to_datetime_sub(stmt,
+ proc_rettype));
+ }
+ else
+ {
+ set_tuplefield_int2(&row->tuple[14],
+ pgtype_to_sqldesctype(stmt,
+ proc_rettype));
+ set_tuplefield_null(&row->tuple[15]);
+ }
+ }
+ else if (i <= proc_nargs)
+ {
+ if ((proc_argtypes[i - 1] == PG_TYPE_DATE)
+ || (proc_argtypes[i - 1] == PG_TYPE_TIME)
+ || (proc_argtypes[i - 1] == PG_TYPE_TIME_WITH_TMZONE)
+ || (proc_argtypes[i - 1] == PG_TYPE_DATETIME)
+ || (proc_argtypes[i - 1] == PG_TYPE_ABSTIME)
+ || (proc_argtypes[i - 1] ==
+ PG_TYPE_TIMESTAMP_NO_TMZONE)
+ || (proc_argtypes[i - 1] == PG_TYPE_TIMESTAMP))
+ {
+ set_tuplefield_int2(&row->tuple[14], SQL_DATETIME);
+ set_tuplefield_int2(&row->tuple[15],
+ pgtype_to_datetime_sub(stmt,
+ proc_argtypes
+ [i - 1]));
+ }
+ else
+ {
+ set_tuplefield_int2(&row->tuple[14],
+ pgtype_to_sqldesctype(stmt,
+ proc_argtypes
+ [i - 1]));
+ set_tuplefield_null(&row->tuple[15]);
+ }
+ }
+ else
+ {
+ set_tuplefield_int2(&row->tuple[14], 0);
+ set_tuplefield_null(&row->tuple[15]);
+ }
+
+ set_tuplefield_null(&row->tuple[16]); /* CHAR_OCTET_LENGTH */
+
+ /*
+ * This one is actually meaningful
+ */
+ set_tuplefield_int4(&row->tuple[17], i); /* ORDINAL_POSITION */
+
+ set_tuplefield_string(&row->tuple[18], ""); /* IS_NULLABLE */
+#endif
+
+ /*
+ * finally we add the manually constructed row to the result
+ * set to be returned as the SQLProcedureColumns
+ */
+ QR_add_tuple(stmt->result, row);
+
+
+ }
+ result = PGAPI_Fetch(hcol_stmt);
+
+ }
+ if (result != SQL_NO_DATA_FOUND)
+ {
+ stmt->errormsg = SC_create_errormsg(hcol_stmt);
+ stmt->errornumber = col_stmt->errornumber;
+ SC_log_error(func, "", stmt);
+ SQLFreeStmt(hcol_stmt, SQL_DROP);
+ return SQL_ERROR;
+ }
+
+ /*
+ * also, things need to think that this statement is finished so
+ * the results can be retrieved.
+ */
+ stmt->status = STMT_FINISHED;
+
+ /*
+ * set up the current tuple pointer for SQLFetch
+ */
+ stmt->currTuple = -1;
+ stmt->rowset_start = -1;
+ stmt->current_col = -1;
+
+ SQLFreeStmt(hcol_stmt, SQL_DROP);
+ mylog("SQLProcedureColumns(): EXIT, stmt=%u\n", stmt);
+
+ return SQL_SUCCESS;
}


@@ -4206,7 +4851,7 @@
" proname as " "PROCEDURE_NAME" ", '' as " "NUM_INPUT_PARAMS" ","
" '' as " "NUM_OUTPUT_PARAMS" ", '' as " "NUM_RESULT_SETS" ","
" '' as " "REMARKS" ","
- " case when prorettype = 0 then 1::int2 else 2::int2 end as " "PROCEDURE_TYPE" " from pg_namespace, pg_proc where");
+ " case when prorettype = 0 then 1::int2 else 2::int2 end as " "PROCEDURE_TYPE" " from pg_namespace, pg_proc");
else
strcpy(proc_query, "select '' as " "PROCEDURE_CAT" ", '' as " "PROCEDURE_SCHEM" ","
" proname as " "PROCEDURE_NAME" ", '' as " "NUM_INPUT_PARAMS" ","
diff -Nur psqlodbc-dist/odbcapi30.c psqlodbc-neu/odbcapi30.c
--- psqlodbc-dist/odbcapi30.c 2002-09-21 16:23:48.000000000 +0200
+++ psqlodbc-neu/odbcapi30.c 2002-09-21 17:20:12.000000000 +0200
@@ -491,8 +491,7 @@
SQL_FUNC_ESET(pfExists, SQL_API_SQLNUMPARAMS); /* 63 */
/* SQL_FUNC_ESET(pfExists, SQL_API_SQLPARAMOPTIONS); 64 deprecated */
SQL_FUNC_ESET(pfExists, SQL_API_SQLPRIMARYKEYS); /* 65 */
- if (ci->drivers.lie)
- SQL_FUNC_ESET(pfExists, SQL_API_SQLPROCEDURECOLUMNS); /* 66 not implemeted yet */
+ SQL_FUNC_ESET(pfExists, SQL_API_SQLPROCEDURECOLUMNS); /* 66 */
SQL_FUNC_ESET(pfExists, SQL_API_SQLPROCEDURES); /* 67 */
SQL_FUNC_ESET(pfExists, SQL_API_SQLSETPOS); /* 68 */
/* SQL_FUNC_ESET(pfExists, SQL_API_SQLSETSCROLLOPTIONS); 69 deprecated */

----- End forwarded message -----

--
Dr. Michael Meskes, Geschäftsführer, credativ GmbH
Karl-Heinz-Beckurts-Str. 13, 52428 Jülich, Germany
Tel.: +49 (2461) 69071-0
Fax: +49 (2461) 69071-1
Mobil: +49 (170) 1857143
Email: Michael(dot)Meskes(at)credativ(dot)de

Attachment Content-Type Size
psqlodbc.diff text/plain 23.4 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2002-09-20 19:50:37 Re: Optimizer generates bad plans.
Previous Message Peter Eisentraut 2002-09-20 19:24:00 Re: [PATCHES] to_char(FM9.9) bug fix

Browse pgsql-odbc by date

  From Date Subject
Next Message Hiroshi Inoue 2002-09-21 16:44:33 Re: ODBC Driver doesn't return fractional timestamp
Previous Message Ben Trewern 2002-09-20 13:39:55 Re: datetime and ODBC driver