Skip to Content.
Sympa Menu

freetds - Re: [freetds] Datetime field issue

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Frediano Ziglio <freddy77 AT gmail.com>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Datetime field issue
  • Date: Sat, 12 Dec 2009 16:16:27 +0100

Il giorno gio, 10/12/2009 alle 15.24 -0500, Mike Blake ha scritto:
> Doing some digging. It appears that FreeTDS is reporting the DATETIME
> columns as type 111.
>
> -----Original Message-----
> From: freetds-bounces AT lists.ibiblio.org
> [mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Mike Blake
> Sent: Thursday, December 10, 2009 11:41 AM
> To: freetds AT lists.ibiblio.org
> Subject: [freetds] Datetime field issue
>
> Working on a migration from Oracle 10.2 on Windows to Oracle 11.2 on
> Linux. There are processes that use ODBC to get data from SQL Server.
> I have created an 11.2 database on a test Linux box and configured
> unixODBC, FreeTDS, and the Oracle Gateway for ODBC. When I try to query
> a table with a DATETIME column it comes back with an ORA-00904 that the
> column does not exist. Looking at the gateway log, the DATETIME columns
> are showing up as a dtype:11 which is not mapping to any data type. The
> other data types are mapping OK. Oracle support says that the ODBC
> driver (FreeTDS) is using the ODBC v2 standard and that the gateway
> requires ODBC v3 which would tag the DATETIME column as dtype:93. Is
> Oracle support confused? Is FreeTDS installed wrong on my Linux server?
>
>
>
> Mike Blake
>

Ok.... Oracle seems to call SQLColumns to get information from table.
Currently our call reply with ODBC 2 data... please try attached patch,
it apply to CVS HEAD and 0.82 branch too.

freddy77

Index: src/odbc/odbc.c
===================================================================
RCS file: /cvs/freetds/freetds/src/odbc/odbc.c,v
retrieving revision 1.517
diff -U 10 -r1.517 odbc.c
--- src/odbc/odbc.c	29 Nov 2009 18:56:47 -0000	1.517
+++ src/odbc/odbc.c	12 Dec 2009 15:04:01 -0000
@@ -909,23 +909,24 @@
 		    SQLSMALLINT cbColumnName)
 {
 	int retcode;
 
 	INIT_HSTMT;
 
 	tdsdump_log(TDS_DBG_FUNC, "SQLProcedureColumns(%p, %p, %d, %p, %d, %p, %d, %p, %d)\n", 
 			hstmt, szCatalogName, cbCatalogName, szSchemaName, cbSchemaName, szProcName, cbProcName, szColumnName, cbColumnName);
 
 	retcode =
-		odbc_stat_execute(stmt, "sp_sproc_columns ", 4, "O@procedure_qualifier", szCatalogName, cbCatalogName,
+		odbc_stat_execute(stmt, "sp_sproc_columns ", TDS_IS_MSSQL(stmt->dbc->tds_socket) ? 5 : 4,
+				  "O@procedure_qualifier", szCatalogName, cbCatalogName,
 				  "P@procedure_owner", szSchemaName, cbSchemaName, "P@procedure_name", szProcName, cbProcName,
-				  "P@column_name", szColumnName, cbColumnName);
+				  "P@column_name", szColumnName, cbColumnName, "V@ODBCVer", (char*) NULL, 0);
 	if (SQL_SUCCEEDED(retcode) && stmt->dbc->env->attr.odbc_version == SQL_OV_ODBC3) {
 		odbc_col_setname(stmt, 1, "PROCEDURE_CAT");
 		odbc_col_setname(stmt, 2, "PROCEDURE_SCHEM");
 		odbc_col_setname(stmt, 8, "COLUMN_SIZE");
 		odbc_col_setname(stmt, 9, "BUFFER_LENGTH");
 		odbc_col_setname(stmt, 10, "DECIMAL_DIGITS");
 		odbc_col_setname(stmt, 11, "NUM_PREC_RADIX");
 	}
 	ODBC_RETURN_(stmt);
 }
