FreeTDS Development Group

[Dylan Kucera <djkucera AT sympatico.ca>]

Hi Freddy, thanks for your response... (continued below)

> Well... from log the problem is not that FreeTDS cannot detect
> SQLNumResultCols and prepare is that if there are parameters FreeTDS
> is not able to prepare correctly statement resulting in an error in
> the log.
> The Microsoft solution is similar to SQLDescribeParam solution. It
> parse the query and if able it issue a special crafted query to get
> types. This is currently not supported by FreeTDS. And not even
> planned.

So you don't like my work-around concept?  To use a combination of
examining the query or, if possible (ie. no unbound parameters),
issue the Execute early?

> I don't understand why it works using protocol 7.0...

It doesn't - the Protocol 7/8 problem I had was with that "sp_unprepare 0"
problem that you put a fix in for.  Haven't gotten around to trying that
fix out yet...

Thanks again,
DK.


> freddy77


> 2011/3/31 Dylan Kucera <djkucera AT sympatico.ca>:
>> Freddy,
>>
>> Attached is a dump of the problem occurring with the unmodified
>> FreeTDS.  I'm working with 0.83.dev.20110125 as a base.
>>
>> The plot thickened a little bit when we went to upgrade Oracle
>> Heterogeneous Services in order to try to remedy some other issues.
>> The newer version required the driver to also be able to
>> SQLDescribeCol before SQLExecute.  So I started using a method of
>> Executing the statement early in SQLNumResultCols and SQLDescribeCol
>> and keeping a record of that fact so I wouldn't try to execute twice.
>> This worked until it also needed to do SQLNumResultCols on statements
>> with Bind Variables before binding all the variables.  So I had to
>> keep my original "counting commas" approach as well.
>>
>> I'm including a diff file as well of the things I have done to odbc.c
>> in order to allow (in some use-cases) the early use of
>> SQLNumResultCols and SQLDescribeCol.  Maybe you can think of a more
>> elegant way to do it?  I'm sure there must be a better way than a lazy
>> global variable approach, but I'm not familiar enough with the
>> underlying structures to make the change properly.  And it's most
>> certainly not MARS friendly! :-O  Regardless, for now, this appears to
>> be a decent stop-gap for our use-case as I've not found a way in which
>> Oracle Heterogeneous Services can trip up FreeTDS with this patch.
>>
>> I will be travelling for the next couple of weeks, so please forgive
>> me if I'm latent in responses for the next while - I am still very
>> interested in finding a solution that the community would be willing
>> to adopt into the official code.
>>
>> Thank you for your response and consideration!
>> DK.
>>
>> On Tuesday, March 29, 2011 11:08:47 AM, Frediano Ziglio wrote:
>>
>>> Ehmm... should work... could you post a dump please??
>>
>>> There is also a test that check this.
>>
>>> freddy77
>>
>>> 2011/3/12 Dylan Kucera <djkucera AT sympatico.ca>:
>>>> The following post is unrelated to the issue I just posted with respect 
>>>> to
>>>> the "handle 0" problem with TDS 8.0 that I just posted.
>>>>
>>>> I was hoping to get some feedback on a work-around that I created for the
>>>> limitation where FreeTDS cannot determine the answer for SQLNumResultCols
>>>> after only calling SQLPrepare.  As designed, from what I understand,
>>>> SQLExecute must be called first.
>>>>
>>>> I'm working with the Oracle Heterogenous Services database link 
>>>> capability
>>>> so I have no ability to change the approach it takes, and in some
>>>> scenarios it asks for SQLNumResultCols after only doing a SQLPrepare.
>>>>
>>>> From what I've observed, I believe I can count on the structure of the
>>>> statement where this occurs to be
>>>>
>>>> SELECT "c1", "c2", ..., "cn" FROM "t1" WHERE ...
>>>>
>>>> So, assuming there is no reason to believe the database link has chosen
>>>> invalid columns (which it won't because it's already confirmed the column
>>>> list with sp_columns!  It just seems to have obsessive compulsive
>>>> disorder!) then counting the commas before the FROM and adding one does
>>>> the trick.
>>>>
>>>> I know it's not the most elegant thing in the world but after doing this 
>>>> I
>>>> can use my Oracle Heterogeneous Services database links just fine.  For
>>>> those who generally work on the FreeTDS code, would you consider putting
>>>> something like this into the main code line for those of us wishing to 
>>>> use
>>>> FreeTDS in this use case?  Would it really be worse than returning 0?
>>>>
>>>> Perhaps at the least this helps someone else out, though I'd be open to
>>>> any cautionary tales if someone has already been down this path.
>>>>
>>>> in odbc.c, 2 changes:
>>>>
>>>> 1) Near line 1320:  redefine IRD_UPDATE as per another post I found where
>>>> someone had a similar problem:
>>>>
>>>> #define IRD_UPDATE(desc, errs, exit) do { } while(0)
>>>>
>>>> #if 0
>>>> #define IRD_UPDATE(desc, errs, exit) \
>>>>        do { \
>>>>                if (desc->type == DESC_IRD && 
>>>> ((TDS_STMT*)desc->parent)->need_reprepare && \
>>>>                    odbc_update_ird((TDS_STMT*)desc->parent, errs) != 
>>>> SQL_SUCCESS) \
>>>>                        exit; \
>>>>        } while(0)
>>>> #endif
>>>>
>>>> 2) Near line 4425: redefine SQLNumResultCols as follows:
>>>>
>>>> SQLRETURN ODBC_API
>>>> SQLNumResultCols(SQLHSTMT hstmt, SQLSMALLINT FAR * pccol)
>>>> {
>>>> char *s;
>>>> char *p;
>>>> long c;
>>>>
>>>>        INIT_HSTMT;
>>>>
>>>>
>>>>        tdsdump_log(TDS_DBG_FUNC, "SQLNumResultCols(%p, %p)\n",
>>>>                        hstmt, pccol);
>>>>
>>>>        /*
>>>>         * 3/15/2001 bsb - DBD::ODBC calls SQLNumResultCols on non-result
>>>>         * generating queries such as 'drop table'
>>>>         */
>>>> #if 0
>>>>        if (stmt->row_status == NOT_IN_ROW) {
>>>>                odbc_errs_add(&stmt->errs, "24000", NULL);
>>>>                ODBC_RETURN(stmt, SQL_ERROR);
>>>>        }
>>>> #endif
>>>>        IRD_UPDATE(stmt->ird, &stmt->errs, ODBC_RETURN(stmt, SQL_ERROR));
>>>>        *pccol = stmt->ird->header.sql_desc_count;
>>>>
>>>>
>>>>        if (stmt->ird->header.sql_desc_count == 0) {
>>>> tdsdump_log(TDS_DBG_FUNC, "SQLNumResultCols(%p, %p) - Desparate measures 
>>>> - parse from %s\n",
>>>> hstmt, pccol, stmt->prepared_query);
>>>>                s = malloc (strlen(stmt->prepared_query) + 64);
>>>>                strcpy(s, stmt->prepared_query);
>>>>                strupr(s);
>>>>                p = strstr(s, " FROM ");
>>>>                if (p != 0) {
>>>>                        *p = '\0';
>>>>                        c = 1;
>>>>                        p = s;
>>>>                        while ((p = strchr(p+1, ',')) != 0) {
>>>>                                ++c;
>>>>                        }
>>>>                        *pccol = c;
>>>>                }
>>>>                free(s);
>>>>        }
>>>>
>>>>
>>>> tdsdump_log(TDS_DBG_FUNC, "SQLNumResultCols(%p, %p) = %d\n",
>>>> hstmt, pccol, *pccol);
>>>>
>>>>
>>>>        ODBC_RETURN_(stmt);
>>>> }
>>>>
>>>>
>>>> _______________________________________________
>>>> FreeTDS mailing list
>>>> FreeTDS AT lists.ibiblio.org
>>>> http://lists.ibiblio.org/mailman/listinfo/freetds
>>>>
>> _______________________________________________
>> FreeTDS mailing list
>> FreeTDS AT lists.ibiblio.org
>> http://lists.ibiblio.org/mailman/listinfo/freetds
>>
>>
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds