Re: [Tcl-tdbc] TDBC, stored procedures, output parameters, and multiple results.

[Kevin K. <kk...@ny...>]

Dirk,

I've finally managed to make a version of TDBC that I think will allow
for you to work around stored procedure limitations. (I'm still working
on making OUT and INOUT parameters work; that turns out to be seriously
tricky, since ODBC isn't really willing to tell a caller in advance
whether a parameter in an unknown statement is IN, OUT or INOUT.) But
you should be able to do what you suggested and set up a call like:

set name fred
set data [$connection allrows {
     DECLARE @x INTEGER;
     EXECUTE lookup_person :name, @x;
     SELECT @x AS result;
}]

and get a row like {result 1-518-555-1212}

Essentially, the changes are:

- @ is no longer a special character in the SQL accepted by tdbc::odbc
   (or tdbc::postgres or tdbc::mysql). This allows for user variables
   on those three drivers.

- tdbc::odbc and tdbc::sqlite3 now allow semicolons in statements.

- All drivers now have a 'nextresults' method on result sets so that
   a statement can return multiple groups of results (either because
   it has multiple semicolon-separated pieces, or because a stored
   procedure returns multiple cursors). 'nextresults' is called after
   processing a set of rows ('nextrow', 'nextlist' or 'nextdict' has
   returned 0) and returns 1 if there's another set of rows to process,
   or 0 if the statement is completely finished.

- columns and rowcount on result sets are updated at 'nextresults'

- 'allrows' and 'foreach' methods continue as long as 'nextresults'
   returns 1.  The '-columnsvariable' gets updated every time
   'nextresults' is called.

If this gives you enough to make progress, let me know and I'll try to
roll up another beta release.  And I'll try to keep plugging on real
OUTPUT parameters!

-- 
73 de ke9tv/2, Kevin