public inbox for [email protected]
help / color / mirror / Atom feed=?GBK?Q?[PATCH]_Allow_catalogname_(database)_and_procname_=A3=A8no?= =?GBK?Q?_under_public_schema)__enumeration_with_SQLProcedures_?=
3+ messages / 2 participants
[nested] [flat]
* =?GBK?Q?[PATCH]_Allow_catalogname_(database)_and_procname_=A3=A8no?= =?GBK?Q?_under_public_schema)__enumeration_with_SQLProcedures_?=
@ 2023-10-10 05:09 =?GBK?B?wfXXrw==?= <[email protected]>
2023-10-10 13:04 ` Re: [PATCH] Allow catalogname (database) and procname （no under public schema) enumeration with SQLProcedures Dave Cramer <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: =?GBK?B?wfXXrw==?= @ 2023-10-10 05:09 UTC (permalink / raw)
To: pgsql-odbc
hello！
The attached patch fixed SQLProcedures two bug:
1. make enumeration of databases (catalogs in ODBC speak) work.
2. make enumeration of procedurename (procname in ODBC speak) work.
Step:
1. login postgres
2. create schem named schem1
3. set search_path TO schem1
4. create procedure named genre_insert_data
5. with odbc application（retcode = SQLProcedures (hstmt, NULL, 0, NULL, 0, (SQLCHAR *)"genre_insert_data", strlen("genre_insert_data"));）to get the genre_insert_data(step 4)
6. the result is empty.
7. After the patch is compiled using the attached patch, then run the odbc application（step 5）, The result is shown below:
Attachments:
[image/png] image.png (3.3K, 3-image.png)
download | view image
[image/png] image.png (6.7K, 4-image.png)
download | view image
[application/octet-stream] AllCatalogsProcname-SQLProcedures.patch (696B, 5-AllCatalogsProcname-SQLProcedures.patch)
download | inline diff:
diff info.c info--bak.c
1936c1936
< {
---
> {
5485c5485
< appendPQExpBufferStr(&proc_query, "select" "(select current_catalog)" " as " "PROCEDURE_CAT" ", nspname as " "PROCEDURE_SCHEM" ","
---
> appendPQExpBufferStr(&proc_query, "select ''::varchar as " "PROCEDURE_CAT" ", nspname as " "PROCEDURE_SCHEM" ","
5493,5495c5493
<
< if (escSchemaName)
< schema_appendPQExpBuffer1(&proc_query, " and nspname %s'%.*s'", op_string, escSchemaName, TABLE_IS_VALID(szProcName, cbProcName), conn);
---
> schema_appendPQExpBuffer1(&proc_query, " and nspname %s'%.*s'", op_string, escSchemaName, TABLE_IS_VALID(szProcName, cbProcName), conn);
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [PATCH] Allow catalogname (database) and procname （no under public schema) enumeration with SQLProcedures
2023-10-10 05:09 =?GBK?Q?[PATCH]_Allow_catalogname_(database)_and_procname_=A3=A8no?= =?GBK?Q?_under_public_schema)__enumeration_with_SQLProcedures_?= =?GBK?B?wfXXrw==?= <[email protected]>
@ 2023-10-10 13:04 ` Dave Cramer <[email protected]>
2023-10-11 12:16 ` =?GBK?Q?Re:Re:_[PATCH]_Allow_catalogname_(database)_and_procname?= =?GBK?Q?_=A3=A8no_under_public_schema)_enumeration_with_SQLProcedures?= =?GBK?B?wfXXrw==?= <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Dave Cramer @ 2023-10-10 13:04 UTC (permalink / raw)
To: 刘庄 <[email protected]>; +Cc: pgsql-odbc
Hello,
Thanks for the patch, however it does not apply cleanly using patch or git
apply
Can you provide a PR postgresql-interfaces/psqlodbc (github.com)
<https://github.com/postgresql-interfaces/psqlodbc; and add tests please ?
Dave Cramer
www.postgres.rocks
On Tue, 10 Oct 2023 at 01:09, 刘庄 <[email protected]> wrote:
> hello！
>
>
> The attached patch fixed SQLProcedures two bug:
>
> 1. make enumeration of databases (catalogs in ODBC speak) work.
>
> 2. make enumeration of procedurename (procname in ODBC speak) work.
>
>
>
> Step:
>
> 1. login postgres
>
> 2. create schem named schem1
>
> 3. set search_path TO schem1
>
> 4. create procedure named genre_insert_data
>
> 5. with odbc application（retcode = SQLProcedures (hstmt, NULL, 0,
> NULL, 0, (SQLCHAR *)"genre_insert_data", strlen("genre_insert_data"));）to
> get the genre_insert_data(step 4)
>
> 6. the result is empty.
>
> 7. After the patch is compiled using the attached patch, then run the odbc
> application（step 5）, The result is shown below:
>
>
>
>
>
>
Attachments:
[image/png] image.png (3.3K, 3-image.png)
download | view image
[image/png] image.png (6.7K, 4-image.png)
download | view image
[image/png] image.png (3.3K, 5-image.png)
download | view image
^ permalink raw reply [nested|flat] 3+ messages in thread
* =?GBK?Q?Re:Re:_[PATCH]_Allow_catalogname_(database)_and_procname?= =?GBK?Q?_=A3=A8no_under_public_schema)_enumeration_with_SQLProcedures?=
2023-10-10 05:09 =?GBK?Q?[PATCH]_Allow_catalogname_(database)_and_procname_=A3=A8no?= =?GBK?Q?_under_public_schema)__enumeration_with_SQLProcedures_?= =?GBK?B?wfXXrw==?= <[email protected]>
2023-10-10 13:04 ` Re: [PATCH] Allow catalogname (database) and procname （no under public schema) enumeration with SQLProcedures Dave Cramer <[email protected]>
@ 2023-10-11 12:16 ` =?GBK?B?wfXXrw==?= <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: =?GBK?B?wfXXrw==?= @ 2023-10-11 12:16 UTC (permalink / raw)
To: Dave Cramer <[email protected]>; +Cc: pgsql-odbc
hello Dave Cramer:
I'm sorry that my access to github is very unstable.
I will send you the source code change file(info-patch.c) and added test cases first(catalogfunctions-test.c， sampletables.sql).
I will submit a PR postgresql-interfaces/psqlodbc (github.com) when my network is stable.
This patch has been verified in my local environment can make enumeration of databases and procedurename work. The verification results are as follows:
OS vesion: CentOS7 3.10.0-957.el7.x86_64
PG server version: PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
psqlODBC version: psqlodbc-15.00.0000
unixODBC version: unixODBC 2.3.1
The regression (with added test cases) result:
With no path: is empty.
With the attached patch： SQLProcedures work well
Regards,
Liu Zhuang
At 2023-10-10 21:04:31, "Dave Cramer" <[email protected]> wrote:
Hello,
Thanks for the patch, however it does not apply cleanly using patch or git apply
Can you provide a PR postgresql-interfaces/psqlodbc (github.com) and add tests please ?
Dave Cramer
www.postgres.rocks
On Tue, 10 Oct 2023 at 01:09, 刘庄 <[email protected]> wrote:
hello！
The attached patch fixed SQLProcedures two bug:
1. make enumeration of databases (catalogs in ODBC speak) work.
2. make enumeration of procedurename (procname in ODBC speak) work.
Step:
1. login postgres
2. create schem named schem1
3. set search_path TO schem1
4. create procedure named genre_insert_data
5. with odbc application（retcode = SQLProcedures (hstmt, NULL, 0, NULL, 0, (SQLCHAR *)"genre_insert_data", strlen("genre_insert_data"));）to get the genre_insert_data(step 4)
6. the result is empty.
7. After the patch is compiled using the attached patch, then run the odbc application（step 5）, The result is shown below:
Attachments:
[image/png] image.png (15.0K, 3-image.png)
download | view image
[image/png] image.png (18.6K, 4-image.png)
download | view image
[text/x-c] info-patch.c (182.1K, 5-info-patch.c)
download
[text/x-c] catalogfunctions-test.c (10.4K, 6-catalogfunctions-test.c)
download | inline:
/*
* This test is used to check the output and processing of the catalog
* functions listed as below:
* - SQLGetTypeInfo
* - SQLTables
* - SQLColumns
* - SQLSpecialColumns
* - SQLStatistics
* - SQLPrimaryKeys
* - SQLForeignKeys
* - SQLProcedureColumns
* - SQLTablePrivileges
* - SQLColumnPrivileges
* - SQLProcedures
* - SQLGetInfo
*/
#include <string.h>
#include <stdio.h>
#include <stdlib.h>
#include "common.h"
/* define a macro to simplify function calls */
#define PRINT_RESULT_SERIES(hstmt, idarray, rowcount) print_result_series(hstmt, idarray, sizeof(idarray)/sizeof(idarray[0]), rowcount, FALSE)
int
main(int argc, char **argv)
{
int rc;
HSTMT hstmt = SQL_NULL_HSTMT;
/* Cases where output is limited to relevant information only */
SQLSMALLINT sql_tab_privileges_ids[6] = {1, 2, 3, 4, 6, 7};
SQLSMALLINT sql_column_privileges_ids[6] = {1, 2, 3, 4, 7, 8};
SQLSMALLINT sql_column_ids[6] = {1, 2, 3, 4, 5, 6};
SQLSMALLINT sql_pro_column_ids[] = {1, 2, 3, 4, 5, 6, 7};
char buf[1000];
char username[100];
SQLSMALLINT len;
int i;
test_connect();
rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);
if (!SQL_SUCCEEDED(rc))
{
print_diag("failed to allocate stmt handle", SQL_HANDLE_DBC, conn);
exit(1);
}
SQLExecDirect(hstmt, (SQLCHAR *) "drop table if exists testtab2", SQL_NTS);
/* Check for SQLGetTypeInfo */
printf("Check for SQLTypeInfo\n");
rc = SQLGetTypeInfo(hstmt, SQL_VARCHAR);
CHECK_STMT_RESULT(rc, "SQLGetTypeInfo failed", hstmt);
print_result_meta(hstmt);
print_result(hstmt);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/* Check for SQLTables */
printf("Check for SQLTables\n");
rc = SQLTables(hstmt, NULL, 0,
(SQLCHAR *) "public", SQL_NTS,
(SQLCHAR *) "%", SQL_NTS,
(SQLCHAR *) "TABLE", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLTables failed", hstmt);
print_result_meta(hstmt);
print_result(hstmt);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/* Check for SQLColumns */
printf("Check for SQLColumns\n");
rc = SQLColumns(hstmt,
NULL, 0,
(SQLCHAR *) "public", SQL_NTS,
(SQLCHAR *) "%", SQL_NTS,
NULL, 0);
CHECK_STMT_RESULT(rc, "SQLColumns failed", hstmt);
print_result_meta(hstmt);
/*
* Print only the 6 first columns, we do not want for example
* to get the OID in output, and this information looks to be
* enough.
*/
PRINT_RESULT_SERIES(hstmt, sql_column_ids, -1);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/* Check for SQLColumnPrivileges */
printf("Check for SQLColumnPrivileges\n");
rc = SQLColumnPrivileges(hstmt,
NULL, 0,
(SQLCHAR *) "public", SQL_NTS,
(SQLCHAR *) "testtab1", SQL_NTS,
(SQLCHAR *) "id", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLColumnPrivileges failed", hstmt);
print_result_meta(hstmt);
PRINT_RESULT_SERIES(hstmt, sql_column_privileges_ids, -1);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/* Check for SQLSpecialColumns */
printf("Check for SQLSpecialColumns\n");
rc = SQLSpecialColumns(hstmt, SQL_ROWVER,
NULL, 0,
(SQLCHAR *) "public", SQL_NTS,
(SQLCHAR *) "testtab1", SQL_NTS,
SQL_SCOPE_SESSION,
SQL_NO_NULLS);
CHECK_STMT_RESULT(rc, "SQLSpecialColumns failed", hstmt);
print_result_meta(hstmt);
print_result(hstmt);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/*
* Check for SQLStatistics. It is important to note that this function
* returns statistics like the number of pages used and the number of
* index scans.
*/
printf("Check for SQLStatistics\n");
rc = SQLStatistics(hstmt,
NULL, 0,
(SQLCHAR *) "public", SQL_NTS,
(SQLCHAR *) "testtab1", SQL_NTS,
0, 0);
CHECK_STMT_RESULT(rc, "SQLStatistics failed", hstmt);
print_result_meta(hstmt);
print_result(hstmt);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/* Check for SQLPrimaryKeys */
printf("Check for SQLPrimaryKeys\n");
rc = SQLPrimaryKeys(hstmt,
NULL, 0,
(SQLCHAR *) "public", SQL_NTS,
(SQLCHAR *) "testtab1", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLPrimaryKeys failed", hstmt);
print_result_meta(hstmt);
print_result(hstmt);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/* Check for SQLForeignKeys */
printf("Check for SQLForeignKeys\n");
rc = SQLForeignKeys(hstmt,
NULL, 0,
(SQLCHAR *) "public", SQL_NTS,
(SQLCHAR *) "testtab1", SQL_NTS,
NULL, 0,
(SQLCHAR *) "public", SQL_NTS,
(SQLCHAR *) "testtab_fk", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLForeignKeys failed", hstmt);
print_result_meta(hstmt);
print_result(hstmt);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/* Check for SQLProcedures */
printf("Check for SQLProcedures\n");
rc = SQLProcedures(hstmt,
NULL, 0,
NULL, 0,
// (SQLCHAR *) "public", SQL_NTS,
(SQLCHAR *) "proce_nobublic", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLProcedures failed", hstmt);
print_result_meta(hstmt);
print_result(hstmt);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/* Check for SQLProcedureColumns */
printf("Check for SQLProcedureColumns\n");
rc = SQLProcedureColumns(hstmt,
NULL, 0,
(SQLCHAR *) "public", SQL_NTS,
(SQLCHAR *) "simple_add", SQL_NTS,
NULL, 0);
CHECK_STMT_RESULT(rc, "SQLProcedureColumns failed", hstmt);
print_result_meta(hstmt);
print_result(hstmt);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/*
* Check for SQLProcedureColumns for a function
* returning normal type
*/
rc = SQLProcedureColumns(hstmt, NULL, 0,
NULL, 0,
(SQLCHAR *) "set_byte", SQL_NTS,
NULL, 0);
CHECK_STMT_RESULT(rc, "SQLProcedureColumns failed", hstmt);
PRINT_RESULT_SERIES(hstmt, sql_pro_column_ids, -1);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/*
* Check for SQLProcedureColumns for a function
* returning composite type
*/
rc = SQLProcedureColumns(hstmt, NULL, 0,
(SQLCHAR *) "public", SQL_NTS,
(SQLCHAR *) "getfoo", SQL_NTS,
NULL, 0);
CHECK_STMT_RESULT(rc, "SQLProcedureColumns failed", hstmt);
PRINT_RESULT_SERIES(hstmt, sql_pro_column_ids, -1);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/*
* Check for SQLProcedureColumns for a function
* returning setof composite type
*/
rc = SQLProcedureColumns(hstmt, NULL, 0,
(SQLCHAR *) "public", SQL_NTS,
(SQLCHAR *) "getboo", SQL_NTS,
NULL, 0);
CHECK_STMT_RESULT(rc, "SQLProcedureColumns failed", hstmt);
PRINT_RESULT_SERIES(hstmt, sql_pro_column_ids, -1);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/*
* Check for SQLProcedureColumns for a function
* returning table
*/
rc = SQLProcedureColumns(hstmt, NULL, 0,
(SQLCHAR *) "public", SQL_NTS,
(SQLCHAR *) "tbl_arg", SQL_NTS,
NULL, 0);
CHECK_STMT_RESULT(rc, "SQLProcedureColumns failed", hstmt);
PRINT_RESULT_SERIES(hstmt, sql_pro_column_ids, -1);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/*
* Check for SQLProcedureColumns for a function with OUT
* parameters returning setof
* This is equivalent to tbl_arg.
*/
rc = SQLProcedureColumns(hstmt, NULL, 0,
(SQLCHAR *) "public", SQL_NTS,
(SQLCHAR *) "set_of", SQL_NTS,
NULL, 0);
CHECK_STMT_RESULT(rc, "SQLProcedureColumns failed", hstmt);
PRINT_RESULT_SERIES(hstmt, sql_pro_column_ids, -1);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/* Check for SQLTablePrivileges */
printf("Check for SQLTablePrivileges\n");
rc = SQLTablePrivileges(hstmt,
NULL, 0,
(SQLCHAR *) "public", 0,
(SQLCHAR *) "testtab1", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLTablePrivileges failed", hstmt);
print_result_meta(hstmt);
PRINT_RESULT_SERIES(hstmt, sql_tab_privileges_ids, 5);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/* Test SQLGetInfo */
printf("Check for SQLGetInfo\n");
rc = SQLGetInfo(conn, SQL_TABLE_TERM, buf, sizeof(buf), &len);
CHECK_STMT_RESULT(rc, "SQLGetInfo failed", hstmt);
printf("Term for \"table\": %s\n", buf);
/****
* Misc extra tests.
*/
/*
* Older versions of the driver had a bug in handling table-types lists
* longer than 32 entries. Check for that.
*/
rc = SQLTables(hstmt, "", SQL_NTS,
"public", SQL_NTS,
"testtab%", SQL_NTS,
"1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5, TABLES", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLTables failed", hstmt);
print_result(hstmt);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/*
* Older versions of the driver had a buffer overflow bug in handling table
* patterns with lots of escapes, with standard_conforming_strings=off.
* Check for that.
*/
for (i = 0; i < sizeof(buf) - 1; i++)
buf[i] = '\\';
buf[i] = '\0';
rc = SQLTables(hstmt, "", SQL_NTS,
"public", SQL_NTS,
buf, SQL_NTS,
"TABLES", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLTables failed", hstmt);
print_result(hstmt);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/*
* Check that things work with an invalid search_path. current_schema()
* returns NULL in that case, which used to cause a segfault.
*
* Perform these tests in a fresh connection, so that we don't use a
* cached current_schema value.
*/
test_disconnect();
test_connect();
rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);
CHECK_CONN_RESULT(rc, "failed to allocate stmt handle", conn);
rc = SQLExecDirect(hstmt, (SQLCHAR *) "set search_path='bogus_schema'", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
rc = SQLGetInfo(conn, SQL_USER_NAME, username, sizeof(username), &len);
CHECK_STMT_RESULT(rc, "SQLGetInfo failed", hstmt);
rc = SQLTables(hstmt, "", SQL_NTS,
username, SQL_NTS,
"testtab%", SQL_NTS,
"", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLTables failed", hstmt);
print_result(hstmt);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
/* Clean up */
test_disconnect();
return 0;
}
[application/x-sql] sampletables.sql (2.9K, 7-sampletables.sql)
download
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-10-11 12:16 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-10 05:09 =?GBK?Q?[PATCH]_Allow_catalogname_(database)_and_procname_=A3=A8no?= =?GBK?Q?_under_public_schema)__enumeration_with_SQLProcedures_?= =?GBK?B?wfXXrw==?= <[email protected]>
2023-10-10 13:04 ` Re: [PATCH] Allow catalogname (database) and procname （no under public schema) enumeration with SQLProcedures Dave Cramer <[email protected]>
2023-10-11 12:16 ` =?GBK?Q?Re:Re:_[PATCH]_Allow_catalogname_(database)_and_procname?= =?GBK?Q?_=A3=A8no_under_public_schema)_enumeration_with_SQLProcedures?= =?GBK?B?wfXXrw==?= <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox