FreeTDS Development Group

[Man Min Yan <manmin_yan AT yahoo.com.au>]

Hi Frediano,

You're right, tds_free_dynamic doesn't seem to be right. It's not removing 
the items from the tds->dyns list properly. Also, my previous patch is not 
right as it would throw away any previously prepared statement. Here's my 2nd 
go at fixing this problem.

I've also tried to identify where the prepares were happening, and it seems 
to be a freetds problem. Here's the call stack:
    main()
    perl_run()
    Perl_runops_standard()
    Perl_pp_entersub()
    XS_DBI_dispatch()
    XS_DBD__ODBC__st_execute()
    odbc_st_execute()
    SQLExecute()    from libodbc.so.1
    SQLExecute() at odbc.c:3343
    _SQLExecute() at odbc.c:3142
    tds_submit_prepare() at query.c:1007

The code in _SQLExecute is actually un-preparing the statement, re-preparing 
it, then executing it every time it is called.

Rgds,
Man Min



----- Original Message ----
From: Frediano Ziglio <freddy77 AT gmail.com>
To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
Sent: Wednesday, 5 November, 2008 2:46:52 AM
Subject: Re: [freetds] Procedure or function has too many arguments specified

>
> Hi,
>
> I think I have found the cause to this problem. The following patch
> fixes the problem, but I don't know the code well enough to know
> whether this is the proper fix or not.
>
> *** mem.c.orig  2008-11-04 16:35:39.000000000 +1100
> --- mem.c       2008-11-04 16:23:24.000000000 +1100
> ***************
> *** 99,104 ****
> --- 99,105 ----
>         for (curr = tds->dyns; curr != NULL; curr = curr->next)
>                 if (!strcmp(curr->id, id)) {
>                         /* id already exists! just return it */
> +                       curr->num_id = 0;
>                         return curr;
>                 }
>
> This is what's causing the problem to occur:
>
> - The algorithm in the tds_get_dynid function in tds/query.c will
> always return the same 65535 ids in a single session (The code
> increments the variable inc_num everytime the function is called, but
> then restricts its value with incr & 0xffff). This means that after
> the 65536's iteration of a query through the sp_prepare/sp_execute
> cycle, the id is the same as iteration 1.
>

Good spot...
My doubt is why the dynamic is still around ??
ODBC should free this so you shouldn't have so old dynamics... Perhaps does
it fail to free??

> - In tds_submit_prepare, the id from tds_get_dynid is passed to
> tds_alloc_dynamic. When given a previously used id, tds_alloc_dynamic
> will return the previously allocated structure. However, it doesn't
> clear its contents (in particular, the num_id variable). This causes
> the num_id returned from the server to be ignored in
> tds_process_tokens, in the TDS_PARAM_TOKEN case, where it does
>         if (tds->internal_sp_called == TDS_SP_PREPARE
>                         && tds->cur_dyn && ***tds->cur_dyn->num_id ==
> 0*** && curcol->column_cur_size > 0) {
>                         tds->cur_dyn->num_id = *(TDS_INT *)
> curcol->column_data;
>                     }
>
>
> This later causes freetds to try to sp_execute the wrong prepared
> statement.
>
>
> Man Min
>
>
>
> ----- Original Message ----
> From: Man Min Yan <manmin_yan AT yahoo.com.au>
> To: freetds AT lists.ibiblio.org
> Sent: Friday, 17 October, 2008 1:25:27 PM
> Subject: [freetds] Procedure or function has too many arguments
> specified
>
> Hi,
> I am getting the above error when trying to execute over
> 65535 statements (tested with insert, update and select) using
> DBD::ODBC, unixODBC and freeTDS (both 0.82 stable and
> 0.83 20081016) talking to SQL Server 2000 and 2005. I can consistently
> replicate the problem with the script below.
>
> Please note:
> - my freetds.conf is configured with 'tds version = 8.0'
> - I'm running on f7, with unixODBC 2.2.12-5, DBD::ODBC 1.13
> - the script runs fine if I use DBD::ODBC, unixODBC, mysql-connector
> (sorts of eliminates DBD::ODBC, unixODBC as the culprits)
> - the script runs fine if I use DBD::Proxy (eliminates the dbms side
> as the culprit)
> - the script only fails if there is a prepared select present (even if
> never used).
> - it always fails on record 65536 (16-bit number somewhere??)
> - I've attached an extract of the freetds dump log for the last couple
> of records.
>
> Any help in solving this problem would be greatly appreciated. Thanks.
> ---
> #!/usr/local/bin/perl -w
>
> use strict;
> use DBI;
>
> # The table 'test_table' has the following structure
> #    create table test_table (id int not null, value varchar(10))
>
> my $dsn = 'dbi:ODBC:sqlserver'; my $user='xx'; my $pass='xx'; #my $dsn
> = 'dbi:ODBC:mysql'; my $user='xx'; my $pass='xx'; #my $dsn =
> 'dbi:Proxy:hostname=myserver;port=40000;dsn=dbi:ODBC:sqlserver
'; my $user='xx'; my $pass='xx';
> my $dbh = DBI->connect($dsn, $user, $pass,
>     {
>         PrintError => 0,
>         RaiseError => 0,
>         AutoCommit => 1,
>     }) or die $DBI::errstr;
> $dbh->do('truncate table test_table') or die $dbh->errstr;
>
> #my $updSth = $dbh->prepare('update test_table set value=?
> where id=?') or die $dbh->errstr;
> my $selSth = $dbh->prepare('select * from test_table where
> id=?') or die $dbh->errstr;
> my $insSth = $dbh->prepare('insert into test_table values (?,
> ?)') or die $dbh->errstr;
>
> $| = 1;
> my $maxIteration = 100000;
> for (my $i = 1; $i <= $maxIteration; $i++) {
>     print "$i\r";
>     $insSth->execute($i, $i) or die "Fail execute with $i - ",
> $insSth->errstr;
>     #$updSth->execute('v' . $i, $i) or die "Fail execute with $i - ",
> $updSth->errstr;
>     #$selSth->execute($i) or die "Fail execute with $i - ",
> $selSth->errstr;
>     #$selSth->finish();
> }
>

So... A prepare and many execute... I would ask why perl keep preparing so
much queries... Or is it a FreeTDS problem??

Could you enable ODBC trace. I'm very busy with other issues so I have very
few time...

Freddy77
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds



      Search 1000's of available singles in your area at the new Yahoo!7 
Dating. Get Started http://au.dating.yahoo.com/?cid=53151&pid=1011

Attachment: patch
Description: Binary data