conncting a database from a routine
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 62
- Joined: Wed Dec 08, 2004 2:26 am
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
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
-
- 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
"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
"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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 62
- Joined: Wed Dec 08, 2004 2:26 am
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
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
-
- Premium Member
- Posts: 62
- Joined: Wed Dec 08, 2004 2:26 am
-
- Participant
- Posts: 107
- Joined: Wed Sep 29, 2004 10:15 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
ray.wurlod wrote: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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.