Skip to Content.
Sympa Menu

freetds - Re: [freetds] Squirrel JDBC-ODBC Bridge and freeTDS ODBC

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Re: [freetds] Squirrel JDBC-ODBC Bridge and freeTDS ODBC

Chronological Thread  
    < Chronological >       < Thread >
  • From: Зверев Виталий Борисович <VZverev AT genesis.spb.ru>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Squirrel JDBC-ODBC Bridge and freeTDS ODBC
  • Date: Thu, 3 Nov 2005 20:38:48 +0300
Thanks for clarification, Frediano.
if we wish to achieve 100% mimic for DatabaseMetaData.getSchemas() and
DatabaseMetaData.getCatalogs() in jdbc-odbc bridging we should'n use
sp_tableswc. I understand this method looks more systematic, but actually
JdbcOdbc bridge call:
1) for getCatalogs()
- original SQLSRV32.DLL::SQLTablesW call "sp_tables N'', N'', N'%', NULL",
IAW "sp_tables @table_name = N'', @table_owner = N'',
@table_qualifier = N'%' , @table_type = null",
but sp_tableswc does not return thees rows.

we could see it in jdbcdebug.log by Squirrel
....
*DatabaseMetaData.getCatalogs
*DatabaseMetaData.getTables (%,,,null)
Allocating Statement Handle (SQLAllocStmt), hDbc=57676616
hStmt=57681616
Tables (SQLTables), hStmt=57681616,catalog=%, schema=, table=,
types=null
Number of result columns (SQLNumResultCols), hStmt=57681616
value=5
*ResultSet.getMetaData
Fetching (SQLFetch), hStmt=57681616
*ResultSetMetaData.getColumnType (1)
Column attributes (SQLColAttributes), hStmt=57681616, icol=1, type=2
value (int)=-9
Column attributes (SQLColAttributes), hStmt=57681616, icol=1, type=3
value (int)=256
Get string data (SQLGetData), hStmt=57681616, column=1, maxLen=257
admin
Fetching (SQLFetch), hStmt=57681616
*ResultSetMetaData.getColumnType (1)
Get string data (SQLGetData), hStmt=57681616, column=1, maxLen=257
Aida32
....
So, getTables (%,,,null) was transformed into sp_tables '', '', '%', NULL

2) for getSchemas()
original SQLSRV32.DLL::SQLTablesW call "sp_tables N'', N'%', N'', NULL".
"sp_tableswc N'', N'%', N'', NULL" does not return rows like sp_tables

*DatabaseMetaData.getSchemas
*DatabaseMetaData.getTables (,%,,null)
Allocating Statement Handle (SQLAllocStmt), hDbc=57676616
hStmt=57681616
Tables (SQLTables), hStmt=57681616,catalog=, schema=%, table=,
types=null
Number of result columns (SQLNumResultCols), hStmt=57681616
value=5
*ResultSet.getMetaData
Fetching (SQLFetch), hStmt=57681616
*ResultSetMetaData.getColumnType (1)
Column attributes (SQLColAttributes), hStmt=57681616, icol=1, type=2
value (int)=-9
Column attributes (SQLColAttributes), hStmt=57681616, icol=2, type=2
value (int)=-9
Column attributes (SQLColAttributes), hStmt=57681616, icol=2, type=3
value (int)=256
Get string data (SQLGetData), hStmt=57681616, column=2, maxLen=257
dbo
Fetching (SQLFetch), hStmt=57681616

sp_tableswc could mimic *structure* of sp_tables resultset, but power of the
set will be different.
In accordance with it we have many identical records on the Squirrel's tab
'Schemas'

