conncting a database from a routine
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 107
- Joined: Wed Sep 29, 2004 10:15 am
conncting a database from a routine
Hi All
Can you share some idea on how to connect to a database from a routine. I had a requirement that i had to write the return value of a routine to a database table.
Thanks
RK
Can you share some idea on how to connect to a database from a routine. I had a requirement that i had to write the return value of a routine to a database table.
Thanks
RK
"Routine" = what here? Before/After/Transform BASIC routine? External C++ routine?
Short answer is don't do it. Use 'normal' mechanisms to do that, i.e. a job that takes that result as a parameter and does the db insert. A Sequence job would make the linkage trivial.
Long answer is search the forums for "BCI". Kind of like embarking on a new career, but some people here seem to like going down that path. For me, the words "10 foot" and "pole" come to mind.
Short answer is don't do it. Use 'normal' mechanisms to do that, i.e. a job that takes that result as a parameter and does the db insert. A Sequence job would make the linkage trivial.
Long answer is search the forums for "BCI". Kind of like embarking on a new career, but some people here seem to like going down that path. For me, the words "10 foot" and "pole" come to mind.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 107
- Joined: Wed Sep 29, 2004 10:15 am
-
- Participant
- Posts: 107
- Joined: Wed Sep 29, 2004 10:15 am
Return codes from a routine used in a before/after job/stage call aren't easily accessible. If you're calling from a Sequence a DS BASIC Routine you have access to the return code easily and could pass that to the next stage which is a Command stage and uses the command line interface to talk to a database.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Premium Member
- Posts: 62
- Joined: Wed Dec 08, 2004 2:26 am
Hi
Is it possible to update a database table from a basic transform routine.
Thanks in advance
sasi
Is it possible to update a database table from a basic transform routine.
Thanks in advance
sasi
Last edited by sasidhar_kari on Thu Apr 16, 2009 8:47 am, edited 1 time in total.
-
- Premium Member
- Posts: 62
- Joined: Wed Dec 08, 2004 2:26 am
OK, I'll throw you a BCI bone since you seem to be having search issues.
An example of the syntax here. Regarding documentation here. More examples here and here. Lastly, many of the posts mention the reasons why this is a less than ideal approach, like this one for example... many more exist. You've got a GUI tool, so use the GUI rather than take this approach, which (IMHO) just adds a completely unnecessary layer of obfuscation and complexity to your processing.
All this from a simple Exact Search of "BCI" and looking through the first couple of pages from the 154 matches it found.
An example of the syntax here. Regarding documentation here. More examples here and here. Lastly, many of the posts mention the reasons why this is a less than ideal approach, like this one for example... many more exist. You've got a GUI tool, so use the GUI rather than take this approach, which (IMHO) just adds a completely unnecessary layer of obfuscation and complexity to your processing.
All this from a simple Exact Search of "BCI" and looking through the first couple of pages from the 154 matches it found.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 62
- Joined: Wed Dec 08, 2004 2:26 am
Hi
I can understand the experts feeling that its a stupid requirement but the situation is like that i had to provide a solution in such a manner. Thanks for your valuable advises and thoughts.
With the inputs available in the forum I am able to write up the routine but it is compiled with zero errors but the return value is 1 which states that the SQL.Success is false
Can somebody throw me an idea on this
The routine has 3 parameters (datasource,Uid,PWD)
Code :
$INCLUDE UNIVERSE.INCLUDE ODBC.H
hEnv = SQL.NULL.HENV
hDBC = SQL.NULL.HDBC
hStmt = SQL.NULL.HSTMT
Ans = 0
ErrCode = SQLAllocEnv(hEnv)
ErrCode = SQLAllocConnect(hEnv, hDBC)
ErrCode = SQLConnect(hDBC, DataSource, UserID, Pwd)
SQLStmt = "update copy.sampl set Idvalue=3000 where ID=1"
If Right(TrimB(SQLStmt),1) <> ";" Then SQLStmt := ";"
ErrCode = SQLExecDirect(hStmt, SQLStmt)
If ErrCode = SQL.SUCCESS Then Ans= 0
Else
Ans= 1
END ;
ErrCode = SQLFreeStmt(hStmt, SQL.DROP)
ErrCode = SQLDisconnect(hDBC)
ErrCode = SQLFreeConnect(hDBC)
ErrCode = SQLFreeEnv(hEnv)
-----------------
Thanks
Sasi
I can understand the experts feeling that its a stupid requirement but the situation is like that i had to provide a solution in such a manner. Thanks for your valuable advises and thoughts.
With the inputs available in the forum I am able to write up the routine but it is compiled with zero errors but the return value is 1 which states that the SQL.Success is false
Can somebody throw me an idea on this
The routine has 3 parameters (datasource,Uid,PWD)
Code :
$INCLUDE UNIVERSE.INCLUDE ODBC.H
hEnv = SQL.NULL.HENV
hDBC = SQL.NULL.HDBC
hStmt = SQL.NULL.HSTMT
Ans = 0
ErrCode = SQLAllocEnv(hEnv)
ErrCode = SQLAllocConnect(hEnv, hDBC)
ErrCode = SQLConnect(hDBC, DataSource, UserID, Pwd)
SQLStmt = "update copy.sampl set Idvalue=3000 where ID=1"
If Right(TrimB(SQLStmt),1) <> ";" Then SQLStmt := ";"
ErrCode = SQLExecDirect(hStmt, SQLStmt)
If ErrCode = SQL.SUCCESS Then Ans= 0
Else
Ans= 1
END ;
ErrCode = SQLFreeStmt(hStmt, SQL.DROP)
ErrCode = SQLDisconnect(hDBC)
ErrCode = SQLFreeConnect(hDBC)
ErrCode = SQLFreeEnv(hEnv)
-----------------
Thanks
Sasi