Prev: teapot and libraries
Next: Parsed C++ source code with regsub does not compile on Windows
From: tom.rmadilo on 18 Jun 2010 20:09
On Jun 18, 4:49 pm, lowclouds <x...(a)nowhere.net> wrote:
> Replying to my original question...
>
> The problem, as I saw it, was that I wanted to make multiple queries
> against a temp table and did not see how to do this using either tclodbc
> or tdbc::odbc. It appeared that the lifetime of a temp table was the
> batch of sql statements submitted to the MS SQL Server and I ran into
> the problem that either batches of sql statements were explicitly not
> supported, tdbc::odbc, or possibly, that multiple result sets were not
> supported, tclodbc.
>
> It turns out that MS SQL Server supports two kinds of temp tables, local
> and global, and that global temp tables do survive sql statement calls.
> local tables are called #tmp and global tables, ##mytemp.
> So, now, I can happily continue to use tclodbc or tdbc::odbc.
>
> Thanks for all the pertinent replies,
> Craig
>
> Note: I don't see a consistent syntax for creating temporary tables in
> use. MS doesn't seem to support 'create temporary table', but 'create
> local temporary table' also doesn't appear to be standard.
> It also doesn't seem like sql batches are disallowed by ODBC, but I can
> see how supporting them could be difficult.

Maybe you're talking about transaction level stuff. It seems like you
should be able to create a table in one statement and use it later on.
But if you have auto-commit per statement set on your db handle, then
the temp table probably disappears like all other allocated
resources.

So maybe try three statements rolled into a transaction. How you do
that depends on the database driver, but this feature is vastly
different than executing three, or even two sql statements at one
time. Remember that each sql statement has a return code and possibly
a result set. Also, a stored procedure is equivalent to a transaction,
it either succeeds of fails and can execute multiple sql statements.
From: lowclouds on 18 Jun 2010 21:33
I don't think this is directly related to transactions. It is related to
support for batches of sql statements and for support of returning
multiple returnsets. Here's a simple example you can try at home:

package require sqlite3
sqlite3 play play.sqlite
play eval {create temp table mtemp as select 1 as c1, 2 as c2 }
play eval {select * from mtemp; select c1 from mtemp}

the last command returns "1 2 1", the output from both sql queries.

