Re: [Tcl-tdbc] batching sql

[Patrick D. <pat...@ac...>]

Neither the stored procedure or separating the statements work in this case.
I have a lot of sql that's quite dynamic so a stored proc doesn't work and
the separate prepare / executes don't seem to keep temp tables in play. 

Take for example the following (overlook the fact that this could be done
efficiency in one sql, this just represents something much more complex).

select sod.productid into #temp1 from sales.salesorderdetail sod;
select th.productid into #temp2 from production.transactionhistory th;
select t1.productid, count(*) from #temp1 t1 join #temp2 t2 
	on t1.productid = t2.productid
	group by t1.productid
	having count(*) > 100000;

This would be impossible to do in three separate prepare / executes because
the temp table seems to get destroyed after the first execute. 

Here's a link on the MS site on batching. 
http://msdn.microsoft.com/en-us/library/ms131033.aspx

I think a good compromise would be to only return the result set for the
final SQL statement and automatically pitch the rest. This is what we have
done in the past with batch sql in our in house library. Maybe determine how
many semicolons there are in the sql and execute each one, destroy the
results until you get to the final one, then execute and create the result
set? The it's just a matter of documenting that the final result set is the
only one that will be returned. 

My opinion is that this would be a very useful feature, I hope you agree. I
can help if you want but it doesn't appear that the tdbc odbc code is on
sourceforge. 

Here's an example script against adventureworks. 

package require tdbc::odbc

set conn_string "DRIVER=SQL
Server;SERVER=127.0.0.1,1433;DBQ=AdventureWorks;UID=test_user;PWD=test_user;
APP=test_tdbc"
tdbc::odbc::connection create db $conn_string

set sql "select sod.productid into #temp1 from sales.salesorderdetail sod"
set sql_exec [db prepare $sql]
$sql_exec execute

set sql	"select th.productid into #temp2 from production.transactionhistory
th"
set sql_exec [db prepare $sql]
$sql_exec execute

set sql	"select t1.productid, count(*) from #temp1 t1 join #temp2 t2 
		on t1.productid = t2.productid
		group by t1.productid
		having count(*) > 100000"
set sql_exec [db prepare $sql]

and the results. 


bin\tclsh test_tdbc.tcl
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#temp1'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be
prepared.
(executing the statement)
    while executing
"my init ::oo::Obj12::Stmt::3"
    (in namespace inscope "::oo::Obj20" script line 1)
    invoked from within
"::namespace inscope ::oo::Obj20 {my init} ::oo::Obj12::Stmt::3"
    ("uplevel" body line 1)
    invoked from within
"uplevel 1 [list {*}[namespace code {my init}] $statement {*}$args]"
    (class "::tdbc::odbc::resultset" constructor line 3)
    invoked from within
"::tdbc::odbc::resultset create ::oo::Obj19::ResultSet::1
::oo::Obj12::Stmt::3"
    ("uplevel" body line 1)
    invoked from within
"uplevel 1  [list $resultSetClass create $name [self] {*}$args]"
    (class "::tdbc::statement" method "execute" line 7)
    invoked from within
"$sql_exec execute"
    invoked from within
"set results [$sql_exec execute]"
    (file "test_tdbc.tcl" line 21)




Patrick Dunnigan

Email: pat...@ac...

Visit us at www.activecompliance.com

This e-mail message is intended only for the use of the individual or entity
to which it is addressed, and may contain information that is confidential.
If you are not the intended recipient, any dissemination, distribution or
copying of this communication is strictly prohibited. If you have received
this communication in error, please notify us immediately by reply email.


-----Original Message-----
From: Kevin Kenny [mailto:kk...@ny...] 
Sent: Thursday, February 19, 2009 2:34 PM
To: pat...@ac...
Cc: tcl...@li...
Subject: Re: [Tcl-tdbc] batching sql

Patrick Dunnigan wrote:
> Is there anyway that this semicolon restriction can be removed and leave 
> it up to the individual user of tdbc to make that decision?

That's harder than it looks. The difficulty is that TDBC as specified
doesn't deal with multiple result sets, and that's a complicated thing
to add.  Would it be possible either to orchestrate the statements
with a Tcl procedure or else wrap them in a stored procedure in the
database?

-- 
73 de ke9tv/2, Kevin