Posts: 838

Joined: Wed Aug 22, 2007 10:09 AM

problem with SQL INSERT [solved]
Posted: Fri Mar 20, 2020 09:18 AM

Hi,

I can´t compile this code:

   

LOCAL cTabla      := company("ITEMS")

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

Thank you.

Saludos / Regards,

FWH 20.04, Harbour 3.2.0 dev (r1909261630) y BCC 7.40

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: problem with SQL INSERT
Posted: Fri Mar 20, 2020 09:57 AM

Code (fw): Select all Collapse

LOCAL cTabla      := company("ITEMS")

Please change it as

Code (fw): Select all Collapse

PRIVATE cTabla      := company("ITEMS")

Regards

G. N. Rao.

Hyderabad, India

MOISES

Posts: 838

Joined: Wed Aug 22, 2007 10:09 AM

Re: problem with SQL INSERT
Posted: Fri Mar 20, 2020 10:09 AM

Sorry, not working.

Saludos / Regards,

FWH 20.04, Harbour 3.2.0 dev (r1909261630) y BCC 7.40

MOISES

Posts: 838

Joined: Wed Aug 22, 2007 10:09 AM

Re: problem with SQL INSERT
Posted: Wed Mar 25, 2020 09:53 AM

Any fix, please?

Saludos / Regards,

FWH 20.04, Harbour 3.2.0 dev (r1909261630) y BCC 7.40

Jack

Posts: 300

Joined: Wed Jul 11, 2007 11:06 AM

Re: problem with SQL INSERT
Posted: Wed Mar 25, 2020 11:27 AM

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

Posts: 838

Joined: Wed Aug 22, 2007 10:09 AM

Re: problem with SQL INSERT
Posted: Wed Mar 25, 2020 11:39 AM

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...

Saludos / Regards,

FWH 20.04, Harbour 3.2.0 dev (r1909261630) y BCC 7.40

mgsoft

Posts: 422

Joined: Mon Aug 17, 2009 12:18 PM

Re: problem with SQL INSERT
Posted: Sat Mar 28, 2020 06:25 PM

Maybe this could be:

Code (fw): Select all Collapse

#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. ) ]

Saludos,

Eduardo

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: problem with SQL INSERT
Posted: Tue Mar 31, 2020 07:10 AM

MOISES wrote:Sorry, not working.

Code (fw): Select all Collapse

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

   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

Regards

G. N. Rao.

Hyderabad, India

MOISES

Posts: 838

Joined: Wed Aug 22, 2007 10:09 AM

Re: problem with SQL INSERT
Posted: Wed Apr 01, 2020 11:04 AM

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.

Saludos / Regards,

FWH 20.04, Harbour 3.2.0 dev (r1909261630) y BCC 7.40

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: problem with SQL INSERT
Posted: Wed Apr 01, 2020 11:20 AM

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

Regards

G. N. Rao.

Hyderabad, India

MOISES

Posts: 838

Joined: Wed Aug 22, 2007 10:09 AM

Re: problem with SQL INSERT
Posted: Wed Apr 01, 2020 12:02 PM

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?

Saludos / Regards,

FWH 20.04, Harbour 3.2.0 dev (r1909261630) y BCC 7.40

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: problem with SQL INSERT
Posted: Wed Apr 01, 2020 01:19 PM

Please study the existing translates in the fwsqlcmd.ch

Regards

G. N. Rao.

Hyderabad, India

MOISES

Posts: 838

Joined: Wed Aug 22, 2007 10:09 AM

Re: problem with SQL INSERT
Posted: Wed Apr 01, 2020 03:59 PM

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.

Saludos / Regards,

FWH 20.04, Harbour 3.2.0 dev (r1909261630) y BCC 7.40

FiveTech Support Forums

xtranslate SQL INSERT INTO [TABLE <tbl> ] ( <cols,...> ) ARRAY <a> => ;

Continue the discussion