FWMARIADB:copy record. How can it know the dest. record id?

[dutch]

Dear Master,

I try to copy from one to another by

Code: Select all  Expand view

oCn:CopyTable('orgTable','desTable', , ,'id=1')

Code: Select all  Expand view

aRow := oCn:QueryResult('select * from '+cTable+' where '+cWhere )
oCn:Insert( cTable, nil, aRow )
 

How do I know the 'id' of destination record?

Thanks in advance.
Dutch

[nageswaragunupudi]

Let us consider this:

Code: Select all  Expand view

aRow := oCn:QueryResult('select * from '+cTable+' where '+cWhere )
oCn:Insert( cTable, nil, aRow )
 

I understand you are reading one or more rows from a source table and trying to insert into a destination table and also that both tables have the same structure with ID as autoinc primary key.

aRow contains ID values as recorded in the source table.

If the destination table does not contain these IDs the rows are inserted with the same ID in the destination table also.

Problem arises when the destination table already contains some rows with the same IDs.

In such cases of conflict, by default, the operation results in error and aborts from that point.

We can use the 4th logical parameter to decide what to do in case of conflict, i.e., if the ID already exists in the destination table.

Set the 4th optional parameter lUpdate to .T.:
In this case, if the ID already exists in the destination table, the same row is updated with the values from the source and otherwise the rows are inserted retaining the same ID as in the source.

Set the 4th parameter to .F.: ( ignore duplicates )
In this case rows with duplicate IDs are skipped without raising error and other rows are inserted retaining the same ID as in the source.

If you let me know what exactly you want to do, I can suggest how you can do that.

[dutch]

Dear Master,

My purpose is to write some data in a new records after ::copytable().
Example
==========
I need to copy the hotel reservation of a guest and change some data in a new record such as New Arrival date, New Departure date for next arrival period.
How can I search or filter the new record. The method ::GoBottom() is not 100% corrected way.

I have a question.

Code: Select all  Expand view

aFields := oCn:TableStructure(tablename) -> array
aRows  := oCn:QueryResult('select * from '+tablename) -> array

Is it returning same structure and sequence?

If so, I will use oRs:Append( aFields[n][1], aRows ) and then I can edit and change the new record after oRs:Append()

Thank you in advance.

[nageswaragunupudi]

I have a question.

Code: Select all  Expand view

aFields := oCn:TableStructure(tablename) -> array
aRows  := oCn:QueryResult('select * from '+tablename) -> array
 

Is it returning same structure and sequence?

Yes

Please see this example

Code: Select all  Expand view

  oRs   := oCn:RowSet( "customer" )
   oRs:Goto( 10 )  // we want to copy this rec and append
   aRow  := oRs:CopyRec()
   ADel( aRow, 1, .t. ) // ID field. We need to remove it
   oRs:Append( aRow )
   oRs:Edit()
 

[dutch]

Dear Mr.Rao,

Thanks you so much. I will try.

Regards,
Dutch

[dutch]

Dear Mr.Rao,

Please see this example

Code: Select all  Expand view

  oRs   := oCn:RowSet( "customer" )
   oRs:Goto( 10 )  // we want to copy this rec and append
   aRow  := oRs:CopyRec()
   ADel( aRow, 1, .t. ) // ID field. We need to remove it
   oRs:Append( aRow )
   oRs:Edit()
 

oRs:Append() still have "id" columns (autoinc) in structure. It will not append new record.
If I adel( :aStructure ) with "id" element and use as below, it work.
oRs:Append( aStructure /* without "id" column */, aRow )

Is it correct way?

Regards,
Dutch

[nageswaragunupudi]

The code I posted removes ID field.
Please try my code exactly without changes

[nageswaragunupudi]

This is made even much simpler in the next version (17.12)

Code: Select all  Expand view

oRs:GoTo( n )
aRow := oRs:CopyRec()
oRs:Append( aRow )
 

[Marc Vanzegbroeck]

Hi,

To know the last inserted ID, you could do

oQry=oSQL:execute("SELECT LAST_INSERT_ID() AS lastinsert")
msginfo(oQry:fields(0):value)

[nageswaragunupudi]

Hi,

To know the last inserted ID, you could do

oQry=oSQL:execute("SELECT LAST_INSERT_ID() AS lastinsert")
msginfo(oQry:fields(0):value)

It is a lot simpler with FWMARIADB.
Simply

Code: Select all  Expand view

? oCn:InsertID()