Another problem - we should mimic 'Connection is busy with results for
another hstmt'
.....
Tables (SQLTables), hStmt=57681616,catalog=%, schema=, table=, types=null
RETCODE = -1
ERROR - Generating SQLException...
SQLException: SQLState(S1000) vendor code(0)
java.sql.SQLException: [Microsoft][ODBC SQL Server Driver]Connection is busy
with results for another hstmt
.....
And next (may be it is Squirrel's bug) odbc.c:change_database called by
JdbcOdbc bridge with quoted identifiers ("master" instead of master) and as
result - USE ["master"] call failed.

I'm a bit busy now, may be take a look at this later

Sincerely yours.
-------------------------------------------------------
Vitaly Zverev, OCP DBA 8&8i
Oracle E-Business Suite &
Infiniband Linux Cluster Administrator
Genesis Ltd., St.Petersburg, Russia



> -----Original Message-----
> From: freetds-bounces AT lists.ibiblio.org
> [mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of
> ZIGLIO, Frediano, VF-IT
> Sent: Wednesday, November 02, 2005 4:00 PM
> To: FreeTDS Development Group
> Subject: Re: [freetds] Squirrel JDBC-ODBC Bridge and freeTDS ODBC
>
> ...
> >
> > The jdbcdebug.log says:
> > ----------------------------------
> > *Driver.connect (jdbc:odbc:Macola1Free) JDBC to ODBC
> Bridge: Checking
> > security No SecurityManager present, assuming trusted
> > application/applet JDBC to ODBC Bridge 2.0001 Current
> Date/Time: Tue
> > Nov 01 10:36:18 GMT-05:00 2005 Loading JdbcOdbc library Allocating
> > Environment handle (SQLAllocEnv)
> > hEnv=3059112
> > Allocating Connection handle (SQLAllocConnect) ...skipping forward
> > java.sql.SQLException: [unixODBC][FreeTDS][SQL
> Server]Invalid cursor
> > state Get string data (SQLGetData), hStmt=3898184, column=1,
> > maxLen=129
> > at
> > sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958)
> > at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
> > at sun.jdbc.odbc.JdbcOdbc.SQLTables(JdbcOdbc.java:5519)
> > at
> ...
>
> Try with this patch
>
> Index: src/odbc/odbc.c
> ===================================================================
> RCS file: /cvsroot/freetds/freetds/src/odbc/odbc.c,v
> retrieving revision 1.394
> diff -u -1 -0 -r1.394 odbc.c
> --- src/odbc/odbc.c 31 Aug 2005 15:22:10 -0000 1.394
> +++ src/odbc/odbc.c 2 Nov 2005 12:53:13 -0000
> @@ -5455,24 +5455,60 @@
> }
>
>
> SQLRETURN SQL_API
> SQLTables(SQLHSTMT hstmt, SQLCHAR FAR * szCatalogName,
> SQLSMALLINT cbCatalogName, SQLCHAR FAR * szSchemaName,
> SQLSMALLINT cbSchemaName, SQLCHAR FAR *
> szTableName, SQLSMALLINT cbTableName, SQLCHAR FAR * szTableType,
> SQLSMALLINT cbTableType)
> {
> int retcode;
> char *type = NULL;
> -
> + char *proc = NULL;
> + int proc_allocated = 0;
> + int wildcards;
> + TDSSOCKET *tds;
>
> INIT_HSTMT;
>
> + tds = stmt->dbc->tds_socket;
> +
> + /* fix for processing */
> + cbCatalogName = odbc_get_string_size(cbCatalogName,
> szCatalogName);
> +
> + /* support wildcards on catalog (only odbc 3) */
> + wildcards = 0;
> + if (stmt->dbc->env->attr.odbc_version == SQL_OV_ODBC3 &&
> + (memchr(szCatalogName, '%', cbCatalogName) ||
> memchr(szCatalogName, '_', cbCatalogName)))
> + wildcards = 1;
> +
> + proc = "sp_tables ";
> + if (cbCatalogName > 0) {
> + if (wildcards) {
> + /* if catalog specified and wildcards use
> sp_tableswc under mssql2k */
> + if (TDS_IS_MSSQL(tds) &&
> tds->product_version >=
> TDS_MS_VER(8,0,0))
> + proc = "sp_tableswc ";
> +
> + /* TODO support wildcards on catalog even for
> Sybase */
> + } else {
> + /* if catalog specified and not wildcards use
> catatog on name (catalog..sp_tables) */
> + int len = tds_quote_id(tds, NULL,
> szCatalogName,
> cbCatalogName);
> + proc = (char*) malloc(len + 15);
> + if (!proc) {
> + odbc_errs_add(&stmt->errs, "HY001",
> NULL);
> + ODBC_RETURN(stmt, SQL_ERROR);
> + }
> + proc_allocated = 1;
> + tds_quote_id(tds, proc, szCatalogName,
> cbCatalogName);
> + strcpy(proc + len, "..sp_tables ");
> + }
> + }
> +
> /* fix type if needed quoting it */
> if (szTableType) {
> int len = odbc_get_string_size(cbTableType,
> szTableType);
> int to_fix = 0;
> int elements = 0;
> char *p = (char *) szTableType;
> char *const end = p + len;
>
> for (;;) {
> char *begin = p; @@ -5488,20 +5524,22 @@
> ++p;
> }
> /* fix it */
> tdsdump_log(TDS_DBG_INFO1, "len %d to_fix %d
> elements %d\n", len, to_fix, elements);
> if (len && to_fix) {
> char *dst;
>
> tdsdump_log(TDS_DBG_INFO1, "fixing
> type elements\n");
> type = (char *) malloc(len + elements * 2);
> if (!type) {
> + if (proc_allocated)
> + free(proc);
> odbc_errs_add(&stmt->errs,
> "HY001", NULL);
> ODBC_RETURN(stmt, SQL_ERROR);
> }
> p = (char *) szTableType;
> dst = type;
> for (;;) {
> char *begin = p;
>
> p = memchr(p, ',', end - p);
> if (!p)
> @@ -5518,25 +5556,27 @@
> if (p >= end)
> break;
> *dst++ = *p++;
> }
> cbTableType = dst - type;
> szTableType = (SQLCHAR *) type;
> }
> }
>
> retcode =
> - odbc_stat_execute(stmt, "sp_tables ", 4,
> "P@table_name",
> szTableName, cbTableName, "P@table_owner", szSchemaName,
> + odbc_stat_execute(stmt, proc, 4, "P@table_name",
> szTableName, cbTableName, "P@table_owner", szSchemaName,
> cbSchemaName,
> "P@table_qualifier", szCatalogName, cbCatalogName,
> "@table_type", szTableType,
> cbTableType);
> if (type)
> free(type);
> + if (proc_allocated)
> + free(proc);
> if (SQL_SUCCEEDED(retcode) &&
> stmt->dbc->env->attr.odbc_version == SQL_OV_ODBC3) {
> odbc_col_setname(stmt, 1, "TABLE_CAT");
> odbc_col_setname(stmt, 2, "TABLE_SCHEM");
> }
> ODBC_RETURN_(stmt);
> }
>
> /**
> * Log a useful message about unimplemented options
> * Defying belief, Microsoft defines mutually exclusive options that
>
>
> (already in CVS HEAD)
>
> freddy77
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
>


Archive powered by MHonArc 2.6.24.

Top of Page