topic-36428

Board index FiveWin for Harbour/xHarbour SQL insert DateTime

Posts: 6983

Joined: Fri Oct 07, 2005 07:07 PM

SQL insert DateTime
Posted: Fri Dec 07, 2018 10:52 AM

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

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

Otto

Posts: 6983

Joined: Fri Oct 07, 2005 07:07 PM

Re: SQL insert DateTime
Posted: Fri Dec 07, 2018 11:18 AM

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

 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

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: SQL insert DateTime
Posted: Fri Dec 07, 2018 07:11 PM

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

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

aData can be one record or multiple records.

Example:

Code (fw): Select all Collapse

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

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

oCn:UploadFromAlias( cSqlTable, cDbfFieldList, cSqlFieldList )

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

Code (fw): Select all Collapse

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.

Regards

G. N. Rao.

Hyderabad, India

FiveTech Support Forums

Continue the discussion