SQL scripts and Result set

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gregor Kovač - 26 Jan 2006 09:13 GMT

Hi!

In my application I'd like to let the user run SQL snippets like:
BEGIN ATOMIC
DECLARE X BIGINT;

SET X = 3;
UPDATE TABLE ....
...
END

Is it possible to get a ResultSet from that?

Best regards,
Kovi

Signature

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

| Gregor Kovac | Gregor.Kovac@mikropis.si |

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| In A World Without Fences Who Needs Gates? |
| Experience Linux. |

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

Reply to this Message

Gert van der Kooij - 26 Jan 2006 09:41 GMT

> Hi!
>
[quoted text clipped - 11 lines]
> Best regards,
> Kovi

If you mean that you want to know which rows were updated then check
the article at
http://www.databasejournal.com/features/db2/article.php/3342211

Reply to this Message

Gregor Kovač - 27 Jan 2006 07:00 GMT

>> Hi!
>>
[quoted text clipped - 15 lines]
> the article at
> http://www.databasejournal.com/features/db2/article.php/3342211

Hi!

Yes, interesting. I do know about those data-change-table-reference stuff,
but that does not help me much, since the statement:
SELECT * FROM FINAL TABLE(
BEGIN ATOMIC
DECLARE X BIGINT;

SET X = 3;
UPDATE TABLE ....
...
END
)

does not work.

Best regards,
Kovi

Signature

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

| Gregor Kovac | Gregor.Kovac@mikropis.si |

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| In A World Without Fences Who Needs Gates? |
| Experience Linux. |

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

Reply to this Message

Serge Rielau - 27 Jan 2006 10:38 GMT

>>>Hi!
>>>
[quoted text clipped - 31 lines]
>
> does not work.

No it doesn't needed to draw a line in the sand somewhere.. :-)
Can you use a stored procedure or an SQL Table function?

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

Reply to this Message

Kovi - 21 Feb 2006 11:35 GMT

> Gregor Kovac wrote:
> >
[quoted text clipped - 33 lines]
> No it doesn't needed to draw a line in the sand somewhere.. :-)
> Can you use a stored procedure or an SQL Table function?

NO, sorry. Since this SQL is dynamically made up in my application and
I don't want to create procedures onm the fly.

Best regards,
Kovi

> Cheers
> Serge
[quoted text clipped - 3 lines]
> DB2 UDB for Linux, Unix, Windows
> IBM Toronto Lab

Reply to this Message

Knut Stolze - 21 Feb 2006 16:42 GMT

>> >>>In my application I'd like to let the user run SQL snippets like:
>> >>>BEGIN ATOMIC
[quoted text clipped - 3 lines]
>> >>> ...
>> >>>END

> NO, sorry. Since this SQL is dynamically made up in my application and
> I don't want to create procedures onm the fly.

What exactly are you doing in the dynamic compound statement? Maybe you can
simply do away with it and use parameter markers or something.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

Gregor Kovač - 22 Feb 2006 08:50 GMT

>>> >>>In my application I'd like to let the user run SQL snippets like:
>>> >>>BEGIN ATOMIC
[quoted text clipped - 9 lines]
> What exactly are you doing in the dynamic compound statement? Maybe you
> can simply do away with it and use parameter markers or something.

The query that is in the application looks like:
BEGIN ATOMIC
DECLARE X BIGINT;

SET X = SELECT FIELD1 FROM TABLE1 WHERE FIELD2 = {GET_VALUE_FROM_INPUT};
UPDATE TABLE2 SET TS = CURRENT TIMESTAMP WHERE ID = X;
END

The marker {GET_VALUE_FROM_INPUT} gets the accual value that the user enters
in the input form. So these SQL has to be preparsed before it can be send
to the database.

Best regards,
Kovi

Signature

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

| Gregor Kovac | Gregor.Kovac@mikropis.si |

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| In A World Without Fences Who Needs Gates? |
| Experience Linux. |

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

Reply to this Message

Serge Rielau - 22 Feb 2006 12:46 GMT

