problem with SQL INSERT [solved]

[MOISES]

Hi,

I can´t compile this code:

Code: Select all

   

LOCAL cTabla      := company("ITEMS")

  aSql := SQL INSERT INTO &cTabla ( CODIGO, DESCRIPCIO, IMPORTE ) VALUES ( 'C01', 'CONCEPTO PARA PRUEBAS', 12.12 )
   SQLEXEC( aSQL )
 

Thank you.

[nageswaragunupudi]

Code: Select all

LOCAL cTabla      := company("ITEMS")

Please change it as

Code: Select all

PRIVATE cTabla      := company("ITEMS")

[MOISES]

Sorry, not working.

[MOISES]

Any fix, please?

[Jack]

Hi,
With MSSQL i do this :

csql:="INSERT INTO "+(cTabla)+ " (PATID,LASTNAME) VALUES ("
cSql:=cSql+" 'C01', 'CONCEPTO PARA PRUEBAS'+" )"

TRY
oCon:Execute(cSql)
CATCH oErr
ShowAdoEr( oCon,csql )
END TRY

Hope it will help !

Philippe

[MOISES]

Thank you.

The advantage of such command is that it handles the sintax for every Database. So the same code will work under MSSQL, MySQL, Oracle...

[mgsoft]

Maybe this could be:

Code: Select all

#xtranslate ADO_SQL UPDATE  [TABLE <tbl> ] SET [ <col1> = <val1> [,<colN> = <valN> ] ] [ WHERE <*cwhere*> ]  => ;
   "UPDATE " + <tbl> + " SET " + ;
   FW_ArrayAsList( \{  FW_QuotedColSQL( <"col1"> ) + " = " +  FW_ValToSQL( <val1> ) ;
   [,  FW_QuotedColSQL( <"colN"> ) + " = " + FW_ValToSQL( <valN> )  ] \} ) [ + " WHERE " + CharRepl( '"', <"cwhere">, "'", .t. ) ]

[nageswaragunupudi]

Sorry, not working.

Code: Select all

   local cSql
   local cName    := "John"
   local cCity    := "New York"
   local dHireDate:= Date() - 1200
   local nSalary  := 123023.75

   PRIVATE cTable := "customer"

   cSql := SQL INSERT INTO &cTable ( FIRST, CITY, HIREDATE, SALARY ) VALUES ( cName, cCity, dHireDate, nSalary )
   ? cSql
 

INSERTING MULTIPLE ROWS:

Code: Select all

   local cSql
   local aData    := ;
      {  { "John",  "New York", Date() - 1200, 23465.55 } ;
      ,  { "Jimmy", "Sydney",   Date() - 2000, 32000.00 } ;
      ,  { "David", "Manila",   Date() - 4000, 54234.76 } ;
      }

   PRIVATE cTable := "customer"

   cSql := SQL INSERT INTO &cTable ( FIRST, CITY, HIREDATE, SALARY ) ARRAY aData
   ? cSql
 

[MOISES]

Hi,

Hope you are doing well.

There is a typo on calling TABLE. I fixed as follows:

#xtranslate SQL INSERT INTO [TABLE <tbl> ] ( <cols,...> ) ARRAY <a> => ;
"INSERT INTO " + <tbl> + " ( " + FW_QuotedColSQL( #<cols> ) + " ) " + " VALUES " + ;
StrTran( StrTran( FW_ValToSql( AClone( <a> ) ), '( (', '(' ), ') )', ')' )

It now works with local variables.

[nageswaragunupudi]

But we can not change because that breaks the existing code many users are already using.

[MOISES]

Thank you.

I will create my own function with regular INSERT.

How can I transform all the data in an array into VALUES with FW_ValToSQL?

[nageswaragunupudi]

Please study the existing translates in the fwsqlcmd.ch

[MOISES]

Thank you. I used FW_AdoApplyParams.

I would like to take this opportunity to thank you for the enormous usefulness of the ADO functions you have created, which make our work much easier. Congratulations once again.