SQL scripts and Result set

[Gregor Kovaè]

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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

[Gert van der Kooij]

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

[Gregor Kovaè]

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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

[Serge Rielau]

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

[Kovi]

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

[Brian Tkatch]

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

B.

[Knut Stolze]

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

[Gregor Kovaè]

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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

[Serge Rielau]

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

[Knut Stolze]

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

[Serge Rielau]

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

[Gregor Kovaè]

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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

[Serge Rielau]

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

[Gregor Kovaè]

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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~