Page 1 of 1
Executing db2 sql command in a routine
Posted: Wed Jun 20, 2007 5:05 am
by rafik2k
I have created one simple sever routine as below, where I am connecting to database and trying to insert one record into a table.
There is no compilation error and while testing it returns 0, but record is not inserting into table.
Could you please let me know where I am going wrong? and what is solution on this?
Code: Select all
DB2Cmd="connect to sample user test using test;insert into db2admin.add(name,phone) values('ABC',111);"
Call DSExecute("TCL", DB2Cmd, ResultText, SystemReturnCode)
If SystemReturnCode = 0 Then
Ans = 0
Return(Ans)
End
If SystemReturnCode <> 0 Then
Ans = 1
Return(Ans)
End
Thanks in advance.
Posted: Wed Jun 20, 2007 5:56 am
by ray.wurlod
Don't you need a quit in DB2Cmd ?
Posted: Wed Jun 20, 2007 6:49 am
by chulett
Umm... you need to run it from 'DOS' rather than 'TCL'.
Posted: Wed Jun 20, 2007 7:19 am
by rafik2k
chulett wrote:Umm... you need to run it from 'DOS' rather than 'TCL'. ...
Thanks Craig for input,
even I tried using DOS, I didn't get compilation error, but it returns 1
Don't you need a quit in DB2Cmd ?
Ray: Do you mean
connect reset command?
Please advice where I am going wrong.
Posted: Wed Jun 20, 2007 7:25 am
by chulett
Does your syntax work from the command line? Call DSLogInfo() with your ResultText to see what's going on...
Posted: Sat Jun 23, 2007 3:27 pm
by rafik2k
I have modified the code and tried to run, but the job aborted sayting
Job stopped - before-job routine returned error: Error variable unassigned on return from BEFORE routine DSU.tempSql
Code: Select all
DB2Cmd1="db2cmd;db2 connect to sample user test using test; db2 insert into db2admin.add(name,phone) values('ABC',111); db2 connect reset"
Call DSExecute("DOS", DB2Cmd1, ResultText, SystemReturnCode)
Call DSLogInfo("msg " : ResultText, "temp")
If SystemReturnCode = 0 Then
Ans = 0
End
If SystemReturnCode <> 0 Then
Ans = 1
End
and routine doesnot give any log information.
But the following code runs perferctly using batch file.
Could you please let me know what is the issue for above code?
Code: Select all
db2 connect to sample user test using test
db2 insert into db2admin.add(name,phone) values('ABC',111)
db2 connect reset
Posted: Sat Jun 23, 2007 5:06 pm
by chulett
"Error variable unassigned on return from BEFORE routine"
Your problem in a nutshell. A
Before/After Subroutine is
not equivalent to a
Transform Function like you would use in a Transformer derivation. And which you seem to be trying to write.
A Transform Function can have multiple Arg values and returns a single Ans variable. Before/After subroutines take an InputArg and return an ErrorCode. The default code when you start one looks like this:
Code: Select all
ErrorCode = 0 ;* set this to non-zero to stop the stage/job
You seemed to have removed this and replaced it with the 'Ans' that a Transform Function would expect.
You'll need to correct this.
Posted: Tue Jun 26, 2007 12:46 am
by rafik2k
Thanks Craig!
I added your suggestion, this time job runs fine, but SystemReturnCode deos not return 0 and record is not inserting.
Moreover ResultText doesnot retun any information.
Code: Select all
DB2Cmd1="db2cmd;db2 connect to sample user test using test; db2 insert into db2admin.add(name,phone) values('ABC',111); db2 connect reset"
Call DSExecute("DOS", DB2Cmd1, ResultText, SystemReturnCode)
If SystemReturnCode = 0 Then
Call DSLogInfo("msg insert success " : ResultText, "temp")
End
If SystemReturnCode <> 0 Then
Call DSLogInfo("msg insert error " : ResultText, "temp")
End
** Free allocated ressources
ErrorCode = 0 ;
Kindly let me know if anyone of you have any suggestion.
Thanks
Posted: Sat Jun 30, 2007 7:35 am
by rafik2k
I searched in archive and the following code executes db2 sql perferctly.
Still i am wondering why my previous code does not work!
Any help or suggestion on this will be greatly appreciated
Thanks in advance
Code: Select all
$INCLUDE UNIVERSE.INCLUDE ODBC.H
** Initialise parameters ODBC
henv = SQL.NULL.HENV
hConn = SQL.NULL.HDBC
hstmt = SQL.NULL.HSTMT
password = Field(InputArg,"|",3)
userName = Field(InputArg,"|",2)
dataSource = Field(InputArg,"|",1)
Call DSLogInfo("password " : password, "ExecSql")
Call DSLogInfo("userName " : userName, "ExecSql")
Call DSLogInfo("dataSource " : dataSource, "ExecSql")
** Connect to DataSource
status = SQLAllocEnv(hEnv)
status = SQLAllocConnect(hEnv, hConn)
status = SQLConnect(hConn, dataSource, userName , password )
If status = SQL.ERROR
Then
Call DSLogInfo("Connection KO " , "ExecSql")
ErrorCode = -1 ;
Goto Finally
End Else
Call DSLogInfo("Connection OK " , "ExecSql")
End
** Execution request (here, my Sql Statement )
status = SQLAllocStmt(hConn, hStmt)
Stmt= "insert into db2admin.add values('Munir',888);"
Call DSLogInfo("request SQL: " : Stmt, "ExecSql")
status = SQLExecDirect(hStmt, Stmt)
If status<>SQL.SUCCESS AND status<>SQL.SUCCESS.WITH.INFO
Then
Call DSLogInfo("Request KO ", "ExecSql")
ErrorCode = -1 ;
Goto Finally
End Else
Call DSLogInfo("Request OK ", "ExecSql")
End
** Free allocated ressources
ErrorCode = 0 ;
Finally:
status = SQLFreeStmt(hStmt, SQL.DROP)
status = SQLDisconnect(hConn)
status = SQLFreeConnect(hConn)
status = SQLFreeEnv(hEnv)
Posted: Sat Jun 30, 2007 7:53 am
by ray.wurlod
The latter code, with the SQL... functions, is leveraging the BASIC SQL Client Interface.
In your previous post, is there anything in ResultText when SystemReturnCode is non-zero?
What you're doing in this example is creating a virtual BAT file - SystemReturnCode captures its exit status and ResultText captures its output.
Posted: Sat Jun 30, 2007 3:45 pm
by rafik2k
Thanks Ray and Craig for your input!
I would appreciate if somebody helps me to find solution of my original post
Posted: Sat Jun 30, 2007 11:37 pm
by ray.wurlod
And we would appreciate it if you were to post what you get in ResultText, as requested.