This doesn't work, directly translated using tclodbc against MS SQL
Server, for two reasons:
a: if mtemp is named #mtemp, as I first tried it, the table doesn't
exist when you try to eval the second sql batch
b: the second sql batch fails using tdbc::odbc because that
implementation rejects multiple sql statements separated by semi-colons
(it's even in the test suite!) It fails using tclobdc because it only
returns the first result set, ie:

db connect mssql ...
mssql {select 1 as c1, 2 as c2 into #mtemp;select * from #mtemp;
select c1 from #mtemp}

returns {1 2}

so, not a transaction thing, but a batch thing, as well as a lifetime
of temp table thing. sqlite persists all temp tables for the life of the
connection, while ms sql server provides two lifetime types (as well as
access rights differences.)

craig

On 6/18/2010 5:09 PM, tom.rmadilo wrote:
> On Jun 18, 4:49 pm, lowclouds<x...(a)nowhere.net> wrote:
>> Replying to my original question...
>>
>> The problem, as I saw it, was that I wanted to make multiple queries
>> against a temp table and did not see how to do this using either tclodbc
>> or tdbc::odbc. It appeared that the lifetime of a temp table was the
>> batch of sql statements submitted to the MS SQL Server and I ran into
>> the problem that either batches of sql statements were explicitly not
>> supported, tdbc::odbc, or possibly, that multiple result sets were not
>> supported, tclodbc.
>>
>> It turns out that MS SQL Server supports two kinds of temp tables, local
>> and global, and that global temp tables do survive sql statement calls.
>> local tables are called #tmp and global tables, ##mytemp.
>> So, now, I can happily continue to use tclodbc or tdbc::odbc.
>>
>> Thanks for all the pertinent replies,
>> Craig
>>
>> Note: I don't see a consistent syntax for creating temporary tables in
>> use. MS doesn't seem to support 'create temporary table', but 'create
>> local temporary table' also doesn't appear to be standard.
>> It also doesn't seem like sql batches are disallowed by ODBC, but I can
>> see how supporting them could be difficult.
>
> Maybe you're talking about transaction level stuff. It seems like you
> should be able to create a table in one statement and use it later on.
> But if you have auto-commit per statement set on your db handle, then
> the temp table probably disappears like all other allocated
> resources.
>
> So maybe try three statements rolled into a transaction. How you do
> that depends on the database driver, but this feature is vastly
> different than executing three, or even two sql statements at one
> time. Remember that each sql statement has a return code and possibly
> a result set. Also, a stored procedure is equivalent to a transaction,
> it either succeeds of fails and can execute multiple sql statements.

From: Donal K. Fellows on 19 Jun 2010 05:58
On 18/06/2010 04:36, jr4412 wrote:
> On Jun 18, 3:57 am, "tom.rmadilo"<tom.rmad...(a)gmail.com> wrote:
>> MS explains that SQL injection isn't their fault..
>
> nothing ever is, in fact, the world is at fault for not doing things
> the Redmond way. ;(

It's the fault of all those dastardly scum who don't use Visual Studio
to write their attacks!

Donal.
From: tom.rmadilo on 19 Jun 2010 12:12
On Jun 18, 6:33 pm, lowclouds <x...(a)nowhere.net> wrote:
> I don't think this is directly related to transactions. It is related to
> support for batches of sql statements and for support of returning
> multiple returnsets. Here's a simple example you can try at home:
>
> package require sqlite3
> sqlite3 play play.sqlite
> play eval {create temp table mtemp as select 1 as c1, 2 as c2 }
> play eval {select * from mtemp; select c1 from mtemp}
>
> the last command returns "1 2 1", the output from both sql queries.
>
> This doesn't work, directly translated using tclodbc against MS SQL
> Server, for two reasons:
>    a: if mtemp is named #mtemp, as I first tried it, the table doesn't
> exist when you try to eval the second sql batch
>    b: the second sql batch fails using tdbc::odbc because that
> implementation rejects multiple sql statements separated by semi-colons
> (it's even in the test suite!) It fails using tclobdc because it only
> returns the first result set, ie:
>
>    db connect mssql ...
>    mssql {select 1 as c1, 2 as c2 into #mtemp;select * from #mtemp;
> select c1 from #mtemp}
>
>    returns {1 2}
>
>    so, not a transaction thing, but a batch thing, as well as a lifetime
> of temp table thing. sqlite persists all temp tables for the life of the
> connection, while ms sql server provides two lifetime types (as well as
> access rights differences.)

Every connection handle should have a transaction boundary. Usually
this is per statement, since this minimizes the resources used to
maintain multiple simultaneous transactions/sessions whatever.

Also databases have interfaces which allow batches of statements,
usually loaded from disk, via a command line shell or something like
that. Some databases have direct copy batch interfaces to bypass the
traditional SQL statements. This is useful for saving and restoring a
database which you know is in a consistent state.

But my assumption is that temp tables are transaction level objects,
so they are not available outside the current transaction. Or, maybe
they are session level objects, so when a session dies, the resources
are cleaned up.

Obviously the session state is maintained by the interface, in this
case odbc as implemented by tdbc. I haven't read anything about ODBC
indicating the ability to execute multiple statements: a session can
maintain multiple statements, but it can only execute one at a time.
Each executed statement should get a result handle and an exception
handle, but these are separate data structures, so they don't get
combined. This would obviously be a programming nightmare, somewhat
similar to having a function return the combined result of every
statement in the body of the function.

The only thing that somewhat worries me about your results is that the
odbc interface to mssql actually executed the first statement and
discarded the remaining ones. I would expect an error instead. One
thing MS explains in their SQL injection guide is that slipping in
an ; can greatly facilitate an attack, for example:

update passwords set pass = 'mypass'; --'where user = 'me';

This is aided in mssql because you can also use the fact that certain
variables have limited length. The quoting functions will silently
truncate the result and include this in the query! Silent buffer
overflow! What a concept.

> craig
>
> On 6/18/2010 5:09 PM, tom.rmadilo wrote:
>
>
>
> > On Jun 18, 4:49 pm, lowclouds<x...(a)nowhere.net>  wrote:
> >> Replying to my original question...
>
> >> The problem, as I saw it, was that I wanted to make multiple queries
> >> against a temp table and did not see how to do this using either tclodbc
> >> or tdbc::odbc. It appeared that the lifetime of a temp table was the
> >> batch of sql statements submitted to the MS SQL Server and I ran into
> >> the problem that either batches of sql statements were explicitly not
> >> supported, tdbc::odbc, or possibly, that multiple result sets were not
> >> supported, tclodbc.
>
> >> It turns out that MS SQL Server supports two kinds of temp tables, local
> >> and global, and that global temp tables do survive sql statement calls..
> >> local tables are called #tmp and global tables, ##mytemp.
> >> So, now, I can happily continue to use tclodbc or tdbc::odbc.
>
> >> Thanks for all the pertinent replies,
> >> Craig
>
> >> Note: I don't see a consistent syntax for creating temporary tables in
> >> use. MS doesn't seem to support 'create temporary table', but 'create
> >> local temporary table' also doesn't appear to be standard.
> >> It also doesn't seem like sql batches are disallowed by ODBC, but I can
> >> see how supporting them could be difficult.
>
> > Maybe you're talking about transaction level stuff. It seems like you
> > should be able to create a table in one statement and use it later on.
> > But if you have auto-commit per statement set on your db handle, then
> > the temp table probably disappears like all other allocated
> > resources.
>
> > So maybe try three statements rolled into a transaction. How you do
> > that depends on the database driver, but this feature is vastly
> > different than executing three, or even two sql statements at one
> > time. Remember that each sql statement has a return code and possibly
> > a result set. Also, a stored procedure is equivalent to a transaction,
> > it either succeeds of fails and can execute multiple sql statements.

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