@@ -4525,23 +4526,24 @@
 	   SQLSMALLINT cbColumnName)
 {
 	int retcode;
 
 	INIT_HSTMT;
 
 	tdsdump_log(TDS_DBG_FUNC, "SQLColumns(%p, %s, %d, %s, %d, %s, %d, %s, %d)\n", 
 			hstmt, szCatalogName, cbCatalogName, szSchemaName, cbSchemaName, szTableName, cbTableName, szColumnName, cbColumnName);
 
 	retcode =
-		odbc_stat_execute(stmt, "sp_columns ", 4, "P@table_name", szTableName, cbTableName, "P@table_owner", szSchemaName,
+		odbc_stat_execute(stmt, "sp_columns ", TDS_IS_MSSQL(stmt->dbc->tds_socket) ? 5 : 4,
+				  "P@table_name", szTableName, cbTableName, "P@table_owner", szSchemaName,
 				  cbSchemaName, "O@table_qualifier", szCatalogName, cbCatalogName, "P@column_name", szColumnName,
-				  cbColumnName);
+				  cbColumnName, "V@ODBCVer", (char*) NULL, 0);
 	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_col_setname(stmt, 7, "COLUMN_SIZE");
 		odbc_col_setname(stmt, 8, "BUFFER_LENGTH");
 		odbc_col_setname(stmt, 9, "DECIMAL_DIGITS");
 		odbc_col_setname(stmt, 10, "NUM_PREC_RADIX");
 	}
 	ODBC_RETURN_(stmt);
 }
@@ -6378,23 +6380,24 @@
 		scope = 'C';
 	else
 		scope = 'T';
 
 	if (fColType == SQL_BEST_ROWID)
 		col_type = 'R';
 	else
 		col_type = 'V';
 
 	retcode =
-		odbc_stat_execute(stmt, "sp_special_columns ", TDS_IS_MSSQL(stmt->dbc->tds_socket) ? 6 : 4, "O", szTableName,
+		odbc_stat_execute(stmt, "sp_special_columns ", TDS_IS_MSSQL(stmt->dbc->tds_socket) ? 7 : 4, "O", szTableName,
 				  cbTableName, "O", szSchemaName, cbSchemaName, "O@qualifier", szCatalogName, cbCatalogName,
-				  "@col_type", &col_type, 1, "@scope", &scope, 1, "@nullable", &nullable, 1);
+				  "@col_type", &col_type, 1, "@scope", &scope, 1, "@nullable", &nullable, 1,
+				  "V@ODBCVer", (char*) NULL, 0);
 	if (SQL_SUCCEEDED(retcode) && stmt->dbc->env->attr.odbc_version == SQL_OV_ODBC3) {
 		odbc_col_setname(stmt, 5, "COLUMN_SIZE");
 		odbc_col_setname(stmt, 6, "BUFFER_LENGTH");
 		odbc_col_setname(stmt, 7, "DECIMAL_DIGITS");
 	}
 	ODBC_RETURN_(stmt);
 }
 
 SQLRETURN ODBC_API
 SQLStatistics(SQLHSTMT hstmt, SQLCHAR FAR * szCatalogName, SQLSMALLINT cbCatalogName, SQLCHAR FAR * szSchemaName,
@@ -6775,20 +6778,22 @@
 
 	/* read all params and calc len required */
 	va_start(marker, nparams);
 	len = strlen(begin) + 2;
 	for (i = 0; i < nparams; ++i) {
 		int param_len;
 
 		p = va_arg(marker, char *);
 
 		switch (*p) {
+		case 'V':	/* ODBC version */
+			len += strlen(p) + 3;
 		case 'O':	/* ordinary arguments */
 		case 'P':	/* pattern value arguments */
 			params[i].type = *p++;
 			break;
 		default:
 			params[i].type = 0;	/* ordinary type */
 			break;
 		}
 		params[i].name = p;
 
@@ -6815,28 +6820,31 @@
 		ODBC_RETURN(stmt, SQL_ERROR);
 	}
 
 	/* build string */
 	p = proc;
 	if (param_qualifier >= 0)
 		p += tds_quote_id(stmt->dbc->tds_socket, p, params[param_qualifier].value, params[param_qualifier].len);
 	strcpy(p, begin);
 	p += strlen(begin);
 	for (i = 0; i < nparams; ++i) {
-		if (!params[i].value)
+		if (!params[i].value && params[i].type != 'V')
 			continue;
 		if (params[i].name[0]) {
 			strcpy(p, params[i].name);
 			p += strlen(params[i].name);
 			*p++ = '=';
 		}
-		p += odbc_quote_metadata(stmt->dbc, params[i].type, p, params[i].value, params[i].len);
+		if (params[i].type != 'V')
+			p += odbc_quote_metadata(stmt->dbc, params[i].type, p, params[i].value, params[i].len);
+		else
+			*p++ = (stmt->dbc->env->attr.odbc_version == SQL_OV_ODBC3) ? '3': '2';
 		*p++ = ',';
 	}
 	*--p = '\0';
 	assert(p - proc + 1 <= len);
 
 	/* set it */
 	retcode = odbc_set_stmt_query(stmt, proc, p - proc);
 	free(proc);
 
 	if (retcode != SQL_SUCCESS)



Archive powered by MHonArc 2.6.24.

Top of Page