Re: Datetime field issue

Frediano Ziglio <[email protected]>
Newsgroups gmane.comp.db.tds.freetds
Message-ID <1260630987.5010.2.camel@freddy> 
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: [email protected]
> [mailto:[email protected]] On Behalf Of Mike Blake
> Sent: Thursday, December 10, 2009 11:41 AM
> To: [email protected]
> 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

_______________________________________________
FreeTDS mailing list
[email protected]
http://lists.ibiblio.org/mailman/listinfo/freetds
vedi.diff (text/x-patch, 5.1 KB) 
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)
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.