// This is a small demo program showing how to use SQLExpress
//-----------------------------------------------------------------------------

#include "sql.ch"
#include "sqlext.ch"

Procedure Main()
   Local i, oConn, oStmt, oCursor, aData[2]

   oConn := SQLConnection():new()

   // display all ODBC data sources available on user's machine and allow user to select one
   if ! oConn:DriverConnect()
      MsgBox("Unable to connect to data source!")
      Return
   endif

   // create a new SQLStatement object
   oStmt := oConn:NewStatement()

   // in case our test table already exists.... let's drop it
   oStmt:SQLString := 'DROP TABLE MyTable'
   oStmt:Execute()

   // this SQL statement will create a brand new table with two fields
   oStmt:SQLString := 'CREATE TABLE MyTable (Field1 char(10), Field2 char(20))'

   if oStmt:Execute() == SQL_XPP_ERROR
      Return
   endif

   // OK! a new table has been created, now insert 10 rows into this table...

   // Here's the SQL statement for inserting the rows:
   oStmt:SQLString := 'INSERT INTO MyTable (Field1, Field2) VALUES (?,?)'

   // Since we're going to execute this statement multiple times, let's prepare it:
   oStmt:Prepare()

   for i := 1 to 10
      aData[1] := LTrim(Str(i))
      aData[2] := Replicate(Chr(64+i),20)
      oStmt:Execute( aData )
   next

   // we don't need the statement object any more, so let's kill it
   oStmt:Destroy()

   MsgBox("A new table with 10 rows has been created!")

   // now create a cursor to 'browse' the new table...
   oCursor := oConn:Cursor("SELECT * FROM MyTable")

   oCursor:Execute()

   if !(oCursor:UsePositionDelete .and. oCursor:UsePositionUpdate)
      // this ODBC driver does not support positioned updates or deletes so we need to define a primary
      // key to ensure that the updates and deletes will be applied to the correct rows in the database
      oCursor:SetPrimaryKey("Field1")
   endif

   // Let's delete all the even numbered rows, and modify the rest
   // Notice that you can use either the cursor column name or column position to access fields

   ? "Here's the contents of the new table...."
   while ! oCursor:eof
      ? oCursor:FieldGet("Field1"), oCursor:FieldGet("Field2")

      if Val(oCursor:FieldGet(1)) % 2 == 0
         // this is an even numbered row... let's delete it
         oCursor:delete()
      else
         // this is an odd number row... let's modify it
         oCursor:FieldPut(2, Left(oCursor:FieldGet(2),7) + ' * updated *')
      endif

      oCursor:skip()
   end

   // now let's insert 5 rows through the cursor

   for i := 1 to 5
      oCursor:Append()
      oCursor:FieldPut("Field1", LTrim(Str(Seconds())) )
      oCursor:FieldPut("Field2", Time())
      oCursor:UpdateRow()
   next

   ? "Here's the contents after a few deletes, updates and inserts...."

   oCursor:Refresh()
   while ! oCursor:eof
      ? oCursor:FieldGet("Field1"), oCursor:FieldGet("Field2")
      oCursor:skip()
   end

   ? "disconnecting..."
   oConn:destroy()    // this will also drop the cursor
   wait

   Return