Thread: [s.hetze@linux-ag.de: PostgreSQL integration Visual Basic, SQLProcedureColumns]
[s.hetze@linux-ag.de: PostgreSQL integration Visual Basic, SQLProcedureColumns]
From
Michael Meskes
Date:
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] asarg2, 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
Attachment
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
Re: [s.hetze@linux-ag.de: PostgreSQL integration Visual Basic, SQLProcedureColumns]
From
Michael Meskes
Date:
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!