470,614 Members | 1,396 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,614 developers. It's quick & easy.

SQL scripts and Result set

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

--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
| Gregor Kovac | Gr**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
Jan 26 '06 #1
13 1372
In article <op*****************@news.siol.net>, Gregor Kovac
(gr**********@mikropis.si) says...
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


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

Jan 26 '06 #2
Gert van der Kooij wrote:
In article <op*****************@news.siol.net>, Gregor Kovac
(gr**********@mikropis.si) says...
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


If you mean that you want to know which rows were updated then check
the article at
http://www.databasejournal.com/featu...le.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
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
| Gregor Kovac | Gr**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
Jan 27 '06 #3
Gregor Kovańć wrote:
Gert van der Kooij wrote:

In article <op*****************@news.siol.net>, Gregor Kovac
(gr**********@mikropis.si) says...
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


If you mean that you want to know which rows were updated then check
the article at
http://www.databasejournal.com/featu...le.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.

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
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab
Jan 27 '06 #4

Serge Rielau wrote:
Gregor Kovac wrote:
Gert van der Kooij wrote:

In article <op*****************@news.siol.net>, Gregor Kovac
(gr**********@mikropis.si) says...

Hi!

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

SET X >>> UPDATE TABLE ....
...
END

Is it possible to get a ResultSet from that?

Best regards,
Kovi

If you mean that you want to know which rows were updated then check
the article at
http://www.databasejournal.com/featu...le.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 > UPDATE TABLE ....
...
END
)

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?


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
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab


Feb 21 '06 #5
I did not know of this clause (the documentation on the internal
network here is not updated). Thanx for posting it.

B.

Feb 21 '06 #6
Kovi wrote:
>>>In my application I'd like to let the user run SQL snippets like:
>>>BEGIN ATOMIC
>>> DECLARE X BIGINT;
>>>
>>> SET X >>> UPDATE TABLE ....
>>> ...
>>>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.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Feb 21 '06 #7
Knut Stolze wrote:
Kovi wrote:
>>>In my application I'd like to let the user run SQL snippets like:
>>>BEGIN ATOMIC
>>> DECLARE X BIGINT;
>>>
>>> SET X >>> UPDATE TABLE ....
>>> ...
>>>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.

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
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
| Gregor Kovac | Gr**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
Feb 22 '06 #8
Gregor Kovańć wrote:
Knut Stolze wrote:

Kovi wrote:

>>>In my application I'd like to let the user run SQL snippets like:
>>>BEGIN ATOMIC
>>> DECLARE X BIGINT;
>>>
>>> SET X >>> UPDATE TABLE ....
>>> ...
>>>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.


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

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

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Feb 22 '06 #9
Serge Rielau wrote:
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...

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Feb 22 '06 #10
Knut Stolze wrote:
Serge Rielau wrote:

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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Feb 22 '06 #11
Serge Rielau wrote:
Knut Stolze wrote:
Serge Rielau wrote:

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

:) 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
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
| Gregor Kovac | Gr**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
Feb 24 '06 #12
Gregor Kovańć wrote:
Serge Rielau wrote:

Knut Stolze wrote:
Serge Rielau wrote:

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


:) 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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Feb 24 '06 #13
Serge Rielau wrote:
Gregor KovaŤ wrote:
Serge Rielau wrote:

Knut Stolze wrote:

Serge Rielau wrote:

> 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


:) 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

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
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
| Gregor Kovac | Gr**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
Feb 27 '06 #14

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by Florian Lindner | last post: by
4 posts views Thread by Florian Lindner | last post: by
1 post views Thread by George van den Driessche | last post: by
3 posts views Thread by Unregistered | last post: by
8 posts views Thread by Leszek | last post: by
7 posts views Thread by chakkaradeepcc | last post: by
11 posts views Thread by cwhite | last post: by
3 posts views Thread by Michel Bouwmans | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.