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.hetze@linux-ag.de> -----

Date: Fri, 20 Sep 2002 17:37:03 +0200
From: Sebastian Hetze <s.hetze@linux-ag.de>
To: michael.meskes@credativ.de
Cc: dpage@vale-housing.co.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.Hetze@Linux-AG.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.Meskes@credativ.de
Fri, 20 Sep 2002 21:26:01 +0200
Michael Meskes <meskes@postgresql.org>
[s.hetze@linux-ag.de: PostgreSQL integration Visual Basic,
	SQLProcedureColumns]
Michael Meskes wrote:
> 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.

It is in 7.3.

If the return tuple definition is fixed:
instead of:
     exec sp_myproc()
     go
do
     select * from sp_myproc();

If the return tuple definition is *not* fixed:
do
     select * from sp_myproc() as table_alias([col definition list]);

Does this help any? Can he try the 7.3 beta?

Joe





Fri, 27 Sep 2002 09:53:02 -0700
Joe Conway <mail@joeconway.com>
Re: [ODBC] [s.hetze@linux-ag.de: PostgreSQL integration Visual Basic, 
On Fri, Sep 27, 2002 at 09:53:02AM -0700, Joe Conway wrote:
> It is in 7.3.
> 
> If the return tuple definition is fixed:
> instead of:
>     exec sp_myproc()
>     go
> do
>     select * from sp_myproc();

That's a great feature to have. 

> If the return tuple definition is *not* fixed:
> do
>     select * from sp_myproc() as table_alias([col definition list]);
> 
> Does this help any? Can he try the 7.3 beta?

Unfortunately no. They are not willing to use a beta so they are appearantly switching to SAP DB. Sorry.

Michael
-- 
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

Sun, 29 Sep 2002 19:20:32 +0200
Michael Meskes <meskes@postgresql.org>
Re: [ODBC] [s.hetze@linux-ag.de: PostgreSQL integration Visual Basic,
	SQLProcedureColumns]