Page 1 of 1
problem with SQL INSERT [solved]
Posted: Fri Mar 20, 2020 9:18 am
by 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.
Re: problem with SQL INSERT
Posted: Fri Mar 20, 2020 9:57 am
by nageswaragunupudi
Please change it as
Code: Select all
PRIVATE cTabla := company("ITEMS")
Re: problem with SQL INSERT
Posted: Fri Mar 20, 2020 10:09 am
by MOISES
Sorry, not working.
Re: problem with SQL INSERT
Posted: Wed Mar 25, 2020 9:53 am
by MOISES
Any fix, please?
Re: problem with SQL INSERT
Posted: Wed Mar 25, 2020 11:27 am
by 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
Re: problem with SQL INSERT
Posted: Wed Mar 25, 2020 11:39 am
by 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...
Re: problem with SQL INSERT
Posted: Sat Mar 28, 2020 6:25 pm
by 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. ) ]
Re: problem with SQL INSERT
Posted: Tue Mar 31, 2020 7:10 am
by nageswaragunupudi
MOISES wrote: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
Re: problem with SQL INSERT
Posted: Wed Apr 01, 2020 11:04 am
by 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.
Re: problem with SQL INSERT
Posted: Wed Apr 01, 2020 11:20 am
by nageswaragunupudi
But we can not change because that breaks the existing code many users are already using.
Re: problem with SQL INSERT
Posted: Wed Apr 01, 2020 12:02 pm
by 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?
Re: problem with SQL INSERT
Posted: Wed Apr 01, 2020 1:19 pm
by nageswaragunupudi
Please study the existing translates in the fwsqlcmd.ch
Re: problem with SQL INSERT
Posted: Wed Apr 01, 2020 3:59 pm
by 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.