Problem with SQL Native

[Rick Lipkin]

Oscar

Googled your error .. here are some ideas as what to look for ....

https://www.percona.com/blog/2019/07/05 ... 045-error/

Rick Lipkin

[Carles]

Hi,

Maybe you can try

Code: Select all | Expand

cSet := Set( _SET_DATEFORMAT, 'yyyy-mm-dd' )     
cSql := "SELECT * FROM test_timestamp WHERE col_timestamp >= '" + dtoc( LDATE ) + "' AND col_timestamp < '" + dtoc( HDATE ) + "'"   
Set( _SET_DATEFORMAT, cSet )

Just one more idea. I haven't been able to test it

C.

[ORibeiro]

The FW_DemoDB( 6 ) doesn’t work.

[nageswaragunupudi]

Raw,

I tried to run your example but returned the error: 1045 Acess denied for user 'sql7148817@'189-46-180-40.dsl.telesp.net.br (using password: YES).

Thanks

I see your FWH version (18.02) is very old.

In my sample program, please replace the line

Code: Select all | Expand

   oCn   := FW_DemoDB( 6 )
 

with

Code: Select all | Expand

   FWCONNECT oCn HOST "208.91.198.197:3306" USER "gnraofwh" PASSWORD "Bharat@1950" DATABASE "fwhdemo" PORT "3306"
 

I tested the program with this change using FWH1801 and it works. It should work for you too.
Please try and let us know.

[ORibeiro]

Hi Rao,

I was able to reproduce the situation. Please run the code below that it will return 31 records, but actually are 29.

The same select in HeidiSQL correctly returns 29 records.

Code: Select all | Expand

#include "fivewin.ch"

function Main()
   local oCn, oRs
   local t

   SET DATE ITALIAN
   SET CENTURY ON

   FWCONNECT oCn HOST "208.91.198.197:3306" USER "gnraofwh" PASSWORD "Bharat@1950" DATABASE "fwhdemo" PORT "3306" // oCn   := FW_DemoDB( 6 )
   if oCn == nil
      ? "connect fail"
      return nil
   endif
   oCn:lShowErrors := .t.

   oRs   := oCn:RowSet( "SELECT * FROM test_timestamp WHERE col_created >= '2022-01-01' AND col_created < '2022-01-13'" )
   XBROWSER oRs TITLE "01 JAN TO 12 JAN"
   oRs:Close()

   oRs   := oCn:RowSet( "SELECT COUNT(*) FROM test_timestamp WHERE col_created >= '2022-01-01' AND col_created < '2022-01-13'" )
   XBROWSER oRs TITLE "01 JAN TO 12 JAN"
   oRs:Close()

   oCn:Close()
return nil

 

[ORibeiro]

Please run this test.

Code: Select all | Expand

#include "fivewin.ch"

function Main()
   local oCn, oRs
   local t

   SET DATE ITALIAN
   SET CENTURY ON

   FWCONNECT oCn HOST "208.91.198.197:3306" USER "gnraofwh" PASSWORD "Bharat@1950" DATABASE "fwhdemo" PORT "3306" // oCn   := FW_DemoDB( 6 )
   if oCn == nil
      ? "connect fail"
      return nil
   endif
   oCn:lShowErrors := .t.

   oRs   := oCn:RowSet( "SELECT COUNT(*) FROM test_timestamp WHERE col_created >= '2022-01-01' AND col_created < '2022-01-14';" )
   XBROWSER oRs TITLE "01 JAT TO 13 JAN = 45 RECORDS (OK)"
   oRs:Close()

   oRs   := oCn:RowSet( "SELECT COUNT(*) FROM test_timestamp WHERE col_created >= '2022-01-01' AND col_created < '2022-01-13';" )
   XBROWSER oRs TITLE "01 JAN TO 12 JAN = 31 RECORDS -> (ERR) CORRECT = 29"
   oRs:Close()

   oRs   := oCn:RowSet( "SELECT COUNT(*) FROM test_timestamp WHERE col_created >= '2022-01-13' AND col_created < '2022-01-14';" )
   XBROWSER oRs TITLE "ONLY 13 JAN = 14 RECORDS -> (ERR) CORRECT = 16"
   oRs:Close()

   oCn:Close()
return nil

 

[nageswaragunupudi]

I will also test.
Meanwhile, can you give me a screenshot of the browse of full table without any where clause?
I like to see how does it appear from your time-zone.

[ORibeiro]

Hi Raw,

I sent the image to your e-mail because I don't know how to attach it here on the forum.

Thank your help.

[karinha]

Oscar:

http://imgur.com

or,

https://imgbb.com/

or

https://uploaddeimagens.com.br

Regards, saludos.

[ORibeiro]

Thaks João.

Here it are the image:

https://uploaddeimagens.com.br/imagens/VCBQyKo

[ORibeiro]

Mr. Rao,

Did you notice that the "00:mm:ss" records on the 13th day are showing up on the 12th day?

Do you have any news on this?

[nageswaragunupudi]

I know what is the issue. Just thinking how to explain this to you.

I did not ask screen shot of HeidiSQL.
I asked for screen shot of XBrowse of full table without where clause.
If you provide that, it becomes easier for me to explain.

Otherwise, run this program. You will see two browses one after the other.
Please provide screen shots of both the browses.

Code: Select all | Expand

#include "fivewin.ch"

