topic-41360

Rick Lipkin

Posts: 2706

Joined: Fri Oct 07, 2005 01:50 PM

Re: Problem with SQL Native
Posted: Mon Feb 07, 2022 05:23 PM

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

Posts: 1283

Joined: Fri Feb 10, 2006 02:34 PM

Re: Problem with SQL Native
Posted: Mon Feb 07, 2022 06:27 PM

Hi,

Maybe you can try

Code (fw): Select all Collapse

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.

Salutacions, saludos, regards

"...programar es fácil, hacer programas es difícil..."

UT Page -> https://carles9000.github.io/
Forum UT -> https://discord.gg/bq8a9yGMWh
HIX -> https://github.com/carles9000/hix

ORibeiro

Posts: 187

Joined: Mon Oct 20, 2008 06:33 PM

Re: Problem with SQL Native
Posted: Mon Feb 07, 2022 08:39 PM

The FW_DemoDB( 6 ) doesn’t work.

Oscar Ribeiro

OASyS Informática

Fwh18.02 + xHarbour 1.2.3 + Bcc72

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Problem with SQL Native
Posted: Tue Feb 08, 2022 06:58 AM

ORibeiro wrote: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 (fw): Select all Collapse

   oCn   := FW_DemoDB( 6 )

with

Code (fw): Select all Collapse

   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.

Regards

G. N. Rao.

Hyderabad, India

ORibeiro

Posts: 187

Joined: Mon Oct 20, 2008 06:33 PM

Re: Problem with SQL Native
Posted: Wed Feb 09, 2022 12:50 PM

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 (fw): Select all Collapse

#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

Oscar Ribeiro

OASyS Informática

Fwh18.02 + xHarbour 1.2.3 + Bcc72

ORibeiro

Posts: 187

Joined: Mon Oct 20, 2008 06:33 PM

Re: Problem with SQL Native
Posted: Wed Feb 09, 2022 01:18 PM

Please run this test.

Code (fw): Select all Collapse

#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

Oscar Ribeiro

OASyS Informática

Fwh18.02 + xHarbour 1.2.3 + Bcc72

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Problem with SQL Native
Posted: Wed Feb 09, 2022 01:24 PM

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.

Regards

G. N. Rao.

Hyderabad, India

ORibeiro

Posts: 187

Joined: Mon Oct 20, 2008 06:33 PM

Re: Problem with SQL Native
Posted: Wed Feb 09, 2022 05:10 PM

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.

Oscar Ribeiro

OASyS Informática

Fwh18.02 + xHarbour 1.2.3 + Bcc72

karinha

Posts: 8515

Joined: Tue Dec 20, 2005 07:36 PM

Re: Problem with SQL Native
Posted: Wed Feb 09, 2022 06:26 PM

Oscar:

http://imgur.com

or,

https://imgbb.com/

or

https://uploaddeimagens.com.br

Regards, saludos.

João Santos - São Paulo - Brasil - Phone: +55(11)95150-7341

ORibeiro

Posts: 187

Joined: Mon Oct 20, 2008 06:33 PM

Re: Problem with SQL Native
Posted: Wed Feb 09, 2022 10:10 PM

Thaks João.

Here it are the image:

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

Oscar Ribeiro

OASyS Informática

Fwh18.02 + xHarbour 1.2.3 + Bcc72

ORibeiro

Posts: 187

Joined: Mon Oct 20, 2008 06:33 PM

Re: Problem with SQL Native
Posted: Fri Feb 11, 2022 06:13 PM

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?

Oscar Ribeiro

OASyS Informática

Fwh18.02 + xHarbour 1.2.3 + Bcc72

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Problem with SQL Native
Posted: Fri Feb 11, 2022 06:19 PM

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 (fw): Select all Collapse

#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

Regards

G. N. Rao.

Hyderabad, India

ORibeiro

Posts: 187

Joined: Mon Oct 20, 2008 06:33 PM

Re: Problem with SQL Native
Posted: Mon Feb 14, 2022 07:22 AM

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

Code (fw): Select all Collapse

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

Oscar Ribeiro

OASyS Informática

Fwh18.02 + xHarbour 1.2.3 + Bcc72

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Problem with SQL Native
Posted: Mon Feb 14, 2022 09:54 AM

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 (fw): Select all Collapse

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 (fw): Select all Collapse

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.

Regards

G. N. Rao.

Hyderabad, India

FiveTech Support Forums

Continue the discussion