SQL insert DateTime

[Otto]

Hello,
can some help me please.
I need to insert from a dbf file into a SQL database.
I need following format: YYYY-MM-DD 12:00:00
My code inserts YYYY-MM-DD 00:00:00.
Now I thought of adding 12 hours in a second task but maybe there is an easier solution.
Thank you in advance
Otto

Code: Select all  Expand view

do while .not. eof()
      oRs:Append( { "start","end", "name" },;
         { planner->start , planner->ENDE, planner->TEXT} )
      skip
   enddo

[Otto]

Hello,
now I tried with:
tStart := FW_ADDTIME( planner->start, "12" )
tEnde :=FW_ADDTIME( planner->ende, "12" )
and this is working for me.
Best regards
Otto

Code: Select all  Expand view

 oRs      := oCn:reservations
   do while .not. eof()
   tStart := FW_ADDTIME( planner->start, "12" )
  tEnde :=FW_ADDTIME( planner->ende, "12" )
      oRs:Append( { "start","end", "name","room_id","status","paid" },;
         { tStart, tEnde, planner->TEXT, planner->ROOM, "planner->STATUS", planner->IS_PAID } )

      skip
   enddo

 

[nageswaragunupudi]

It is safer to use full "HH:MM:SS" in FW_AddTime() function.

Your code above works, where we open the table "reservations" as rowset oRs and append record by record.

Recommended alternative, without opening the table as rowset, is to insert one or more records directly into the table using:

Code: Select all  Expand view

oCn:Insert( cTable, acFields, aData, [lUpdate] )
 

aData can be one record or multiple records.

Example:

Code: Select all  Expand view

PLANNER->(DBGOTOP())

aData := PLANNER->( FW_DbfToArray( ;
         "FW_ADDTIME(START,'12:00:00'),FW_ADDTIME(ENDE,'12:00:00'),TEXT,ROOM,STATUS,IS_PAID" ) )

oCn:Insert( "reservations", "start,end,name,room_id,status,paid", aData )
 

This method can be used not only to insert new records (single or bulk), but also for updating existing records with latest information as well as insert-cum-update.

For example, let us assume that "room_id" is either primary key or Unique key field in the "reservation" table. Then,

Code: Select all  Expand view

oCn:Insert( "reservations", "start,end,name,room_id,status,paid", aData, .T. )
// OR
oCn:Upsert( "reservations", "start,end,name,room_id,status,paid", aData )
 

updates the data where room_id already exists and inserts where room_id does not already exist.

This is very useful for refreshing the data on the mysql server with latest additions and modifications in the dbf table.

The Insert/Upsert method is generic. There is a specific method for uploading data from dbf to mysql table.

Code: Select all  Expand view

oCn:UploadFromAlias( cSqlTable, cDbfFieldList, cSqlFieldList )
 

In the above case of reservations, we can export the data with one single statement:

Code: Select all  Expand view

PLANNER->(DBGOTOP())

PLANNER->( oCn:UploadFromAlias( "reservations", ;
         "FW_ADDTIME(START,'12:00:00'),FW_ADDTIME(ENDE,'12:00:00'),TEXT,ROOM,STATUS,IS_PAID" ), ;
         "start,end,name,room_id,status,paid" ) )
 

In case "room_id" is a primary key or unique key, the data will be updated or inserted as required.