Re: Running SQL scripts

On 10/30/2014 2:56 PM, Jon Everton wrote:

Create a stored procedure using input parameters with the table and schema
name. Use the SQL function REPLACE to put your variables into the SQL
statement. Send me an email and I'll send you an example if this rather poor
explanation confuses you.

That's clear Jon, thanks. This thread is morphing into a discussion on
integrating Data Studio with RDi. Imagine you have an existing SQLRPGLE
program with a DECLARE CURSOR as below. You'd like to make some changes
to that SQL statement, what does one do?

Rectangle select, copy, tab to Data Studio, paste. Replace the host
variables with literals, test. Edit, test, repeat. When happy with the
new SQL statement, copy in Data Studio, tab to RDi, select, paste.
Adjust formatting. Save, compile, test. Forgot to replace literals,
wonder why program doesn't work. Replace literals with host variables.
Save, compile, test.

It's better than a sharp stick in the eye, but that's a low bar :-)

date: Thu, 30 Oct 2014 14:30:16 -0400
from: Buck Calabro <kc2hiz@xxxxxxxxx>
subject: Re: [WDSCI-L] Running SQL scripts

On 10/30/2014 2:10 PM, Edmund Reinhardt wrote:

There is a small subset of IBM Data Studio which is shipped with RDi.
Now that IBM Data Studio itself is free, you can get the full features
of the product. The SQL running/editing/diagramming is relevant to
IBM i. The query tuning is not since it is geared to LUW DBW (not IBM i).

I am thinking about developing better integration with RDi eventually,
so I am following this thread with interest.

So if there was an easy way to get embedded SQL into an SQL script
editor in IBM Data Studio would this help write, test queries?

Mad utopian dreamer alert!

Given:
select SYSTEM_COLUMN_NAME, DATA_TYPE, "LENGTH", NUMERIC_SCALE,
COLUMN_TEXT, IS_NULLABLE, "CCSID", SYSTEM_TABLE_NAME,
SYSTEM_TABLE_SCHEMA, COLNO
from SYSCOLUMNS
where SYSTEM_TABLE_NAME = :REF_FILE and
SYSTEM_TABLE_SCHEMA = :REF_LIBR;

I would love to have the test/debug tool/IDE be able to 'declare'
variables :REF_FILE and :REF_LIBR as well as populate them with values and
then execute this code as-is. Once my SQL is working properly, I could then
cut / paste it back into my HLL as-is without having to remove literals and
replace them with host variables.

Similar to the ability SDA has to populate fields for testing (STRSDA option
3).