add recs/repl fields to an existing table of SQL db

[max]

Ado functions are great for accessing SQL (thank you Rao). Import from dbf, export and browse are ok and it run very fast!
Now I need two examples, if possible:
1) how can i add records to an existing table of a database SQL in "batch" mode (not interactively with browse and similar)?
2) how can i replace a content of a field in a specific record of an existing table (like "replace fieldname with variablename" in a dbf)?

Someone have an example to post?

Thank you

[dutch]

Dear Max,

If you use FWMARIADB, you can use oRs:Insert() and oRs:Update() as this post.
http://forums.fivetechsupport.com/viewtopic.php?f=3&t=32657

Ado functions are great for accessing SQL (thank you Rao). Import from dbf, export and browse are ok and it run very fast!
Now I need two examples, if possible:
1) how can i add records to an existing table of a database SQL in "batch" mode (not interactively with browse and similar)?
2) how can i replace a content of a field in a specific record of an existing table (like "replace fieldname with variablename" in a dbf)?

Someone have an example to post?

Thank you

[nageswaragunupudi]

The answer is for ADO.

1) how can i add records to an existing table of a database SQL in "batch" mode (not interactively with browse and similar)?

You can do it in two ways.
(a) Directly using SQL.
(b) Opening recordset in batchmode.

(a) Directly using SQL.
SQL syntax is different for different RDMS like MYSql, MsSql, Oracle, etc. It is also extremely cumborsome to prepare SQL statements. You might have seen lots postings as to how to prepare the sql statement for insert, update, etc.

If you use FWH provided commands and functions there is no scope for any confusion or errors. These commands/functions prepare the sql statements using the syntax applicable to the RDBMS connected by using FW_OpenAdoConnection(), be it MySql, MsSql, Oracle, etc.

This sample demonstrates inserting bulk records using SQL statement. For the purpose of this example, we are using the Demo server of FWH. You may use connecting to your own server later.

Please copy the program to fwh\samples folder and build the exe using buildh.bat or buildx.bat

Code: Select all  Expand view  RUN

#include "fivewin.ch"
#include "adodef.ch"

function Main()

   local oCn, cSql, aData
   local oRs, n

   oCn   := FW_DemoDB( "ADO" ) // You may use your own connection

   // CREATE A TABLE FOR TEST
   TRY
      oCn:Execute( "DROP TABLE instest" )
   CATCH
   END

   FWAdoCreateTable( "instest", { ;
      { "name",  "C", 10, 0 }, ;
      { "amount","N", 10, 2 }, ;
      { "date",  "D",  8, 0 }  }, ;
      oCn )

   // SAMPLE DATA TO BE INSERTED.
   aData := {  ;
      { "David", 2000, Date() - 2000 }, ;
      { "John",  3000, Date() - 1000 }, ;
      { "James", 5000, Date() -  500 }  }

   // This command prepares the actual SQL to be used for insertion
   cSql  := SQL INSERT INTO instest ( name,amount,date ) ARRAY aData

   MEMOEDIT( cSql ) // View the SQL for your information.
   oCn:Execute( cSql ) // insert all records in a single batch
   ? "Inserted"

   // Check if the data is inserted
   oRs   := FW_OpenRecordSet( oCn, "instest" )
   XBROWSER oRs FASTEDIT
   oRs:Close()
   oCn:Close()

return nil
 

b) Using RecordSet opened in Batch mode

Code: Select all  Expand view  RUN

#include "fivewin.ch"
#include "adodef.ch"

function Main()

   local oCn, cSql, aData
   local oRs, n

   oCn   := FW_DemoDB( "ADO" ) // You may use your own connection

   // CREATE A TABLE FOR TEST
   TRY
      oCn:Execute( "DROP TABLE instest" )
   CATCH
   END

   FWAdoCreateTable( "instest", { ;
      { "name",  "C", 10, 0 }, ;
      { "amount","N", 10, 2 }, ;
      { "date",  "D",  8, 0 }  }, ;
      oCn )

   // SAMPLE DATA TO BE INSERTED.
   aData := {  ;
      { "David", 2000, Date() - 2000 }, ;
      { "John",  3000, Date() - 1000 }, ;
      { "James", 5000, Date() -  500 }  }


   ? "Start"
   oRs   := FW_OpenRecordSet( oCn, "instest", adLockBatchOptimistic )
   for n := 1 to Len( aData )
      oRs:AddNew( { "name", "amount", "date" }, aData[ n ] )
   next
   oRs:UpdateBatch() // All records are inserted on the server in one batch
   oRs:Close()
   ? "Inserted"

   // Check if the data is inserted
   oRs   := FW_OpenRecordSet( oCn, "instest" )
   XBROWSER oRs FASTEDIT
   oRs:Close()
   oCn:Close()

return nil
 

[nageswaragunupudi]

2) how can i replace a content of a field in a specific record of an existing table (like "replace fieldname with variablename" in a dbf)?

Using SQL statement

Code: Select all  Expand view  RUN

#include "fivewin.ch"
#include "adodef.ch"

function Main()

   local oCn, cSql, dDate
   local oRs, n

   oCn   := FW_DemoDB( "ADO" ) // You may use your own connection
   dDate := STOD( "20150815" )

   cSql  := SQL UPDATE instest ;
            SET date = dDate  ;
            WHERE id = 2

   ? cSql // view the SQL prepared by the above command
   oCn:Execute( cSql ) // updated

   oCn:Close()

return nil
 

Opening recordset

Code: Select all  Expand view  RUN

#include "fivewin.ch"
#include "adodef.ch"

function Main()

   local oCn, cSql, dDate
   local oRs, n

   oCn   := FW_DemoDB( "ADO" ) // You may use your own connection

   oRs   := FW_OpenRecordSet( oCn, "instest" )
   oRs:MoveNext()
   
   oRs:Date := STOD( "20150815" )
   oRs:Update()
   
   oRs:Close()
   oCn:Close()

return nil
 

[max]

oRs:Date := STOD( "20150815" )
oRs:Update()

Great! Thank you Rao.
With SQL syntax is ok, using Recordset only a correction i had to make compared to the two rows above:

oRs:fields("Date"):value := STOD( "20150815" )
oRs:Updatebatch()

Otherwise it gave me error assigning the new content, and if i don't use :updatebatch() in place of :update() there is no errors but the table does not have the new content. Using mssql db.