conncting a database from a routine

rkdatastage · Post by **rkdatastage** » Wed Apr 15, 2009 7:22 am

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

chulett · Post by **chulett** » Wed Apr 15, 2009 7:37 am

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

rkdatastage · Post by **rkdatastage** » Wed Apr 15, 2009 7:43 am

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

chulett · Post by **chulett** » Wed Apr 15, 2009 7:47 am

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.

rkdatastage · Post by **rkdatastage** » Wed Apr 15, 2009 7:56 am

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

chulett · Post by **chulett** » Wed Apr 15, 2009 8:11 am

Already did.

chulett · Post by **chulett** » Wed Apr 15, 2009 8:40 am

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.

kcbland · Post by **kcbland** » Wed Apr 15, 2009 10:02 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.

sasidhar_kari · Post by **sasidhar_kari** » Thu Apr 16, 2009 5:01 am

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

Thanks in advance

sasi

chulett · Post by **chulett** » Thu Apr 16, 2009 7:34 am

Asked and answered.

sasidhar_kari · Post by **sasidhar_kari** » Thu Apr 16, 2009 8:49 am

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

Regards
Sasi

chulett · Post by **chulett** » Thu Apr 16, 2009 9:02 am

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

chulett · Post by **chulett** » Thu Apr 16, 2009 10:06 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.

ray.wurlod · Post by **ray.wurlod** » Thu Apr 16, 2009 4:45 pm

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

Regards
Sasi

Clarification: it's a stupid requirement.

sasidhar_kari · Post by **sasidhar_kari** » Fri Apr 17, 2009 2:19 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