Executing db2 sql command in a routine

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

[ray.wurlod]

Don't you need a quit in DB2Cmd ?

[chulett]

Umm... you need to run it from 'DOS' rather than 'TCL'.

[rafik2k]

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.

[chulett]

Does your syntax work from the command line? Call DSLogInfo() with your ResultText to see what's going on...

[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

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

[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

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

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

[rafik2k]

Thanks Ray and Craig for your input!
I would appreciate if somebody helps me to find solution of my original post

[ray.wurlod]

And we would appreciate it if you were to post what you get in ResultText, as requested.