DSXchange

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

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

Hi
Thanks for your response. I want to update a table with the return value of a Transform routine. Can you share the syntax to connect to a database , for examle sql server through odbc connection

Thanks
RK

A "transform routine" is written in BASIC and called in a PX job's BASIC Transformer derivation. From there, just let the job write it to the database.

Hi
Thanks for your valuable suggestion. I had done this and now i want to learn how to connect to a database from a routine. Can you please share the idea.

Thanks
RK

Already did.

Here's a freebie and all I'm going to say on the specific subject of db connection from a routine - search here for "BCI" and then clear your calendar.

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.

Hi
Is it possible to update a database table from a basic transform routine.

Thanks in advance

sasi

Asked and answered.

Hi
I had reframed my statement. Can any one clarify the issue

Regards
Sasi

Same question with the same answer... "yes". And all the ways to do that are listed here.

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.

sasidhar_kari wrote:Hi
I had reframed my statement. Can any one clarify the issue

Regards
Sasi

Clarification: it's a stupid requirement.

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