Prev: teapot and libraries
Next: Parsed C++ source code with regsub does not compile on Windows
From: lowclouds on 17 Jun 2010 16:00
Does one exist for tcl that will talk to MS SQL Server?

I'd like to do something like the following:

select * from {select ....} into #temp
go
select x, y, z from #temp where <filter1>
go
select x,y,z from #temp where <filter2>
go
.....

tclodbc doesn't complain when I try this, but also doesn't work: it only
returns the result of the first select from the temp table (in this
case, the go statements are replaced by semi-colons.)

tdbc::odbc explicitly rejects semi-colons and go's are rejected by the
server.

It appears that 'db connect' in all it's incarnations is not really what
the server thinks of as a connection, because the temp table disappears
between calls to 'db $sql' or 'db allrows $sql' or all the other
variants i've tried.

is there some way of doing this that actually works or am I out of luck.

for anyone familiar with MS SQL Server Management Studio, one can create
a new 'query' and do the following

select getdate() as adate into #temp;
select * from #temp;
select adate as bdate from #temp

and get two results sets. the temp table persists across statement
boundaries. This is what I can't figure out how to do with tclodbc or
tdbc::odbc

craig
From: tom.rmadilo on 17 Jun 2010 16:19
On Jun 17, 1:00 pm, lowclouds <x...(a)nowhere.net> wrote:
> Does one exist for tcl that will talk to MS SQL Server?
>
> I'd like to do something like the following:
>
> select * from {select ....} into #temp
> go
> select x, y, z from #temp where <filter1>
> go
> select x,y,z from #temp where <filter2>
> go
> ....
>
> tclodbc doesn't complain when I try this, but also doesn't work: it only
> returns the result of the first select from the temp table (in this
> case, the go statements are replaced by semi-colons.)
>
> tdbc::odbc explicitly rejects semi-colons and go's are rejected by the
> server.
>
> It appears that 'db connect' in all it's incarnations is not really what
> the server thinks of as a connection, because the temp table disappears
> between calls to 'db $sql' or 'db allrows $sql' or all the other
> variants i've tried.
>
> is there some way of doing this that actually works or am I out of luck.
>
> for anyone familiar with MS SQL Server Management Studio, one can create
> a new 'query' and do the following
>
> select getdate() as adate into #temp;
> select * from #temp;
> select adate as bdate from #temp
>
> and get two results sets. the temp table persists across statement
> boundaries. This is what I can't figure out how to do with tclodbc or
> tdbc::odbc

Hopefully you won't figure it out. SQL Server was just identified as
the target of a massive SQL injection attack. According to MS, this is
caused by improper sanitization of user input, but part of the problem
is allowing multiple statements...creating a variable or table and
then referencing it in the next statement.

Of course, SQL server also silently truncates variables, so you can
declare a variable then rely on the fact that it will be truncated to
facilitate you SQL injection.

Don't use MS SQL unless you have mega dollars to review all code.

Also, if you want to use a temporary table, look into views with
parameters. Much easier to understand and probably much faster.
From: lowclouds on 17 Jun 2010 17:57
irrespective of Tom's possibly accurate comments, I'm still interested
in learning if it is possible to do this with any Tcl interface to MS
SQL Server. It is what it is.

thanks,
craig
From: Gerald W. Lester on 17 Jun 2010 18:18
lowclouds wrote:
> irrespective of Tom's possibly accurate comments, I'm still interested
> in learning if it is possible to do this with any Tcl interface to MS
> SQL Server. It is what it is.

Well, at one time the sysql extension compiled and linked against MS SQL
Server -- if it is possible, I would expect you would have to use that
interface or its current equivalent as what you want is not standard SQL and
something like ODBC connect and the like would be unlikely to support it.


--
+------------------------------------------------------------------------+
| Gerald W. Lester, President, KNG Consulting LLC |
| Email: Gerald.Lester(a)kng-consulting.net |
+------------------------------------------------------------------------+
From: Donal K. Fellows on 17 Jun 2010 19:41
On 17 June, 21:00, lowclouds <x...(a)nowhere.net> wrote:
> I'd like to do something like the following:
>
> select * from {select ....} into #temp
> go
> select x, y, z from #temp where <filter1>
> go
> select x,y,z from #temp where <filter2>
> go
> ....

My thoughts here are that either you should rewrite that so that it is
a single query (with subqueries, though my SQL skills aren't good
enough to write an example for you) or you should use a stored
procedure.

Donal.
 |  Next  |  Last
Pages: 1 2 3
Prev: teapot and libraries
Next: Parsed C++ source code with regsub does not compile on Windows