|
| HExecuteSQLQuery - Parameters Not Allowed - WebDev 15 |
| Iniciado por guest, 05,dic. 2010 23:19 - 5 respuestas |
| |
| | | |
|
| |
| Publicado el 05,diciembre 2010 - 23:19 |
Greetings All,
Lately I have been using the Query modeler and things have been going smoothly. Today I had to create a query using straight SQL because there are multiple outer joins and the use of correlation table names (aliases).
The SQL executes fine in the Query tool as long as I check Run hQueryWithoutCorrection in test mode.
But when I run it in the window, I get an error telling me that parameters are not allowed.
I have SQL Native Access installed.
Thanks for any pointers!!
Paul
Here is the SQL
SELECT DISTINCT
FTP.FP_CITY,
FTP.FP_POSTAL_CODE,
FAA.A_ID,
FAA.AIRPORT_IDENTITY,
FARS.a_identity,
FARS.SP_ID AS SP_ID,
FARS.AR_VEHICLE_TYPE AS AR_VEHICLE_TYPE,
FARS.AR_BASE_RATE AS AR_BASE_RATE,
FARS.AR_GRATUITY AS AR_GRATUITY,
FARS.AR_PARKING AS AR_PARKING,
FARS.AR_TOLLS AS AR_TOLLS,
FARS.AR_FUEL AS AR_FUEL,
FARS.AR_EMLN AS AR_EMLN,
FARS.AR_MEET_GREET AS AR_MEET_GREET,
FARS.AR_HOLIDAY AS AR_HOLIDAY,
FARS.AR_DISCOUNT AS AR_DISCOUNT,
FARS.RT_TOTAL AS RT_TOTAL,
FARS.r_currency AS r_currency,
FARS.r_drivetime AS r_drivetime,
FARS.AR_MILEAGE AS AR_MILEAGE,
FARS.AR_RATE_RANK AS AR_RATE_RANK
FROM FT_PLACE FTP
INNER JOIN FT_AIRPORT_ALL FAA ON FAA.A_ID = {Param_Affiliate_Supported_Airport}
Left OUTER JOIN FT_AFFILIATE_RATE_STAGED FARS ON FTP.FP_IDENTITY = FARS.a_identity AND FAA.AIRPORT_IDENTITY = FARS.b_identity AND FARS.SP_ID = {Param_AffiliateID}
WHERE FTP.FP_COUNTRY = {Param_Affiliate_Supported_Country}
AND FTP.FP_STATE = {Param_Affiliate_Supported_Region}
AND FTP.FP_COUNTY = {Param_Affiliate_Supported_SubRegion}
ORDER by
FTP.FP_CITY,
FTP.FP_POSTAL_CODE;
----------CODE ON THE BUTTON----------------------
HExecuteQuery(QRY_Rates_Staged_By_SubRegion_AffiliateID,hQueryWithoutCorrection,AffiliateID,gsRegion,"EWR",gsSubRegion,gsCountry)
TableDisplay(TABLE_QRY_Rates_Staged_By_SubRegion_AffiliateID)
TableSelectMinus(TABLE_QRY_Rates_Staged_By_SubRegion_AffiliateID)
----------END OF CODE ON THE BUTTON----------------------
----------ERROR MESSAGE----------------------
The 'TABLE_QRY_Rates_Staged_By_SubRegion_AffiliateID' control was not be initialized, the following HyperFileSQL Error occurred:
=============================
Error IN the SQL code of query. Unable to initialize the query.
The Use of parameters is not allowed with HExecuteSQLQuery.
=============================
Error IN the SQL code of query. Unable to initialize the query.
The Use of parameters is not allowed with HExecuteSQLQuery.
----------ERROR MESSAGE----------------------
I also tried this code but I get a similar error:
QRY_Rates_Staged_By_SubRegion_AffiliateID.Param_Affiliate_Supported_Airport = "EWR"
QRY_Rates_Staged_By_SubRegion_AffiliateID.Param_Affiliate_Supported_Country = gsCountry
QRY_Rates_Staged_By_SubRegion_AffiliateID.Param_Affiliate_Supported_Region = gsRegion
QRY_Rates_Staged_By_SubRegion_AffiliateID.Param_Affiliate_Supported_SubRegion = gsSubRegion
QRY_Rates_Staged_By_SubRegion_AffiliateID.Param_AffiliateID = AffiliateID
HExecuteSQLQuery(QRY_Rates_Staged_By_SubRegion_AffiliateID,hQueryWithoutCorrection,MyConnectionNativeSQL)
TableDisplay(TABLE_QRY_Rates_Staged_By_SubRegion_AffiliateID)
TableSelectMinus(TABLE_QRY_Rates_Staged_By_SubRegion_AffiliateID) |
| |
| |
| | | |
|
| | |
| |
| Publicado el 06,diciembre 2010 - 07:37 |
Greetings all,
I seem to be stuck in SQL HELL.
I have had great success with executing queries with parameters as long as I can define them using the WebDev query editor/modeler.
But this query built by pasting the SQL with the parameters will be the death of me.
Is it because I am pasting the arguments in with the brackets as {}?
Should I use something else?
When I use HExecuteQuery, why does the error state that HExecuteSQLQuery cannot accept parameters? I am not even calling HExecuteSQLQuery.
The 'TABLE_QRY_Rates_Staged_By_SubRegion_AffiliateID' control was not be initialized, the following HyperFileSQL Error occurred:
=============================
Error IN the SQL code of query. Unable to initialize the query.
The Use of parameters is not allowed with HExecuteSQLQuery.
Thanks!!
Paul |
| |
| |
| | | |
|
| | |
| |
| Publicado el 06,diciembre 2010 - 07:38 |
I start to wonder if there is not something goofy with my setup.
I changed the parameters in the SQL Query. I still get the error as:
is not an Item OR a parameter of query. new parameters must not be declared after query preparation. Check whether No syntax Error occurred IN the Item OR parameter name.
But the interesting thing is that there is NO parameter in my query as:
I had changed this parameter to Param_Airport.
I recompiled the whole project and this error still pops.
I right-click on the query to edit the query parameters and it shows as Param_Airport not .
Ugh.
Paul |
| |
| |
| | | |
|
| | |
| |
| Publicado el 06,diciembre 2010 - 10:30 |
Hi Paul,
Outside the query editor a query is just a string.
For these queries it is probably the easiest way to use stringbuild:
MyQuery=StringBuild("Select * from MyFile where Name='%1' ",sParam1)
Or enter the parameters directly into your query like:
MyQuery="Select * from MyFile where Name='+sParam1+' "
Best regards,
Piet |
| |
| |
| | | |
|
| | |
| |
| Publicado el 07,diciembre 2010 - 22:26 |
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 |
| |
| |
| | | |
|
| | |
| |
| Publicado el 28,marzo 2016 - 21:06 |
| HOLA TENGO UN PROBLEMA EN UNA TABLA SELECCIONE UNA QRY PERO QUIERO AÑADIR UNA COLUMNA CHECKS EN LA CUAL GUARDAR DATO POR DATO |
| |
| |
| | | |
|
| | | | |
| | |
|