Hi to all,
I want to execute sql statement or database procedure before DS job run. How can i do this.....May be my question is very simple.....but i dont know.
Thanks to all.
ITsMe
How to execute database procedure before job run?
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
What is the database you are trying to run the procedure in? Also, if the job you are trying to run after executing the procedure has a database stage in it, you can as well call the procedure from the before sql tab itself.When do you intend to call this procedure, before start of every job?
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
Here is a routine to execute an SQL command via an ODBC connection.
Warning : No guarantees that it will work!
You need to tailor this routine to your database and table specifics and use the help of universe odbc guide to make sense of this routine code.
To find universe odbc guide, use google search.
HTH
Thanks
Code: Select all
$INCLUDE UNIVERSE.INCLUDE ODBC.H
*** INITIALIZE VARIABLES
NUM.COLS = 0
RSTATUS = 0
* DSN = ""
*UID = ""
*PWD = ""
STMT = "drop table soi_wl_table"
Ans = 0
* Check and see if user passed command line args
GET(ARG.)DSN
GET(ARG.)UID
GET(ARG.)PWD
GOSUB ALLOCENV
GOSUB ALLOCCONNECTENV
GOSUB DBCONNECT
GOSUB ALLOCSTMTENV
GOSUB EXECUTESQL
*GOSUB CHECKCOLUMNS
*GOSUB BINDCOLUMNS
*GOSUB FETCHDATA
GOSUB DESTROYOBJECTS
ALLOCENV:
*** ALLOCATE ENVIRONMENT
Fn = "SQLAllocEnv"
IF SQLAllocEnv(henv) # SQL.SUCCESS THEN STOP "Unable to allocate SQL Environment"
RETURN (Ans)
ALLOCCONNECTENV:
*** ALLOCATE CONNECTION ENVIRONMENT
Fn = "SQLAllocConnect"
IF SQLAllocConnect(henv,hdbc) # SQL.SUCCESS THEN STOP "Unable to allocate connection environment"
RETURN (Ans)
DBCONNECT:
*** CONNECT TO DATASOURCE (ODBC)
Fn = "SQLConnect"
IF TRIM(DSN) = "" THEN STOP "Missing DSN"
IF TRIM(UID) = "" THEN STOP "Missing User ID"
IF TRIM(PWD) = "" THEN STOP "Missing User Password"
RSTATUS = SQLConnect(hdbc,DSN,UID,PWD)
*** CHECK RETURN STATUS
IF RSTATUS # SQL.SUCCESS AND RSTATUS # SQL.SUCCESS.WITH.INFO THEN
ESTAT = SQLError(-1,hdbc,-1,SQLSTATE,DBMSCODE,ETEXT)
PRINT "ERROR TEXT ":ETEXT
PRINT "ERROR STATUS ":ESTAT
STOP
END
RETURN (Ans)
ALLOCSTMTENV:
*** NOW ALLOCATE STATEMENT ENVIRONMENT
Fn = "SQLAllocStmt"
IF SQLAllocStmt(hdbc,hstmt) # SQL.SUCCESS THEN STOP "Unable to allocate statement environment"
RETURN (Ans)
EXECUTESQL:
Fn = "SQLExecDirect"
RSTATUS = SQLExecDirect(hstmt,STMT)
IF RSTATUS # SQL.SUCCESS AND RSTATUS # SQL.SUCCESS.WITH.INFO THEN
PRINT "Error while executing SQL statement ":STMT
STOP
END
RETURN (RSTATUS)
CHECKCOLUMNS:
*
* Now find out if any columns were produced.
*
FUNC = "SQLNumResultCols"
RSTATUS = SQLNumResultCols(hstmt, NUM.COLS)
IF RSTATUS # SQL.SUCCESS THEN STOP "Error checking for columns"
RETURN (Ans)
BINDCOLUMNS:
FUNC = "SQLBindCol"
FOR ICOL = 1 to NUM.COLS
RSTATUS = SQLBindCol(hstmt,ICOL,SQL.B.DEFAULT,MAX.RET)
IF RSTATUS # SQL.SUCCESS THEN STOP "Error binding columns"
NEXT ICOL
RETURN (Ans)
FETCHDATA:
FUNC = "SQLFetch"
LOOP
STATUS = SQLFetch(hstmt)
WHILE STATUS <> SQL.NO.DATA.FOUND DO
PRINT MAX.RET
REPEAT
RETURN (Ans)
DESTROYOBJECTS:
*** DESTROY STATEMENT ENVIRONMENT
RSTATUS = SQLFreeStmt(henv,SQL.UNBIND)
*** DESTROY CONNECTION ENVIRONMENT
Fn = "SQLDisconnect"
RSTATUS = SQLDisconnect(hdbc)
IF RSTATUS # SQL.SUCCESS THEN STOP "Unable to destroy SQL connection environment"
RETURN (Ans)
Warning : No guarantees that it will work!
You need to tailor this routine to your database and table specifics and use the help of universe odbc guide to make sense of this routine code.
To find universe odbc guide, use google search.
HTH
Thanks