>>>>>>>In my application I'd like to let the user run SQL snippets like:
>>>>>>>BEGIN ATOMIC
[quoted text clipped - 24 lines]
> Best regards,
> Kovi

Will this do?
UPDATE TABLE2 SET TS = CURRENT TIMESTAMP WHERE ID = (SELECT
FIELD1 FROM TABLE1 WHERE FIELD2 = {GET_VALUE_FROM_INPUT});

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Knut Stolze - 22 Feb 2006 15:36 GMT

> UPDATE TABLE2 SET TS = CURRENT TIMESTAMP WHERE ID = (SELECT
> FIELD1 FROM TABLE1 WHERE FIELD2 = {GET_VALUE_FROM_INPUT});

And then the SELECT from the UPDATE wouldn't be an issue anymore...

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

Serge Rielau - 22 Feb 2006 15:42 GMT

>> UPDATE TABLE2 SET TS = CURRENT TIMESTAMP WHERE ID = (SELECT
>>FIELD1 FROM TABLE1 WHERE FIELD2 = {GET_VALUE_FROM_INPUT});
>
> And then the SELECT from the UPDATE wouldn't be an issue anymore...

Oh, he wants to get the id back? No problem!
SELECT id FROM OLD TABLE(UPDATE TABLE2 SET TS = CURRENT TIMESTAMP WHERE
ID = (SELECT FIELD1 FROM TABLE1 WHERE FIELD2 = {GET_VALUE_FROM_INPUT}));

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Gregor Kovač - 24 Feb 2006 21:37 GMT

>>> UPDATE TABLE2 SET TS = CURRENT TIMESTAMP WHERE ID = (SELECT
>>>FIELD1 FROM TABLE1 WHERE FIELD2 = {GET_VALUE_FROM_INPUT});
[quoted text clipped - 8 lines]
> Serge
:) This is all good and OK, but like thid you loose the flexibility of SQL

DECLARE's, SET's and alike that you can use in SQL procedures.

Best regards,
Kovi

Signature

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

| Gregor Kovac | Gregor.Kovac@mikropis.si |

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| In A World Without Fences Who Needs Gates? |
| Experience Linux. |

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

Reply to this Message

Serge Rielau - 24 Feb 2006 21:47 GMT

>>>> UPDATE TABLE2 SET TS = CURRENT TIMESTAMP WHERE ID = (SELECT
>>>>FIELD1 FROM TABLE1 WHERE FIELD2 = {GET_VALUE_FROM_INPUT});
[quoted text clipped - 10 lines]
> :) This is all good and OK, but like thid you loose the flexibility of SQL
> DECLARE's, SET's and alike that you can use in SQL procedures.

What's it that you want to achieve?
The feature described above is more efficient than procedural logic.
Of course it's not the same and it isn't meant to be.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Gregor Kovač - 27 Feb 2006 16:58 GMT

>>>>> UPDATE TABLE2 SET TS = CURRENT TIMESTAMP WHERE ID = (SELECT
>>>>>FIELD1 FROM TABLE1 WHERE FIELD2 = {GET_VALUE_FROM_INPUT});
[quoted text clipped - 17 lines]
> Cheers
> Serge

What I want to achieve is this:
We have an application that has its input forms stored in a database. We
also have a database replication that is automatic to transfer those input
forms from our development database to our test database, but there is no
automatic replication of functions, stored procedures, database
structure, ...
So it is faster and more "under control" for me to specify a BEGIN ATOMIC
block on that input form than to create a procedure and distribute it on
two, three, ... databases. And this BEGIN ATOMIC block can also be edited
faster than stored procedures, functions, ...
And the most important thing is that in BEGIM ATOMIC block you HAVE the
flexibility of a plain stored procedure, meaning you can use DECLARE, FOR,
WHILE, ...

Best regards,
Kovi

Signature

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

| Gregor Kovac | Gregor.Kovac@mikropis.si |

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| In A World Without Fences Who Needs Gates? |
| Experience Linux. |

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

Reply to this Message

Brian Tkatch - 21 Feb 2006 15:38 GMT

I did not know of this clause (the documentation on the internal
network here is not updated). Thanx for posting it.

B.

Reply to this Message