|
PROFESSIONAL NEWSGROUPS
WINDEV, WEBDEV and WINDEV Mobile |
| | | | | |
| Home → WINDEV 2024 → HExecuteQuery - Parameters Not Allowed - Is That True? |
| HExecuteQuery - Parameters Not Allowed - Is That True? |
| Started by guest, Dec., 06 2010 11:17 PM - 9 replies |
| |
| | | |
|
| |
| Posted on December, 06 2010 - 11:17 PM |
Greetings All,
Can someone tell me if it IS or IS NOT possible to use a query via HExecuteQuery if the query was built using hand-written sql with parameters hitting SQL Server with Native Access?
I created the query, pressed F2 to view the sql, pasted in the sql with the parameters, checked the box to 'Run with HQueryWithoutCorrection in test mode', I run the test, type in the values for the parameter it works perfectly!!
I think I should be able to associate that query with a table and load it using the code below.
YES? NO? It does not work for me as I get a runtime error stating 'The use of parameters is not allowed with HExecuteSQLQuery.' This is odd because I am doing HExecuteQuery, not HExecuteSQLQuery.
I know I will have to hand code the updates as it is a complex query, but I really don't want to have to code for the loading of the table.
Here is my code:
QRY_Rates_Staged_By_SubRegion_AffiliateID2.Param_Airport = sAirport
QRY_Rates_Staged_By_SubRegion_AffiliateID2.Param_Country = gsCountry
QRY_Rates_Staged_By_SubRegion_AffiliateID2.Param_Region = gsRegion
QRY_Rates_Staged_By_SubRegion_AffiliateID2.Param_SubRegion = gsSubRegion
QRY_Rates_Staged_By_SubRegion_AffiliateID2.Param_Affiliate = AffiliateID
HExecuteQuery(QRY_Rates_Staged_By_SubRegion_AffiliateID2,MyConnectionNativeSQL,hQueryWithoutCorrection)
TableDisplay(TABLE_QRY_Rates_Staged_By_SubRegion_AffiliateID)
TableSelectMinus(TABLE_QRY_Rates_Staged_By_SubRegion_AffiliateID)
Thanks all!!
Paul |
| |
| |
| | | |
|
| | |
| |
| Posted on December, 07 2010 - 6:54 AM |
Paul,
it IS possible for sure,because i'm doing this as well!
Maybe the final semi-colon is the bad guy?
Do you fill ALL parameters? Normally when setting a parameter to nul (by NOT giving it a value) is it removed from the sql-text by WD. But i'm not sure if this is the case when using hQueryWithoutCorrection |
| |
| |
| | | |
|
| | |
| |
| Posted on December, 07 2010 - 6:55 AM |
Sorry Paul, was away and couldn't help earlier.
Whenever you try to edit the generated sql code, WD will warn you that it will dissociate the code from the query editor. From there onwards, it is going to use HExecuteSQLQuery and hence no parameters.
If you are from the Microsoft world, you might have heard of sqlpassthru (WD equivalent is hexecutesqlquey). This means that the sql query will not be processed by your interface (foxpro, visual studio, WD) engine but will be sent as it is to the SQL Server. So, if you want to use parameters with hexecutesqlquery, the parameters have to be a part of the sql string in a manner that your server engine can interpret. eg, if you want to send:
select * from stafflist where lastname='Smith' and salary > 16547
the best way is:
myselect is sting="select * from stafflist where lastname='%1' and salary > %2"
myselect=stringbuild(myselect, "Smith", 16547)
then you use hexecutesqlquery on the myselect variable without any parameters as the parameters will be a part of the sql anyway.
Another headache proof way of doing this especially If you have a complex query with the multi-mega outer joins is:
Building a view in sql server. You can then import the view definition into wd, the same way you imported the tables, then use good old hexecutequery with parameters.
Hope this helps.
issah |
| |
| |
| | | |
|
| | |
| |
| Posted on December, 07 2010 - 6:56 AM |
Thanks, Arie.
There is no semi-colon in the project I am currently trying to run.
I went back to another project in which I realized that I had entered manual SQL and it had worked fine.
Now it does not.
I think something changed after I installed the SQL Native Access Driver (for the worse).
I am thinking about uninstalling it and trying again.
Thank you again for your time, Arie.
Paul |
| |
| |
| | | |
|
| | |
| |
| Posted on December, 07 2010 - 6:56 AM |
Thanks Issah,
I just finished my note to Arie after doing a lot of testing. I am currently thinking that Native SQL Driver is to blame as my other project worked before and now longer does (with no code changes).
Paul |
| |
| |
| | | |
|
| | |
| |
| Posted on December, 07 2010 - 6:57 AM |
Hi Issah,
If you use your 'best approach' as...
myselect is sting="select * from stafflist where lastname='%1' and salary > %2"
myselect=stringbuild(myselect, "Smith", 16547)
...do I not preclude the use of associating the query with the table and have the benefit of being able to automatically generated a table whenever the query changes?
Thanks!!
Paul |
| |
| |
| | | |
|
| | |
| |
| Posted on December, 07 2010 - 6:57 AM |
aka pros and cons. Thats why I also suggested creating a view and querying from there. Windev will treat it like a table. That way you dont lose your table benefits.
issah |
| |
| |
| | | |
|
| | |
| |
| Posted on December, 07 2010 - 6:57 AM |
Thanks, Issah.
I have come to the conclusion that installing the Native SQL Access has goofed something up in my development environment.
I went back to an earlier project and realized that calling a query created with hand-written SQL had worked fine. I inadvertantly saved the query and it immediately stopped working.
The only difference I can think of is a) new version of WebDev or b) I installed Native SQL Access.
Best,
Paul |
| |
| |
| | | |
|
| | |
| |
| Posted on December, 07 2010 - 10:26 PM |
I FINALLY FIGURED IT OUT - UGH!! - SEVERAL DAYS LOST
I am not really sure what is going on, but it turns out that when I created the table control, WebDev kindly generated the following code causing the error. I guess it tries to automatically execute the query. And by default it see that it is SQL code and run HExecuteSQLQuery instead of HExecuteQuery with the HQueryWithoutCorrection parameter that I would use.
MySource.Param_Airport = ""
MySource.Param_Affiliate = ""
MySource.Param_Country = ""
MySource.Param_Region = ""
MySource.Param_SubRegion = ""
I commented out the above lines and was able to get it to work. But getting it to work was a real hassle. The following code will NOT work.
QRY_Rates_Staged_By_SubRegion_AffiliateID2.Param_Country = gsCountry
QRY_Rates_Staged_By_SubRegion_AffiliateID2.Param_Region = gsRegion
QRY_Rates_Staged_By_SubRegion_AffiliateID2.Param_SubRegion = gsSubRegion
QRY_Rates_Staged_By_SubRegion_AffiliateID2.Param_Affiliate = AffiliateID
QRY_Rates_Staged_By_SubRegion_AffiliateID2.Param_Airport = sAirport
Why? I think it is because it is a complex query and I have to use HQueryWithoutCorrection. Which means that WebDev stops doing nice stuff for you like putting the quotes around your variables when it builds the sql string.
So I tried this:
QRY_Rates_Staged_By_SubRegion_AffiliateID2.Param_Country = "'" + gsCountry + "'"
QRY_Rates_Staged_By_SubRegion_AffiliateID2.Param_Region = "'" + gsRegion + "'"
QRY_Rates_Staged_By_SubRegion_AffiliateID2.Param_SubRegion = "'" + gsSubRegion + "'"
QRY_Rates_Staged_By_SubRegion_AffiliateID2.Param_Affiliate = "'" + AffiliateID + "'"
QRY_Rates_Staged_By_SubRegion_AffiliateID2.Param_Airport = "'" + sAirport + "'"
But at runtime I get an error telling me that I was not using the 'Param_Country' parameter.
So the only thing that would work was this:
gbBresult = HExecuteQuery(QRY_Rates_Staged_By_SubRegion_AffiliateID2,MyConnectionNativeSQL,hQueryWithoutCorrection+hNoBind,"'" + sAirport + "'","'" + AffiliateID + "'","'" + gsCountry + "'","'" + gsRegion + "'","'" + gsSubRegion + "'")
And that, my friends, is a pain in the butt.
I didn't purchase the SQL Native Access to do more work. I bought it to do less work!!
For instance, if this query had dates in it, then I would have to manually build the strings to convert the dates to the SQL Server format.
So, what did we learn?
Are you listening, my dear friends at WinDev?
1) The SQL Native Access needs to be beefed up. If you want to sell into the larger shops where Oracle, SQL Server and Sybase are mandated then you have to do a better job of supporting these databases.
2) The table control needs to be more robust. You need to be able to specify the query type as HExecuteQuery or HExecuteSQLQuery. If you specify HExecuteQuery, then you need to be able to specify other parameters like HQueryWithoutCorrection. Wait did I say that? Wrong. HQueryWithoutCorrection needs to be thrown out especially if you have Native Access. That's what Native Access if for!!
I am going to post this at the end of my other painful thread on a similar subject.
Best to all,
Paul |
| |
| |
| | | |
|
| | |
| |
| Posted on April, 30 2025 - 8:52 AM |
Hello,
For people stumbling unto this topic, I got the same error, but had a different cause:
I had added an Inner Join to my SQL, which made my ORDER BY clause ambiguous, meaning that the new added table shared a property name.
Worth checking out, maybe it fixes your problem  |
| |
| |
| | | |
|
| | | | |
| | |
| | |
| |
|
|
|