FWHMYSQL oRs:Save() Problem

[vilian]

I'm having a problem with oRS:Save(). Please, look at this sample:

Code: Select all  Expand view  RUN

  cWhere    := oBD:ApplyParams("WHERE esc.cemp = ? ", { "018"})
   oRS   := oBD:Query( "SELECT * FROM esocial AS esc ? ORDER BY data,hora", { cWhere } )

   oRs:status := 3
   oRs:Save()

When I perform oRS:Save() is generating the log bellow that I do not understand.

28/03/2018 13:54:00: FWMARIACONNECTION:EXECUTE_SQL( 5369 ) CallStack() = " <- FWMARIACONNECTION:EXECUTE(5609) <- FWMARIAROWSET:RESYNC(3380) <- FWMARIAROWSET:EDITBASERECORD(4186) <- (b)SFP213(72) <- TBUTTONBMP:CLICK(179)" cSql = "SELECT * FROM esocial AS esc WHERE `esc`.`CEMP` = '018' AND `esc`.`DATA` = '2018-03-28' AND `esc`.`HORA` = '10:46WHERE esc.cemp = '018' 3' AND `esc`.`TIPO` = 'S1030' AND `esc`.`CAMPO` = '10200513' AND ( esc.cemp = '018' ) ORDER BY data,hora" uRet = ::nError = 1064 ::cError = "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '018' 3' AND `esc`.`TIPO` = 'S1030' AND `esc`.`CAMPO` = '10200513' AND ( esc.cem' at line 1" ::cSqlInfo = ""

I Observed there are two clausules WHERE in sql expression ! Could you help me ?

[nageswaragunupudi]

Can you prepare a small test program using any one of the tables on the FW demo server?
If you run fwh\samples\maria01.prg, you see how to connect to the demo server and also see all the tables on the server.
If you can make such a sample, that will be useful for all users.

We may start with a sample like this

Code: Select all  Expand view  RUN

#include "fivewin.ch"

function Main()

   local oCn, oRs, cWhere
   local cLog  := cFileSetExt( ExeName(), "log" )
   
   oCn      := FW_DemoDB()

   oCn:lLogErr := .t.

   cWhere   := oCn:ApplyParams( "WHERE state = ?", { "NY" } )
   oRs      := oCn:RowSet( "SELECT * FROM customer ? ORDER BY city", { cWhere } )

   ? oRs:age
   oRs:age  += 1
   oRs:Save()
   ? oRs:age

   oRs:Close()
   oCn:Close()

   if File( cLog )
      WinExec( "notepad.exe " + cLog )
   endif

return nil

and modify it to reproduce the error.

[vilian]

There is no problem. How could I upload a table to this server ?

[nageswaragunupudi]

Yes, you can.

[vilian]

Here is It:

Code: Select all  Expand view  RUN

#include "fivewin.ch"

FUNCTION Main()

   local oCn, oRs, cWhere,oBrw,oDlg,cSql
   local cLog  := cFileSetExt( ExeName(), "log" )

   oCn      := FW_DemoDB()

   oCn:lLogErr := .t.

   cWhere   := oCn:ApplyParams("WHERE esc.cemp = ? ", { "018"})
   cSql     := "SELECT * FROM esocial AS esc ? ORDER BY data,hora"
   oRs      := oCn:RowSet( cSql,  { cWhere } )

   IF oRs == nil
      ? "Failed to open rowset"
   ELSE
      ? oRs:Source, oRs:aParams[ 1 ], oRs:cResyncSQL

      DEFINE DIALOG oDlg SIZE 800,400 PIXEL TRUEPIXEL
         @ 60,20 XBROWSE oBrw SIZE -20,-20 PIXEL OF oDlg DATASOURCE oRs AUTOCOLS AUTOSORT NOBORDER
         oBrw:CreateFromCode()

         @ 20,20 BUTTON "TEST" SIZE 100,30 PIXEL OF oDlg ;
            ACTION oRs:EditBaseRecord(NIL,.F.,{|oRec| TestSave(oRec)},oBrw)

      ACTIVATE DIALOG oDlg CENTERED

   ENDIF

   oRs:Close()
   oCn:Close()

   ? "done"

   IF File( cLog )
      WinExec( "notepad.exe " + cLog )
   ENDIF

RETURN nil

STATIC FUNCTION TestSave(oRec)

      if oRec:status == 3
         oRec:status := 2
      else
         oRec:status := 3
      endif

      MsgRun( "Saving", "Wait", { || oRec:Save() } )

RETURN .T.

 

Here is the log file:

05/03/18 14:31:41: FWMARIACONNECTION:EXECUTE_SQL( 5369 ) CallStack() = " <- FWMARIACONNECTION:EXECUTE(5609) <- FWMARIAROWSET:RESYNC(3380) <- FWMARIAROWSET:EDITBASERECORD(4186) <- (b)MAIN(54) <- TBUTTON:CLICK(179)" cSql = "SELECT * FROM esocial AS esc WHERE `esc`.`CEMP` = '018' AND `esc`.`DATA` = '2018-02-26' AND `esc`.`HORA` = '11:36WHERE esc.cemp = '018' 8' AND `esc`.`TIPO` = 'S1010' AND `esc`.`CAMPO` = '005' AND ( esc.cemp = '018' ) ORDER BY data,hora" uRet = ::nError = 1064 ::cError = "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '018' 8' AND `esc`.`TIPO` = 'S1010' AND `esc`.`CAMPO` = '005' AND ( esc.cemp = '' at line 1" ::cSqlInfo = ""

Please, Look at this - The error does not happens every time, I had to click in button test three or four times to get it.

[vilian]

Did you make any progress ?

[nageswaragunupudi]

We are testing. We'll get back to you.

[vilian]

+1

[vilian]

This problem is disturbing my customers, because the record is saved, but the xbrowse is not updated.
Please, help me !!!

[nageswaragunupudi]

Please wait a little

[nageswaragunupudi]

This is happening in a very rare case where one of the fields in primary key is a "time" field and its value contains ":1" and the rowset is opened with parameters.

A primary-key having such a field is very uncommon but in this case, primary-key is a composite key with one of the fields a time string.

This is because just like "?", ":n" and "&n" are also considered as replaceable parameters for application of parameters.

In such a rare case we suggest this approach:

Instead of:

Code: Select all  Expand view  RUN

  cWhere   := oCn:ApplyParams("WHERE esc.cemp = ? ", { "018"})
   cSql     := "SELECT * FROM esocial AS esc ? ORDER BY data,hora"
   oRs      := oCn:RowSet( cSql,  { cWhere } )
 

Use:

Code: Select all  Expand view  RUN

  cWhere   := oCn:ApplyParams("WHERE esc.cemp = ? ", { "018"})
   cSql     := "SELECT * FROM esocial AS esc ? ORDER BY data,hora"
   cSql     := oCn:ApplyParams( cSql, { cWhere } )
   oRs      := oCn:RowSet( cSql )
 

[nageswaragunupudi]

From 18.04 we stopped considering :1, :2, ....:n as variables for substitution in ApplyParams() to avaoid confusion when the string contains values like "10:10:20".
&1, &2, ...&3 can still be used as in Dolphin.

This should resolve Mr. Vilian's issue.

[vilian]

Thank you