Grabar en tablas relacionadas en Mysql

[acuellar]

Amigos

No funciona el Save() cuando las tablas están relacionadas con INNER JOIN.

Code: Select all  Expand view

oPDET:=oServer:Query( "SELECT P.*,concat_ws('',TRIM(D.APEPAT),' ',TRIM(D.APEMAT),' ',TRIM(D.NOM1),' ',D.NOM2) AS APENOM FROM PLANIDET AS P INNER JOIN DATPER AS D ON D.IDEMPL=P.IDEMPL ORDER BY APENOM FOR UPDATE")


   oCol:=oBrw:AddCol()
   oCol:bEditValue:= { || oPDET:IVAACTUAL }
   oCol:cHeader:= "RC-IVA"
   oCol:nWidth:=53
   oCol:nEditType:=EDIT_GET
   oCol:bOnPostEdit:={|o, v, n| (oPDET:IVAACTUAL:=v,oPDET:Save()) } //Esto no funciona. Cómo se hace?

 //Esto tampoco funciona
  oSQL:=oPDET:GetRowObj()
   DEFINE DIALOG oDlg3 RESOURCE "VARIABLES" TITLE 'Variables'; oDlg3:lHelpIcon:=.F.
     REDEFINE GET oSQL:DTRAB   ID 103 OF oDlg3 UPDATE PICTURE "99.9"
     REDEFINE GET oSQL:HEXTR   ID 104 OF oDlg3 UPDATE PICTURE "999.9"
     REDEFINE GET oSQL:HRECN   ID 105 OF oDlg3 UPDATE PICTURE "999.9"

     REDEFINE BUTTON oBtn ID 120 OF oDlg3 ACTION ( oSQL:Save(),oSQL:Refresh(),oDlg3:End() )
     REDEFINE BUTTON oBtn ID 121 OF oDlg3 ACTION (oDlg3:End())

     ACTIVATE DIALOG oDlg3 CENTERED

 

Creo que voy a tener que usar UPDATE PLANIDET SET con el que permite grabar

Gracias por la ayuda

Saludos,

Adhemar

[jnavas]

Amigos

No funciona el Save() cuando las tablas están relacionadas con INNER JOIN.

Code: Select all  Expand view

oPDET:=oServer:Query( "SELECT P.*,concat_ws('',TRIM(D.APEPAT),' ',TRIM(D.APEMAT),' ',TRIM(D.NOM1),' ',D.NOM2) AS APENOM FROM PLANIDET AS P INNER JOIN DATPER AS D ON D.IDEMPL=P.IDEMPL ORDER BY APENOM FOR UPDATE")


   oCol:=oBrw:AddCol()
   oCol:bEditValue:= { || oPDET:IVAACTUAL }
   oCol:cHeader:= "RC-IVA"
   oCol:nWidth:=53
   oCol:nEditType:=EDIT_GET
   oCol:bOnPostEdit:={|o, v, n| (oPDET:IVAACTUAL:=v,oPDET:Save()) } //Esto no funciona. Cómo se hace?

 //Esto tampoco funciona
  oSQL:=oPDET:GetRowObj()
   DEFINE DIALOG oDlg3 RESOURCE "VARIABLES" TITLE 'Variables'; oDlg3:lHelpIcon:=.F.
     REDEFINE GET oSQL:DTRAB   ID 103 OF oDlg3 UPDATE PICTURE "99.9"
     REDEFINE GET oSQL:HEXTR   ID 104 OF oDlg3 UPDATE PICTURE "999.9"
     REDEFINE GET oSQL:HRECN   ID 105 OF oDlg3 UPDATE PICTURE "999.9"

     REDEFINE BUTTON oBtn ID 120 OF oDlg3 ACTION ( oSQL:Save(),oSQL:Refresh(),oDlg3:End() )
     REDEFINE BUTTON oBtn ID 121 OF oDlg3 ACTION (oDlg3:End())

     ACTIVATE DIALOG oDlg3 CENTERED

 

Creo que voy a tener que usar UPDATE PLANIDET SET con el que permite grabar

Gracias por la ayuda

Saludos,

Adhemar

Colega,
Vi que tienes una consulta con columna concatenadas, que deseas guardar?
En mi caso todos los ejemplos los desarollo con HeidySql

[cmsoft]

Efectivamente Adhemar, si usas Dolphin, no puedes usar Save cuando la consulta no es una tabla simple, debes hacerlo con UPDATE o creando una nueva Query que contenga una sola tabla con las condiciones que deseas.

[jnavas]

Efectivamente Adhemar, si usas Dolphin, no puedes usar Save cuando la consulta no es una tabla simple, debes hacerlo con UPDATE o creando una nueva Query que contenga una sola tabla con las condiciones que deseas.

Colega
Puedes utilizar

LOCAL cSql:="UPDATE XTABLE SET CAMPO="+uValue

IF !oDB:EXCECUTE(cSql)
? "NO SE PUEDO MODIFICAR"
ENDIF

Ok

[nageswaragunupudi]

With ADO we can edit and save fields in the main table, provided we include primary key also in the sql while reading the recordset. Same way we can edit and save fields in the joined table also if we include primary of key of the joined table also in the sql.

