Executing SQL in routine

[mousazzi]

Hi All.
Does anyone know if is possibile to execute SQL statement inside an Before/After routine and which is the correct code ??
I've tried to insert the SQL statement but (Select ... From ... or Update...) but the compiler raise me an error.

Thanks in advance.

[JoshGeorge]

Try This (Without a doubt below code needs modificatins, so take that into consideration. This can be just a format for you):

*Input Arguments - selectStatement, DSN, UID, PWD

$INCLUDE DSINCLUDE JOBCONTROL.H
$INCLUDE UNIVERSE.INCLUDE ODBC.H

henv = SQL.NULL.HENV
hConn = SQL.NULL.HDBC
hstmt = SQL.NULL.HSTMT

Ans = -1

ErrCode = SQLAllocEnv(hEnv)
ErrCode = SQLAllocConnect(hEnv, hConn)
ErrCode = SQLConnect(hConn, DSN, UID, PWD)

If ErrCode = SQL.ERROR Then
Call DSLogInfo("Connection Fail " , "ExecSQL")
GoTo ErrProcess
End
Call DSLogInfo("Connection Success " , "ExecSQL")

Stmt = '"':selectStatement:';"'
ErrCode = SQLAllocStmt(hConn, hStmt)
ErrCode = SQLBindCol(hStmt, 1, SQL.B.DEFAULT, res)
ErrCode = SQLPrepare(hStmt, Stmt)
ErrCode = SQLExecDirect(hStmt,Stmt)

status =SQLError(hEnv,hConn,hStmt,SQLState,DBMSCode,ErrText)
Call DSLogInfo("Status of SQL":SQLError(hEnv,hConn,hStmt,SQLState,DBMSCode,ErrText), "SQLstatus")

If ErrCode = SQL.ERROR Then
Call DSLogInfo("SQL Failed ":ErrCode , "ExecSQL")
GoTo ErrProcess
End

Call DSLogInfo("SQL Success " , "ExecSQL")
ErrCode = SQLFetch(hStmt)
Call DSLogInfo(ErrCode , "ExecSQL")
Ans = 1

ErrProcess:

FinalStatus = SQLFreeStmt(hStmt, SQL.CLOSE)
FinalStatus = SQLFreeStmt(hStmt, SQL.DROP)
FinalStatus = SQLDisconnect(hConn)
FinalStatus = SQLFreeConnect(hConn)
FinalStatus = SQLFreeEnv(hEnv)

[Nisusmage]

I don't think it's possible.
However, I've just learnt how to create and execute a SQL statement using a server routine.

got check out this post: viewtopic.php?t=109521
That's what set me on the right track..


1) Create a new "server routine"
set it's type to "before/after subroutine"
and give a name like "ExecSQLProc"

This code parses a string and pulls out the parms, you can add another one there for the SQL string, if you like.

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, "ExecSQLProc")
Call DSLogInfo("userName  " : userName, "ExecSQLProc")
Call DSLogInfo("dataSource " : dataSource, "ExecSQLProc")

** 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 " , "ExecSQLProc")
         ErrorCode = -1 ; 
         Goto Finally
      End Else
         Call DSLogInfo("Connection OK " , "ExecSQLProc")
      End


** Execution request (here, my stored procedure )
      status = SQLAllocStmt(hConn, hStmt)
      Stmt= "exec DBA.spdl_AccountTransaction_Clear"
      Call DSLogInfo("request SQL: " : Stmt, "ExecSQLProc")
      status = SQLExecDirect(hStmt, Stmt)

      If status<>SQL.SUCCESS AND status<>SQL.SUCCESS.WITH.INFO
      Then
         Call DSLogInfo("Request KO ", "ExecSQLProc")
         ErrorCode = -1 ; 
         Goto Finally
      End Else
         Call DSLogInfo("Request OK ", "ExecSQLProc")
      End

** Free allocated ressources
      ErrorCode = 0 ; 
Finally:
      status = SQLFreeStmt(hStmt, SQL.DROP)
      status = SQLDisconnect(hConn)
      status = SQLFreeConnect(hConn)
      status = SQLFreeEnv(hEnv)

Save and Compile, then Close.

There's a lot of room here to create much better error handling, and it's prob needed.

2) On your Stage, you'll see the server routine on the drop down, select it.
3) input Value: dsn001|user01|password1

[Nisusmage]

:D *Jinx*

You owe me a Coke!

If you are using "Before/After sub" you cannot use multiple parameters, that's why I had to use the string parser.

Use which ever one suits your needs.

[mousazzi]

Both of the code seems good but I need to connect to Oracle Database not using ODBC: is there a to connect to the Oracle table without it ??

In every case thanks!!

[chulett]

I would heavily question your 'need' to do this. Why not a job if you need to do Oracle work? It is easy enough for it to be a 'pre-' or post-' job or even to do this inside your current job. I've been doing this a long time and have never had a need to fall back on this ODBC 'technique'.

If this works for you, fine. If you'd like to discuss an alternative, start by explaining why you asked your question. Put some context around it, let us know what problem you are trying to solve, don't limit this discussion to the methodology you believe is the way to solve it. That let's people offer alternative approaches rather than just answering your question as asked.

[mousazzi]

I would heavily question your 'need' to do this. Why not a job if you need to do Oracle work? It is easy enough for it to be a 'pre-' or post-' job or even to do this inside your current job. I've been doing this a long time and have never had a need to fall back on this ODBC 'technique'.

If this works for you, fine. If you'd like to discuss an alternative, start by explaining why you asked your question. Put some context around it, let us know what problem you are trying to solve, don't limit this discussion to the methodology you believe is the way to solve it. That let's people offer alternative approaches rather than just answering your question as asked.

I try to explain my problem.
In a sequence job I call a server and I'd like to insert in an Oracle DB a set of information :job name -> Sequencer, Step name -> Server, Start and end time, number of row (selected, updated, inserted) and eventually Oracle error. I try to do this by an SQL statement put in after SQL tab in the OCI stage but I didn't resolve the problem. I also try to pass to a new sequence job, all the parameter I need to insert in the Oracle table but the result is the same.
In other and easy term, I need to create a log in a Oracle table at runtime withot using procedure.
Hope this could better explain my problem.

[DSguru2B]

Even if the routine approach works for you now, it might not in 30 days, as it will expire. DataDirect does not allow usage of its drivers outside of the database stage. You might want to take that into consideration.

[Nisusmage]

I couldn't find a better way of doing this.

My problem is that I land the data from a source, using a Link Partitioner, into 3 different Seq files. From there I use 3 different IQ 12 Bulk load stages to bulk load the 3 different seq files (see below).

The problem there is the parallism, how do you clear the table once before you bulk load 3 different files into 1 table.

Code: Select all

Progress 
    -->LPSplit(3) (Server routine calls a StoredProc to clear table)
        --link1--> Seqfile01--> IQ12Load01 (bulk)
        --link2--> Seqfile02--> IQ12Load02 (bulk)
        --link3--> Seqfile03--> IQ12Load03 (bulk)