datastage: job parameter read from table in database

[XianII]

Hi, I'm trying to read a date from one table and use that date as a parameter on another job. How can I do that?

I've trying with batch jobs, but i don't know how to launch a sql query from code.

Thanks in advance.
Xian

 

[wdeny]

Here's the code:

Funcion: ExecSQL(OraHost, OraUser, OraPassword, SQL)
Note: Although built for Oracle, works with ANY ODBC Data Source.

**********************************************************
#include DSINCLUDE ODBC.H

Common /execsql/ result, rowcount, info

rowlimit = 0
fcname = "ExecSQL"
result = ""

err = SQLAllocEnv(env1)

if err <> SQL.SUCCESS then
Call DSLogFatal(&quot;Erro na alocação de ENVIRONMENT.&quot;, fcname)
return(-1)

end

err = SQLAllocConnect(env1, conn1)

if err <> SQL.SUCCESS then
status = SQLFreeEnv(env1)
Call DSLogFatal(&quot;Erro na alocação de CONNECTION.&quot;, fcname)
return(-1)

end

err = SQLConnect(conn1, OraHost, OraUser, OraPassword)

if err <> SQL.SUCCESS then
status = SQLFreeConnect(conn1)
status = SQLFreeEnv(env1)
Call DSLogFatal(&quot;Erro na conexão com &quot;:OraHost, fcname)
return(-1)

end

err = SQLAllocStmt(conn1, stmt1)

if err <> SQL.SUCCESS then
status = SQLDisconnect(conn1)
status = SQLFreeConnect(conn1)
status = SQLFreeEnv(env1)
Call DSLogFatal(&quot;Erro na alocação do comando '&quot;:SQL:&quot;' em &quot;:OraHost, fcname)
return(-1)

end

err = SQLExecDirect(stmt1, SQL)
status = SQLError(env1, conn1, stmt1, sqlstate, dbmscode, errmsg)
info = &quot;SQL State: &quot;:sqlstateFM:&quot;DBMS Code: &quot;:dbmscodeFM:errmsg

if err <> SQL.SUCCESS then
status = SQLFreeStmt(stmt1, SQL.DROP)
status = SQLDisconnect(conn1)
status = SQLFreeConnect(conn1)
status = SQLFreeEnv(env1)
Call DSLogFatal(&quot;Erro na execução do comando [&quot;:SQL:&quot;] em &quot;:OraHost:&quot;:&quot;FM:info, fcname)
return(-1)

end

err = SQLNumResultCols(stmt1, numcols)

if err <> SQL.SUCCESS then
status = SQLFreeStmt(stmt1, SQL.DROP)
status = SQLDisconnect(conn1)
status = SQLFreeConnect(conn1)
status = SQLFreeEnv(env1)
Call DSLogFatal(&quot;Erro na execução do comando [&quot;:SQL:&quot;] em &quot;:OraHost:&quot;, comando SQLNumResultCols&quot;, fcname)
return(-1)

end

err = SQLRowCount(stmt1, rowcount)

if err <> SQL.SUCCESS then
status = SQLFreeStmt(stmt1, SQL.DROP)
status = SQLDisconnect(conn1)
status = SQLFreeConnect(conn1)
status = SQLFreeEnv(env1)
Call DSLogFatal(&quot;Erro na execução do comando [&quot;:SQL:&quot;] em &quot;:OraHost:&quot;, comando SQLRowCount&quot;, fcname)
return(-1)

end

if rowcount < 0 then rowcount = 0

if numcols = 0 then
status = SQLFreeStmt(stmt1, SQL.DROP)
status = SQLDisconnect(conn1)
status = SQLFreeConnect(conn1)
status = SQLFreeEnv(env1)
return(SQL.SUCCESS)

end

dimension line(numcols)

for i = 1 to numcols
status = SQLBindCol(stmt1, i, SQL.B.DEFAULT, line(i))

next

rownum = 0

loop while @True
err = SQLFetch(stmt1)

if err = 100 then exit

if err <> SQL.SUCCESS then
err = SQLError(env1, conn1, stmt1, sqlstate, dbmscode, errmsg)
status = SQLFreeStmt(stmt1, SQL.DROP)
status = SQLDisconnect(conn1)
status = SQLFreeConnect(conn1)
status = SQLFreeEnv(env1)
Call DSLogFatal(&quot;Erro na execução do comando [&quot;:SQL:&quot;] em &quot;:OraHost:&quot;, comando SQLFetch:&quot;FM:&quot;SQL State: &quot;:sqlstateFM:&quot;DBMS Code: &quot;:dbmscodeFM:errmsg, fcname)
return(-1)

end

if result <> &quot;&quot; then result = resultFM

for i = 1 to numcols
result = result:line(i)
if i < numcols then result = result:&quot;,&quot;

next

rownum += 1
if rownum > rowlimit and rowlimit > 0 then exit

repeat

Ans = SQL.SUCCESS

status = SQLFreeStmt(stmt1, SQL.DROP)
status = SQLDisconnect(conn1)
status = SQLFreeConnect(conn1)
status = SQLFreeEnv(env1)
**********************************************************

Cheers,
Deny