This is my small test.
oCn is my ADO connection object to my MYSQL on localhost. I have all tables in \fwh\samples exported to my MYSQL server.

Code: Select all  Expand view

   cSql := "SELECT c.ID, C.FIRST, C.LAST, C.STATE, C.CITY, S.ID AS IDS, S.CODE, S.NAME FROM CUSTOMER C INNER JOIN STATES S ON C.STATE = S.CODE"


   oRs   := FW_OpenRecordSet( oCn, cSql )

   xbrowser oRs FASTEDIT

 

I am able to comfortably edit all fields of customer table and states table inline and they are saved automatically.

One important condition: The SQL should contain primary keys of tables, whether we browse or not.

Incidentally I tested the same code above on MsAccess, MsSql ( sqlexpress ), Sqlite3, MySql and OracleExpress without any changes and this worked exactly the same with all servers.

I do not know about TDolphin. May be it may work if you include primary key in your query. I do not know.

[acuellar]

Gracias amigos

He tenido que hacerlo así para evitar otra consulta:

Code: Select all  Expand view

  oCol:=oBrw:AddCol()
   oCol:cEditPicture:="99,999.99"
   oCol:bEditValue:= { || oPDET:CONSUMO }
   oCol:nDataStrAlign := AL_RIGHT
   oCol:cHeader:= "CONSUMO"
   oCol:nWidth:=63
   oCol:nEditType:=EDIT_GET
   oCol:bOnPostEdit:={|o, v, n| GrabaCONSUMO(v) }

//...
Function GrabaConsumo(v)
      oPDET:oServer:BeginTransaction()
      nRowID:=oPDET:Sql_rowid
      cQry:="UPDATE PLANIDET SET "
      cQry+="CONSUMO = " + ClipValue2SQL(v)
      cQry+=" WHERE sql_rowid = " + ClipValue2SQL(nRowID)
      oPDET:oServer:SqlQuery( cQry )
      oPDET:oServer:CommitTransaction()
      oPDET:Refresh()
Return Nil
 

Saludos,

Adhemar

[Maurizio]

Hello Rao

I am able to comfortably edit all fields of customer table and states table inline and they are saved automatically.

One important condition: The SQL should contain primary keys of tables, whether we browse or not.

Is it possible to create a primary key in the function FWAdoCreateTable( ) ?

it would be very nice .

Maurizio
www.nipeservice.com

[nageswaragunupudi]

Hello Rao

I am able to comfortably edit all fields of customer table and states table inline and they are saved automatically.

One important condition: The SQL should contain primary keys of tables, whether we browse or not.

Is it possible to create a primary key in the function FWAdoCreateTable( ) ?

it would be very nice .

Maurizio
http://www.nipeservice.com

By default the function creates an auto-increment primary key, unless we set the 4th parameter to False.

FWAdoCreateTable( cTable, aCols, oCn, lAddAutoInc )

By default, lAddAutoInc is true.
aCols is an array like DBF structure ( though we can use ado field type constants also)
If aCols does not contain a field with type "+", if lAddAutoInc is nil or true, the function adds a first column with name ID which is AutoIncrement field ( appropriate to the database )

So, by default all tables created with FWH functions and all tables imported from DBF using FWH functions have an autoincrement primary key "ID" and this feature makes the database safe.

[Maurizio]

Thank Rao for your explanation ,

I mean a case like this

aDbf := {}
AADD (aDbf, {"PROG", "N", 5 , 0 })
AADD (aDbf, {"NAME", "C", 30 , 0 })
FWAdoCreateTable( "test" , aDbf , m->oServer, .t. )

and create with FWAdoCreateTable() a primary key on the file PROG directly , now Is possible with FW_AdoCreateIndex() .

Thanks
Maurizio

[nageswaragunupudi]

We are going off-topic for this post. Better you create another thread for similar topics.

FWAdoCreateTable( "test", { { "PROG", "N", 5, 0 }, { "NAME", "C", 30, 0 } }, oCn )
inserts one auto-increment column ID as the first column and as primary key.
The resultant table will have 3 columns ID,PROG,NAME with ID as auto-increment as primary key.

FWAdoCreateTable( "test", { { "PROG", "+", 5, 0 }, { "NAME", "C", 30, 0 } }, oCn )
Treats the first column PROG as auto-increment primary key column.
The resultant table will have 2 columns PROG,NAME with PROG as auto-increment primary key

It is possible to define your own primary keys like in the 2nd case.
But going by "good practices", I suggest the first alternative of creating a primary key which is not relevant to the user and not maintained by programmer.

This is the function to create indexes.
FW_AdoCreateIndex( oCn, cTable, cIndexName, aIndexCols, lUnique )

Example: FW_AdoCreateIndex( oCn, "CUSTOMER", "CUSTOMER_NAME_IDX", { "FIRSTNAME", "LASTNAME" }, .t. )

But I advise you not to create any indexes. It is not necessary in most cases. We can discuss in greater detail in a separate thread if you like.

[Maurizio]

Thank Rao ,
I agree .

Maurizio