function Main()

   local oCn, oRs
   local t

   SET DATE ITALIAN
   SET CENTURY ON
   SET TIME FORMAT TO "HH:MM:SS"

   FWCONNECT oCn HOST "208.91.198.197:3306" USER "gnraofwh" PASSWORD "Bharat@1950" DATABASE "fwhdemo" PORT "3306"
   if oCn == nil
      ? "connect fail"
      return nil
   endif
   oCn:lShowErrors := .t.

   ? "Time Now in my place", oCn:Now()

   oRs      := oCn:RowSet( "SELECT * FROM test_timestamp ORDER BY col_created" )
   XBROWSER oRs TITLE "AS VIEWED FROM MY COUNTRY TIMEZONE (" + oCn:time_zone + ")" SHOW SLNUM

   oCn:time_zone := "+00:00"
   ? "Time Now in England", oCn:Now()
   oRs:Requery()
   XBROWSER oRs TITLE "AS VIEWED FROM UTC TIMEZONE (GMT) (ENGLAND) (+00:00)" SHOW SLNUM
   oRs:Close()

   oCn:Close()

return nil
 

[ORibeiro]

Thanks Rao,
With oCn:time_zone := '+00:00' is Correct.
Thanks a lot.

Code: Select all | Expand

function Main()
   local oCn, oRs[4], oDlg, oFont

   SET DATE ITALIAN
   SET CENTURY ON
   SET TIME FORMAT TO "HH:MM:SS"

   FWCONNECT oCn HOST "208.91.198.197:3306" USER "gnraofwh" PASSWORD "Bharat@1950" DATABASE "fwhdemo" PORT "3306" // oCn   := FW_DemoDB( 6 )
   if oCn == nil
      ? "connect fail"
      return nil
   endif
   oCn:lShowErrors := .t.
   oCn:time_zone := '+00:00'
   ? "Time Now in my place", oCn:Now()

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-12 BOLD
   DEFINE DIALOG oDlg SIZE 800,900 PIXEL TRUEPIXEL TITLE "Test on native MySQL with TIMESTAMP fields -> With oCn:time_zone := '+00:00'" TRANSPARENT STYLE nOr( WS_CAPTION, WS_THICKFRAME, WS_SYSMENU, WS_MAXIMIZEBOX )

   oRs[1] := oCn:RowSet( "SELECT id,col_created FROM test_timestamp;" )
   @ 010,10 SAY "All 10 records: 5 from the 12th and 5 from the 13th." FONT oFont PIXEL
   @ 025,10 SAY "SELECT id,col_created FROM test_timestamp;"           FONT oFont PIXEL
   @ 040,10 XBROWSE oRs[1] SIZE 300,250 DATASOURCE oRs[1] PIXEL OF oDlg AUTOCOLS AUTOSORT CELL LINES NOBORDER FASTEDIT FOOTERS
   oRs[1]:aCols[1]:bFooter := {||"Records: "+Str( oRs[1]:nLen ) }
   oRs[1]:CreateFromCode()

   oRs[2] := oCn:RowSet( "SELECT id,col_created FROM test_timestamp WHERE col_created > '2022-01-11' AND col_created < '2022-01-13';" )
   @ 310,10 SAY "Only the 5 records of the 12th: There are 5." FONT oFont PIXEL
   @ 325,10 SAY "SELECT id,col_created FROM test_timestamp WHERE col_created > '2022-01-11' AND col_created < '2022-01-13';" FONT oFont PIXEL
   @ 340,10 XBROWSE oRs[2] SIZE 300,250 DATASOURCE oRs[2] PIXEL OF oDlg AUTOCOLS AUTOSORT CELL LINES NOBORDER FASTEDIT FOOTERS
   oRs[2]:aCols[1]:bFooter := {||"Records: "+Str( oRs[2]:nLen ) }
   oRs[2]:CreateFromCode()

   oRs[3] := oCn:RowSet( "SELECT id,col_created FROM test_timestamp WHERE col_created >= '2022-01-13 00:00:00';" )
   @ 610,10 SAY "Only the 5 records of the 13th: There are 5." FONT oFont PIXEL
   @ 625,10 SAY "SELECT id,col_created FROM test_timestamp WHERE col_created >= '2022-01-13 00:00:00';" FONT oFont PIXEL
   @ 640,10 XBROWSE oRs[3] SIZE 300,250 DATASOURCE oRs[3] PIXEL OF oDlg AUTOCOLS AUTOSORT CELL LINES NOBORDER FASTEDIT FOOTERS
   oRs[3]:aCols[1]:bFooter := {||"Records: "+Str( oRs[3]:nLen ) }
   oRs[3]:CreateFromCode()

   ACTIVATE DIALOG oDlg CENTERED // ON INIT ( oDlg:Maximize() )
   oRs[1]:Close()
   oRs[2]:Close()
   oRs[3]:Close()

   oCn:Close()
return nil
 

[nageswaragunupudi]

With oCn:time_zone := '+00:00' is Correct.

Not correct.
What you are thinking correct is not correct
and
what you think not correct is correct.
The table which you created itself was incorrect.

Take Heidi Sql and execute

Code: Select all | Expand

SELECT NOW();
 

Does the result match your time or time in some far away place, England?

When we propose to use functions like NOW(), TIME_STAMP(), etc and TIMESTAMP fields in tables, the right way is to set the session.time_zone to our local time zone, at the very beginning. with

Code: Select all | Expand

SET @@session.time_zone = '-03:00';
 

and then proceed.
Note: '-03:00' is Brazil TimeZone (UTC-3).

If I am still not clear, please let me know.