|
| Inicio → WINDEV 2024 → [WB19] Weird problem running stored procedure via HExecuteSQLQuery |
| [WB19] Weird problem running stored procedure via HExecuteSQLQuery |
| Iniciado por guest, 01,abr. 2015 09:17 - 4 respuestas |
| |
| | | |
|
| |
| Publicado el 01,abril 2015 - 09:17 |
Hi All,
When I try to call a stored procedure on an MSSQL database (v 2012) I get a weird error.
Access is via OLEdb driver.
Connection is:
DB_Connection..Caption = "MyConnection"
DB_Connection..User = DB_UserName
DB_Connection..Password = DB_Password
DB_Connection..Server = DB_Server
DB_Connection..Database = DB_Name
DB_Connection..Provider = hOledbSQLServer
DB_Connection..Access = hOReadWrite
The piece of code that is called to run the stored procedure:
// Clear all error messages in the list previously set by this function pErrLst:ClearListForCaller(dbgInfo(dbgElement,dbgCurrentProcess)) // For testing m_SQLCommand = "EXEC spTest" // also tried "EXEC dbo.spTest" //// IF NOT HExecuteSQLQuery(m_dsResults,pCon,hQueryWithoutCorrection+hNoBind,m_SQLCommand) THEN ErrMsg:AddToList(pErrLst,soErrorMessage::EM_ERROR,"DB","Database error. Executing: "+m_SQLCommand,HErrorInfo(hErrMessage)) MyApp:LogError(pErrLst,HErrorInfo(hErrFullDetails)) bResult = False ELSE m_NumberOfRecords = HNbRec(m_dsResults) ...
Tried with or without the hNoBind option, no difference.
Tried with hQueryDefault and hQueryWithoutCorrection; no effect
The error is:
What happened?
Error in the SQL code of query. Unable to initialize the query.
Unexpected word: spTest
Error code: 70208
Level: non-fatal error (EL_ONRETURN)
WD55 error code: 208
Dump of the error of 'WD190SQL.DLL' module (19.0.60.2).
Debugging information:
IEWD190SQL=1.7
Module=
Version=<19.0.60.2>
Additional Information:
EIT_LOGICALTABLENAME :
When I use as the m_SQLCommand = "Select * from users" to test this, the query runs fine !
When I run the "EXEC spTest" from the SQL Mangement Studio, its runs fine !
When I use the Hxxxx commands or a Query with the same connection, this all works perfectly.
What would be the problem that I cannot call a stored procedure via the EXEC ... syntax ? This must be something stupid or a little thing a forgot :confused:, on the forum I have found different posts using the same syntax to execute a stored procedure.
So what could be wrong ? :hot:
Thanks
Danny |
| |
| |
| | | |
|
| | |
| |
| Publicado el 01,abril 2015 - 10:43 |
Hi Danny,
I'm happy to see some of my classes and best practice concepts make it to good use...
Some thoughts on your question:
- Does the user you are connecting with have the rights to execute stored procedures on the SQL Server?
- Depending on your connection string and the initial SQL Server catalog for the user, I believe you will need to pass the full qualified path to the stored procedure (thus including at least the schema, but maybe also db.schema.procname).
- I believe you always will have to use hQueryWithoutCorrection for calling stored procedures on MSSQL (although bot sure).
Cheers,
Peter H. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 01,abril 2015 - 12:47 |
Hi Peter,
Yes I am using your WXFramework, and it is great working with it !
Thanks for the tips, I have checked them:
- I have checked the rights of the user, and for testing this user is dbOwner at the moment, so this should not make any problem running the stored Procedures. Tried logging into SQL Management studio with the same user, and the stored procedure is called correctly. So i think security is not a problem.
- I have tried running EXEC [DA].[dbo].[spTest], but that gives the same error only the string "DA" is now unknown instead of "spTest". This also runs fine in SQL Management Studio.
- I also think that I always need to use the hQueryWithoutCorrection, I use it in other projects where I only have full SQL statements and no stored procedures. This because the DB is OLEDb.
mmm, what could this be ! I'll guess I have to keep looking and hope anybody has some more tips !
Thanks
Danny |
| |
| |
| | | |
|
| | |
| |
| Publicado el 01,abril 2015 - 14:50 |
Peter,
I tried this code in a windev Window, just to test things, This code worked.
Myconnection..Caption = "MyConnection" Myconnection..User = "****" Myconnection..Password = MyPassword Myconnection..Server = "****" Myconnection..Database = "****" Myconnection..Provider = hOledbSQLServer Myconnection..Access = hOReadWrite DB_TimeOut is int = 120 //Max wait time for SQL return by the WD engine in seconds - TO DO: Implement as ini setting into framework IF HDescribeConnection(Myconnection..Caption,Myconnection..User,MyPassword,Myconnection..Server,Myconnection..Database,hOledbSQLServer,hOReadWrite,"WD Command Timeout ="+DB_TimeOut) THEN IF HOpenConnection(Myconnection..Caption) THEN IF NOT HChangeConnection("*",Myconnection..Caption) THEN Error(ErrorInfo(errFullDetails)) END END END m_SQLCommand is string = "EXEC [spTest]" // also tried "EXEC dbo.spTest" IF NOT HExecuteSQLQuery(dsResult,Myconnection,hQueryWithoutCorrection,m_SQLCommand) THEN Error(ErrorInfo(errFullDetails)) ELSE Info("ok, aan tal records = " + dsResult..NbItem) HReadFirst(dsResult) Trace(dsResult.ID) END
This is the same basic code that is used in the WXApplication DBConnect.
Then I tried to mimic the windev testcode into the WebDev code with the WXFramework.
That did not work...
When I defined a local variable with the connection and filled in or assigned the existing connection variable.
MyConnection is connection
MyConnection = DB_Connection
And used My connection in test lines right after the connection lines in DBConnect (WXApplication). This worked correctly !! Very strange ?
Then I noticed that in the WXApplication DBConnect the functions HDescripbeConnection and HOpenConnection are using the "DBConnection..Caption" as a parameter, not the connection itself !!
So I removed the added code to the WXApplication DBConnect (this code was back original now without the MyConnection hack)
So I tried to use this "Caption" in my code where I called HExecuteSQLQuery also
IF Buildcommand() <> "" THEN // Clear all error messages in the list previously set by this function pErrLst:ClearListForCaller(dbgInfo(dbgElement,dbgCurrentProcess)) IF NOT HExecuteSQLQuery(m_dsResults,pCon..Caption,hQueryWithoutCorrection,m_SQLCommand) THEN ErrMsg:AddToList(pErrLst,soErrorMessage::EM_ERROR,"DB","Database error. Executing: "+m_SQLCommand,HErrorInfo(hErrMessage)) MyApp:LogError(pErrLst,HErrorInfo(hErrFullDetails)) bResult = False ELSE m_NumberOfRecords = HNbRec(m_dsResults)
And guess what !!! It works :rp:
I still do not understand why you should be giving a DB_Connection..Caption instead of a DB_Connection to let the stored procedures work. And why if you use a local connection variable assigned to the same connection data it is also working (then without the ..caption) ??
Maybe someone can explain this, or has a clue why ? It does not seem logical to me !
This is from the help of HExecuteSQLQuery
: Optional character string or connection variable
Name of the connection used to run the query. This connection was defined in the data model editor, or by programming with HDescribeConnection or HOpenConnection, or by specifying the properties of the connection variable. is set to False if this parameter does not correspond to an existing connection.
Maybe I'll have to report this to PCSoft ? (The are going to request a demo project I guess)
So if you have problems with executing a stored procedure via HExecuteSQLQuery and have strange errors, try the things in this post and see if it will fix your problem also ?
Have a nice day
Danny |
| |
| |
| | | |
|
| | |
| |
| Publicado el 02,abril 2015 - 10:32 |
Hi Danny,
Thanks for the heads up!
Interesting... I'll have a check...
There's probably a similar reason why I passed the connection by its assigned name instead of by a reference to the connection variable in the past in the framework. I just don't recall anymore why...
Greetz,
P. |
| |
| |
| | | |
|
| | | | |
| | |
|