Executing SQL in routine
Moderators: chulett, rschirm, roy
Executing SQL in routine
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.
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.
-
- Participant
- Posts: 612
- Joined: Thu May 03, 2007 4:59 am
- Location: Melbourne
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)
*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)
Joshy George
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
Re: Executing SQL in routine
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.
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
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)
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
Last edited by Nisusmage on Mon May 07, 2007 5:53 am, edited 2 times in total.
Re: Executing SQL in routine
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!!
In every case thanks!!
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
I try to explain my problem.chulett wrote: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.
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.
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
it's all about the parallel.
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.
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)