conncting a database from a routine

[ray.wurlod]

You need to determine where the error occurred, and that involves testing each and every one of your ErrCode variables. Particularly the one for SQLConnect(). For example you may have to use SQLSetConnectOptions() to set up the operating system login ID and password. Any time a BCI function returns other than SQL.SUCCESS or SQL.NO.DATA.FOUND it is necessary to call SQLError() repeatedly until all the information has been removed from the handle.

[sasidhar_kari]

Hi
Thanks for your response
I am able to check that whether the database connection is working or not and the connection is establised . Now i am facing problem with the update statement Like

SQLStmt = "update copy.sampl set Idvalue=3000 where ID=1;"
If Right(TrimB(SQLStmt),1) <> ";" Then SQLStmt := ";"
Errcode = SQLError(hEnv,hConn,SQLExecDirect(hStmt, SQLStmt),SQLState,DBMScode,LastErr)
Ans=Errcode

The return value of Ans is giving -2. What does it mean.
I checked the table but the value is not updated.

Kindly share your thoughts

Regards
Sasi

[ray.wurlod]

That's not how you call SQLError(). The third argument must be a valid statement handle.

[sasidhar_kari]

Hi

"That's not how you call SQLError(). The third argument must be a valid statement handle."

I can understand that i am doing some mistake with the statement what you have given , can you please guide me how to resolve this .

What is wrong with thrid argument . is there any alternative method to define it. Could you please kindly share it.

Thanks in advance

Regards
Sasi

[ray.wurlod]

Code: Select all

ErrCode = SQLConnect(hDBC, DataSource, UserID, Pwd) 
If ErrCode <> SQL.SUCCESS
Then
   Loop
      ErrCode = SQLError(hEnv,hDBC,hStmt,SQLState,DBMScode,LastErr)
   While ErrCode <> SQL.NO.DATA.FOUND and ErrCode <> SQL.ERROR
      Msg = "SQL state  " : SQLState : ", DBMS code = " : DBMScode : @VM : LastErr
      Call DSLogWarn(Msg, RoutineName)
   Repeat
End
 ... 

ErrCode = SQLExecDirect(hStmt, SQLstmt)
If ErrCode <> SQL.SUCCESS
Then
   Loop
      ErrCode = SQLError(hEnv,hDBC,hStmt,SQLState,DBMScode,LastErr)
   While ErrCode <> SQL.NO.DATA.FOUND and ErrCode <> SQL.ERROR
      Msg = "SQL state  " : SQLState : ", DBMS code = " : DBMScode : @VM : LastErr
      Call DSLogWarn(Msg, RoutineName)
   Repeat
End

[sasidhar_kari]

Hi
I had tried to implement the code and i found new issues now.

As in my code i had copule of scenario's

Scenario 1 :

$INCLUDE DSINCLUDE JOBCONTROL.H
$INCLUDE UNIVERSE.INCLUDE ODBC.H
* Database Environment Settings

henv = SQL.NULL.HENV
hConn = SQL.NULL.HDBC
hStmt = SQL.NULL.HSTMT
**Ans=0
** Connect to DataSource

status = SQLAllocEnv(hEnv)
status = SQLAllocConnect(hEnv, hConn)
**Ans = status
if status = SQL.SUCCESS then Ans=222
Else
Ans=111
end

Output is : 222

Scenario 2:

$INCLUDE DSINCLUDE JOBCONTROL.H
$INCLUDE UNIVERSE.INCLUDE ODBC.H
* Database Environment Settings

henv = SQL.NULL.HENV
hConn = SQL.NULL.HDBC
hStmt = SQL.NULL.HSTMT
**Ans=0
** Connect to DataSource

status = SQLAllocEnv(hEnv)
status = SQLAllocConnect(hEnv, hConn)
**Ans = status

status = SQLConnect(hConn, DSN, UID ,PWD )
if status = SQL.SUCCESS then Ans=222
Else
Ans=111
end

Output is : 111

Here i found that there is a problem while executing SQLConnect statement.

Can any share your ideas like what kind of possible issues or is there any thing wrong in the SQLConnect Statement. If any thing worng how to overcome it.

Thanks in advance

Regards
Sasi

[sasidhar_kari]

Added point to my question
the value for status variable is 1 and i understood that the sqlconnect is returning SQL_SUCCESS_WITH_INFO.

How to handle this as i am unable to insert or update a table.

Thanks in Advance
Sasi

[rkdatastage]

Hi
It seems that you had a problem while connecting to database.
Did you check whether you are having proper entries in uvodbc.config file

Regards
RK

[ray.wurlod]

Any time a BCI function returns other than SQL.SUCCESS or SQL.NO.DATA.FOUND it is necessary to call SQLError() repeatedly until all the information has been removed from the handle.