|
| HExecuteSQLQuery changing by the time SQL Server gets it. |
| Débuté par Colin, 23 mar. 2019 01:09 - 9 réponses |
| |
| | | |
|
| |
Membre enregistré
13 messages |
|
Posté le 23 mars 2019 - 01:09 |
Hi All,
I have a simple query executed from a button after setting the date range in the form that produces unexplained results.
ctInfoStore is Connection
ctInfoStore..User = "****************"
ctInfoStore..Password = "******************"
ctInfoStore..Server = "localhost"
ctInfoStore..Database = "InfoStore"
ctInfoStore..Provider = hOledbSQLServer
sGuestQuery is string = [
SELECT SURNAME, FIRSTNAME, EMAIL, TELEMOB
FROM guest
WHERE USERCODE IN (
SELECT DISTINCT GuestCode
FROM BookingDetails
WHERE Arrival > '%1'
AND Departure > '%2'
)
AND EMAIL LIKE '%@%'
]
dsDataSet is Data Source
IF HOpenConnection(ctInfoStore) THEN
sQuery is string = StringBuild(sGuestQuery,DateToString(WIN_Main.EDT_ArrivalDate..Value, "YYYY-MM-DD"),DateToString(WIN_Main.EDT_DepartureDate, "YYYY-MM-DD"))
Info(sQuery)
IF HExecuteSQLQuery(dsDataSet, hQueryDefault, sQuery) THEN
WIN_Main.STC_DB_Count..Text = HNbRec(dsDataSet)
END
ELSE
Info(ErrorOccurred() +" - "+ HErrorInfo())
END
The Info(sQuery) line produces
SELECT SURNAME, FIRSTNAME, EMAIL, TELEMOB
FROM guest
WHERE USERCODE IN (
SELECT DISTINCT GuestCode
FROM BookingDetails
WHERE Arrival > '2019-03-22'
AND Departure > '2019-03-22'
)
AND EMAIL LIKE '%@%' just as I expect, however, I was expecting a count of about 22, but I got a count of over 1300.
On investigating what query the SQL Server was executing, I found this (Manually formated for readability)
SET FMTONLY ON SELECT COUNT(*) AS WDACCESNATIF_NBENR FROM (
SELECT "guest"."SURNAME", "guest"."FIRSTNAME", "guest"."EMAIL", "guest"."TELEMOB"
FROM "guest"
WHERE (( USERCODE IN (
SELECT DISTINCT "BookingDetails"."GuestCode" AS "GuestCode"
FROM "BookingDetails"
WHERE ( ( "BookingDetails"."Arrival" > '01-01-1992 01:01:01:000' )
AND ( "BookingDetails"."Departure" > '01-01-1992 01:01:01:000' ) ) ) )
AND "guest"."EMAIL" LIKE '%@%'
)) WD_SUBQUERY SET FMTONLY OFF
Obviously the reason for the high count result is because the dates are wrong. I asked for 2019-03-22, but I got 01-01-1992.
Why is this happening, and how do I fix it?
--
Colin |
| |
| |
| | | |
|
| | |
| |
Membre enregistré
31 messages |
|
Posté le 23 mars 2019 - 17:44 |
This is just a guess (I'm no SQL guru) but have you tried changing the line:
IF HExecuteSQLQuery(dsDataSet, hQueryDefault, sQuery) THEN
to:
IF HExecuteSQLQuery(dsDataSet, hQueryWithoutCorrection, sQuery) THEN
Garry
--
Garry |
| |
| |
| | | |
|
| | |
| |
Membre enregistré
13 messages |
|
Posté le 24 mars 2019 - 00:40 |
Hi Garry,
Thanks for the thought. I tried that after posting, but no change 
--
Colin |
| |
| |
| | | |
|
| | |
| |
Membre enregistré
31 messages |
|
Posté le 25 mars 2019 - 15:07 |
| |
| |
| | | |
|
| | |
| |
Membre enregistré
13 messages |
|
Posté le 26 mars 2019 - 07:09 |
Thanks Garry,
Had a quick look at the link in (1), but this won't help, as the query being generated by WinDev is incorrect, not the results from the server.
I will try the Facebook page you suggested.
--
Colin |
| |
| |
| | | |
|
| | |
| |
Membre enregistré
31 messages |
|
Posté le 26 mars 2019 - 14:42 |
re: (1) Yes, I realize that WinDev produces the correct query (from WinDev's point of view) but it is obviously being misinterpreted by the server.
Here is snippet from a post by Arie that I found on WXForum :
[ Going to Micrsoft Sql server is another thing.
- you need some export/import tool to copy all existing data. In the end I wrote one myself.
- you can start with the standard OLEDB driver (free) or choose to use the native driver (747 euro once and 199 each version upgrade). I would make that decision from the start and personally go for the native driver.
- dates/times/datetimes can be a pain in the…. timefields are createde as datetime in the database. I used the standard driver first which saves 1900-01-01 as the datepart for a time, after that I switched to the native driver which saves 2000-01-01. Costed me quite some time to find out why sorting acted strange after that. ]
What version of MSSQL are you using? Do you have other queries using dates that work?
--
Garry |
| |
| |
| | | |
|
| | |
| |
Membre enregistré
31 messages |
|
Posté le 26 mars 2019 - 14:50 |
| |
| |
| | | |
|
| | |
| |
Posté le 26 mars 2019 - 19:48 |
Hi
Can you try this ...
WHERE CONVERT(VARCHAR, Arrival, 23) > '%1'
AND CONVERT(VARCHAR, Departure, 23) > '%2'
where formated as YYYY-MM-DD
for sqlServer
HTH
Cheers
King |
| |
| |
| | | |
|
| | |
| |
Posté le 26 mars 2019 - 19:53 |
Hi
Try this
WHERE CONVERT(VARCHAR,Arrivale, 23) > '%1'
AND CONVERT(VARCHAR,Departuree, 23) > '%2'
where 23 is sqlDateFormat for YYYY-MM-DD
HTH
Cheers
King |
| |
| |
| | | |
|
| | |
| |
Membre enregistré
13 messages |
|
Posté le 29 mars 2019 - 08:07 |
Hmm..
Thought I had replied to both Garry and King's posts earlier....
OK. Found the solution.
IF HExecuteSQLQuery(dsDataSet, hQueryDefault, sQuery) THEN
needs to be
IF HExecuteSQLQuery(dsDataSet, ctConnectionName, hQueryDefault, sQuery) THEN
On intensive reading of the documentation, you also will find this hidden gem in the "Running an SQL query via a connection" section of the HExecuteSQLQuery page.
--
Colin |
| |
| |
| | | |
|
| | | | |
| | |
|