|
| HExecuteSQLQuery changing by the time SQL Server gets it. |
| Iniciado por Colin, mar., 23 2019 1:09 AM - 9 respostas |
| |
| | | |
|
| |
Membro registado
13 mensagems |
|
Publicado em março, 23 2019 - 1:09 AM |
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 |
| |
| |
| | | |
|
| | |
| |
Membro registado
31 mensagems |
|
Publicado em março, 23 2019 - 5:44 PM |
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 |
| |
| |
| | | |
|
| | |
| |
Membro registado
13 mensagems |
|
Publicado em março, 24 2019 - 12:40 AM |
Hi Garry,
Thanks for the thought. I tried that after posting, but no change 
--
Colin |
| |
| |
| | | |
|
| | |
| |
Membro registado
31 mensagems |
|
Publicado em março, 25 2019 - 3:07 PM |
| |
| |
| | | |
|
| | |
| |
Membro registado
13 mensagems |
|
Publicado em março, 26 2019 - 7:09 AM |
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 |
| |
| |
| | | |
|
| | |
| |
Membro registado
31 mensagems |
|
Publicado em março, 26 2019 - 2:42 PM |
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 |
| |
| |
| | | |
|
| | |
| |
Membro registado
31 mensagems |
|
Publicado em março, 26 2019 - 2:50 PM |
| |
| |
| | | |
|
| | |
| |
Publicado em março, 26 2019 - 7:48 PM |
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 |
| |
| |
| | | |
|
| | |
| |
Publicado em março, 26 2019 - 7:53 PM |
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 |
| |
| |
| | | |
|
| | |
| |
Membro registado
13 mensagems |
|
Publicado em março, 29 2019 - 8:07 AM |
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 |
| |
| |
| | | |
|
| | | | |
| | |
|