Re: FreeTDS 0.82 returning ODBC v2 values instead of ODBCv3 values

I tested with the Easysoft ODBC driver and saw that Sql Server datetime
columns get translated to SQL_TYPE_TIMESTAMP data type 93.  Is there a
way to make FreeTDS translate the same way?  

Oracle 11 will not be able to use FreeTDS until date columns can be
queried.

-----Original Message-----
From: [email protected]
[mailto:[email protected]] On Behalf Of Frediano Ziglio
Sent: Saturday, December 19, 2009 9:16 AM
To: FreeTDS Development Group
Subject: Re: [freetds] FreeTDS 0.82 returning ODBC v2 values instead of
ODBCv3 values

Il giorno ven, 18/12/2009 alle 16.19 -0500, Mike Blake ha scritto:
> Put that code change into odbc.c and recompiled FreeTDS.  Getting the
> same results.  Is there something in the freetds log file that would
> show me that it's using odbc v3?  I see it mentioned in the unixODBC
log
> but can't find any mention of it in the freetds log.
> 

>From log you should see something about SQLSetEnvAttr.

freddy77


> 
> -----Original Message-----
> From: [email protected]
> [mailto:[email protected]] On Behalf Of Frediano
Ziglio
> Sent: Thursday, December 17, 2009 4:13 PM
> To: FreeTDS Development Group
> Subject: Re: [freetds] FreeTDS 0.82 returning ODBC v2 values instead
of
> ODBCv3 values
> 
> I did some tests and no, odbc 3.x must set version, otherwise
allocating
> connection fails and you cannot connect.
> 
> However I applied this patch to CVS HEAD
> 
> 
> 
> 
> Automatic odbc version selection
> ---
> 
> --- a/ChangeLog
> +++ b/ChangeLog
> @@ -1,3 +1,6 @@
> +Thu Dec 17 22:08:50 CET 2009    Frediano Ziglio
> <freddy77_A_gmail_D_com>
> +	* src/odbc/odbc.c: Automatic odbc version selection
> +
>  Thu Dec 17 11:33:39 CET 2009    Frediano Ziglio
> <freddy77_A_gmail_D_com>
>  	* include/tdsodbc.h src/odbc/odbc.c:
>  	- Fix statistics functions for Sybase
> @@ -2010,4 +2013,4 @@ Wed Jan  9 19:54:43 EST 2008	JK Lowden
> <[email protected]>
>  	* ChangeLog-0.82 added because of release
>  	
>  $FreeTDS$
> -$Id: ChangeLog,v 1.2912 2009-12-17 10:33:59 freddy77 Exp $
> +$Id: ChangeLog,v 1.2913 2009-12-17 21:08:59 freddy77 Exp $
> 
> --- a/src/odbc/odbc.c
> +++ b/src/odbc/odbc.c
> @@ -60,10 +60,10 @@
>  #include <dmalloc.h>
>  #endif
>  
> -TDS_RCSID(var, "$Id: odbc.c,v 1.521 2009-12-17 10:33:59 freddy77 Exp
> $");
> +TDS_RCSID(var, "$Id: odbc.c,v 1.522 2009-12-17 21:08:59 freddy77 Exp
> $");
>  
>  static SQLRETURN _SQLAllocConnect(SQLHENV henv, SQLHDBC FAR * phdbc);
> -static SQLRETURN _SQLAllocEnv(SQLHENV FAR * phenv);
> +static SQLRETURN _SQLAllocEnv(SQLHENV FAR * phenv, SQLINTEGER
> odbc_version);
>  static SQLRETURN _SQLAllocStmt(SQLHDBC hdbc, SQLHSTMT FAR * phstmt);
>  static SQLRETURN _SQLAllocDesc(SQLHDBC hdbc, SQLHDESC FAR * phdesc);
>  static SQLRETURN _SQLFreeConnect(SQLHDBC hdbc);
> @@ -1320,7 +1320,7 @@ SQLAllocHandle(SQLSMALLINT HandleType, SQLHANDLE
> InputHandle, SQLHANDLE * Output
>  		return _SQLAllocConnect(InputHandle, OutputHandle);
>  		break;
>  	case SQL_HANDLE_ENV:
> -		return _SQLAllocEnv(OutputHandle);
> +		return _SQLAllocEnv(OutputHandle, SQL_OV_ODBC3);
>  		break;
>  	case SQL_HANDLE_DESC:
>  		return _SQLAllocDesc(InputHandle, OutputHandle);
> @@ -1392,20 +1392,20 @@ SQLAllocConnect(SQLHENV henv, SQLHDBC FAR *
> phdbc)
>  }
>  
>  static SQLRETURN
> -_SQLAllocEnv(SQLHENV FAR * phenv)
> +_SQLAllocEnv(SQLHENV FAR * phenv, SQLINTEGER odbc_version)
>  {
>  	TDS_ENV *env;
>  	TDSCONTEXT *ctx;
>  
> -	tdsdump_log(TDS_DBG_FUNC, "_SQLAllocEnv(%p)\n", 
> -			phenv);
> +	tdsdump_log(TDS_DBG_FUNC, "_SQLAllocEnv(%p, %d)\n",
> +			phenv, (int) odbc_version);
>  
>  	env = (TDS_ENV *) calloc(1, sizeof(TDS_ENV));
>  	if (!env)
>  		return SQL_ERROR;
>  
>  	env->htype = SQL_HANDLE_ENV;
> -	env->attr.odbc_version = SQL_OV_ODBC2;
> +	env->attr.odbc_version = odbc_version;
>  	/* TODO use it */
>  	env->attr.output_nts = SQL_TRUE;
>  
> @@ -1432,7 +1432,7 @@ SQLAllocEnv(SQLHENV FAR * phenv)
>  {
>  	tdsdump_log(TDS_DBG_FUNC, "SQLAllocEnv(%p)\n", phenv);
>  
> -	return _SQLAllocEnv(phenv);
> +	return _SQLAllocEnv(phenv, SQL_OV_ODBC2);
>  }
>  
>  static SQLRETURN
> 
> 
> 
> 
> The easier try is to replace in src/odbc/odbc.c
> 
> env->attr.odbc_version = SQL_OV_ODBC2;
> 
> with
> 
> env->attr.odbc_version = SQL_OV_ODBC3;
> 
> (should work even on 0.82)
> 
> freddy77
> 
> 
> Il giorno gio, 17/12/2009 alle 11.31 -0500, [email protected]
ha
> scritto:
> > On Thu, Dec 17, 2009 at 04:01:46PM +0100, Michal Seliga wrote:
> > > hi,
> > > 
> > > when i am connecting to odbc from qt in linux i had to add
> connection parameter
> > > SQL_ATTR_ODBC_VERSION=SQL_OV_ODBC3, otherwise it also didn't
worked
> properly
> > > 
> > > i don't know oracle, but try to find if you can set connection
> parameters
> > > somewhere, it might help in your case too
> > 
> > The FreeTDS ODBC driver doesn't automatically behave as ODBC v3.  It
> has to be told to.  In C:
> > 
> > 	SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, 
> > 			(SQLPOINTER)SQL_OV_ODBC3, SQL_IS_UINTEGER));
> > 
> > It seems that other drivers infer the application's intended ODBC
> version from the kinds of function calls they make:
> > 
> > http://msdn.microsoft.com/en-us/library/ms714001%28VS.85%29.aspx
> > 
> > 	"ODBC 3.x applications should never call SQLAllocEnv. As a
> result, if the Driver Manager receives a call to SQLAllocEnv, it
> recognizes the application as an ODBC 2.x application."
> > 	
> >
>
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/
> com.ibm.db29.doc.odbc/db2z_drivbhav.htm
> > 
> > 	"The SQL_ATTR_ODBC_VERSION environment attribute controls
> whether the DB2 ODBC 3.0driver exhibits ODBC 2.0 or ODBC 3.0 behavior.
> This value is implicitly set by the ODBC driver by application calls
to
> the ODBC 3.0 function SQLAllocHandle() or the ODBC 2.0 function
> SQLAllocEnv()."
> > 	
> > Perhaps the Oracle software expects the driver to set the ODBC
version
> automatically.  Adding SQL_ATTR_ODBC_VERSION to the connection string
> might help.  
> > 
> > --jkl
> > 
> > 
> > > 
> > > 
> > > On 17. 12. 2009 15:49, Mike Blake wrote:
> > > > Just doing isql or tsql I can query the datetime columns.  The
> problem is when I try to query from Oracle.  I have a DB link in
Oracle
> pointing to the ODBC connection (FreeTDS).  When I try to do the query
> including the datetime columns in Oracle, the log form Oracle shows
the
> datetime columns being ignored.  The datatype code listed for the
> columns being ignored is 11 (ODBC v2) instead of 93 which is the ODBC
v3
> code for datetime or timestamp.  I need FreeTDS to always use the ODBC
> v3 values.  Is there any way to force it to default to ODBC v3?  Or is
> there something in unixODBC that can be set to force ODBC v3?  Anybody
> who upgrades to Oracle 11 will lose the ability to use FreeTDS if it
> can't do ODBC v3 by default.
> > > > 
> > > > I don't get anything very useful in the FreeTDS or unixODBC logs
> when querying from Oracle since the query fails.  I can attach the
> Oracle log which shows the list of columns being looked at and the
> datetime columns being ignored due to being datatype 11.
> > _______________________________________________
> > FreeTDS mailing list
> > [email protected]
> > http://lists.ibiblio.org/mailman/listinfo/freetds
> 
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds

_______________________________________________
FreeTDS mailing list
[email protected]
http://lists.ibiblio.org/mailman/listinfo/freetds