Server Routine to connect SQL Server
Moderators: chulett, rschirm, roy
Server Routine to connect SQL Server
I am trying to connect SQL Server using server routine. See the below peace of code.
Sql = "will have the connection details for sql server"
Debug = 0
SQL = Sql
Command = SQL
Command := 'select id from Id_table'
and then the output will be captures eventually.
But it is not connecting to DB, but triggering exception.
If any one guide me in this will be great.
Sql = "will have the connection details for sql server"
Debug = 0
SQL = Sql
Command = SQL
Command := 'select id from Id_table'
and then the output will be captures eventually.
But it is not connecting to DB, but triggering exception.
If any one guide me in this will be great.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Could be something to do with the PATH for the sql command. Could be the fact that the statement that sets this should have quotes around the command (SQL = "sql ").
You might also like to investigate the BCI functions. A search of DSXchange will throw up code examples. These mimic the ODBC API and give you complete control over the connection to the database.
You might also like to investigate the BCI functions. A search of DSXchange will throw up code examples. These mimic the ODBC API and give you complete control over the connection to the database.
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.
Still getting error
Hi Ray
Thanks for your reply. Yes i did that "Sql", but still getting error. I am getting the user defined error return code of -98. Please guide me, what mistake i did in this.
Thanks for your reply. Yes i did that "Sql", but still getting error. I am getting the user defined error return code of -98. Please guide me, what mistake i did in this.
Code: Select all
RoutineName = 'GetID'
Debug = 0
* Passing this value'sqlcmd OR isql -S DBNAME -U UNAME -P PASSWD' to Sql
SQL = "Sql"
Command = SQL : char(090)
Commnad := 'use Development'
Command := 'select id from id_table'
GoSub ExqtCommand
GetOutput=Trim(Output, @FM)
GetOutput=Ereplace(GetOutput, char(009), '')
If Debug Then Call DSLogInfo(GetOutput, RoutineName:"***DEBUGTHEERROR")
GetOutput=Trim(Output, @FM)
GetAns = GetOutput
Return(Ans)
* Subroutines
*
ExqtCommand:
Call DSExecute('NT', Command, Output, RtnCode)
Message = "Executed sqlserver command."
Message<-1> = "Command=":Command
Message<-1> = "RtnCode=":RtnCode
Message<-1> = "Output=" : Output
If Debug Then Call DSLogInfo(Message, RoutineName:"***DEBUGERROR")
Ans = 0
If Output matches '...ERROR...' Then
Message = "Error executing SQL statement."
Message<-1> = "Command=":SQL
Message<-1> = "RtnCode=":RtnCode
Message<-1> = "Output=":Output
Call DSLogWarn(Message, RoutineName)
Ans = -99
End
If RtnCode <> 0 Then
Message = "Error executing SQL command."
Message<-1> = "Command=":SQL
Message<-1> = "RtnCode=":RtnCode
Message<-1> = "Output=":Output
Call DSLogWarn(Message, RoutineName)
Ans = -98
End
Return(Ans)
Dear chulett
You are right. After i did it, i got the error details. I am trying to correct it. Meanwhile if you can figureout the error pls do help me. - Thanks
Arg1 = sqlcmd -S DBNAME -U UNAME -P PWD
Test failed.
Program "DSU.GetID": Line 7, Variable "Commnad" previously undefined. Empty string used.
DSLogWarn called from : GetID
Message to be logged is...
> Error executing SQL command.
> Command=Sql
> RtnCode=1
> Output='SqlZselect' is not recognized as an internal or external command,
> operable program or batch file.
>
Subroutine "DSLogInfo" called with 1 arguments, 2 were expected"
Program "DSU.GetID": Line 50, Unable to load subroutine.
You are right. After i did it, i got the error details. I am trying to correct it. Meanwhile if you can figureout the error pls do help me. - Thanks
Arg1 = sqlcmd -S DBNAME -U UNAME -P PWD
Test failed.
Program "DSU.GetID": Line 7, Variable "Commnad" previously undefined. Empty string used.
DSLogWarn called from : GetID
Message to be logged is...
> Error executing SQL command.
> Command=Sql
> RtnCode=1
> Output='SqlZselect' is not recognized as an internal or external command,
> operable program or batch file.
>
Subroutine "DSLogInfo" called with 1 arguments, 2 were expected"
Program "DSU.GetID": Line 50, Unable to load subroutine.
Hi chulett
I corrected the errors. This time after it executed i didnt get any return value at all (hope it hangs).
SQL = "sqlcmd -S DBNAME -U UNAME-P PWD" : char(010)
Command = SQL : char(010)
Command := 'use Development' : char(010)
Command := 'select max(id) from ID_Table' : char(010)
Is there is any mistake in the above code? This is the first time i am working with SQL Server.
Your time and comment is highly appriciatable.
I corrected the errors. This time after it executed i didnt get any return value at all (hope it hangs).
SQL = "sqlcmd -S DBNAME -U UNAME-P PWD" : char(010)
Command = SQL : char(010)
Command := 'use Development' : char(010)
Command := 'select max(id) from ID_Table' : char(010)
Is there is any mistake in the above code? This is the first time i am working with SQL Server.
Your time and comment is highly appriciatable.
And I've never worked with SQL Server, so you'll need to wait for someone who has to know if what you are doing is appropriate for that database.
In the meantime, where exactly are you running this routine? In a Sequence job? Where is the return value used?
In the meantime, where exactly are you running this routine? In a Sequence job? Where is the return value used?
-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:
Do you mean to be using as the actual command? If so, put that in.
Code: Select all
isql
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.
Hi Ray
I dont know what mistake i am doing, but i will keep on try to make it work. Meanwhlie if anyone find the way, that will be real help.
But when i tried isql i am getting the below error.
TEST #1
*******
DSLogWarn called from : GetID
Message to be logged is...
> Error executing SQL command.
> Command=isql -S DBNAME -U UNAME -P PWD
> RtnCode=1
> Output='isql' is not recognized as an internal or external command,
> operable program or batch file.
>
DSLogInfo called from : isql -S DBNAME -U UNAME -P PWD
Message to be logged is...
> Error executing SQL command.
> Command=isql -S DBNAME -U UNAME -P PWD
> RtnCode=1
> Output='isql' is not recognized as an internal or external command,
> operable program or batch file.
>
Result = -98 (This is my return value for errors)
I dont know what mistake i am doing, but i will keep on try to make it work. Meanwhlie if anyone find the way, that will be real help.
But when i tried isql i am getting the below error.
TEST #1
*******
DSLogWarn called from : GetID
Message to be logged is...
> Error executing SQL command.
> Command=isql -S DBNAME -U UNAME -P PWD
> RtnCode=1
> Output='isql' is not recognized as an internal or external command,
> operable program or batch file.
>
DSLogInfo called from : isql -S DBNAME -U UNAME -P PWD
Message to be logged is...
> Error executing SQL command.
> Command=isql -S DBNAME -U UNAME -P PWD
> RtnCode=1
> Output='isql' is not recognized as an internal or external command,
> operable program or batch file.
>
Result = -98 (This is my return value for errors)
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: