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

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

Postby dutch » Wed Dec 06, 2017 1:36 pm

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
Regards,
Dutch

FWH 19.01 / xHarbour Simplex 1.2.3 / BCC73 / Pelles C / UEStudio
FWPPC 10.02 / Harbour for PPC (FTDN)
ADS V.9 / MySql / MariaDB
R&R 12 Infinity / Crystal Report XI R2
(Thailand)
User avatar
dutch
 
Posts: 1535
Joined: Fri Oct 07, 2005 5:56 pm
Location: Thailand

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

Postby nageswaragunupudi » Wed Dec 06, 2017 3:01 pm

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.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10206
Joined: Sun Nov 19, 2006 5:22 am
Location: India

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

Postby dutch » Thu Dec 07, 2017 2:31 am

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.
Regards,
Dutch

FWH 19.01 / xHarbour Simplex 1.2.3 / BCC73 / Pelles C / UEStudio
FWPPC 10.02 / Harbour for PPC (FTDN)
ADS V.9 / MySql / MariaDB
R&R 12 Infinity / Crystal Report XI R2
(Thailand)
User avatar
dutch
 
Posts: 1535
Joined: Fri Oct 07, 2005 5:56 pm
Location: Thailand

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

Postby nageswaragunupudi » Thu Dec 07, 2017 5:37 am

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()
 
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10206
Joined: Sun Nov 19, 2006 5:22 am
Location: India

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

Postby dutch » Thu Dec 07, 2017 1:43 pm

Dear Mr.Rao,

Thanks you so much. I will try.

Regards,
Dutch
Regards,
Dutch

FWH 19.01 / xHarbour Simplex 1.2.3 / BCC73 / Pelles C / UEStudio
FWPPC 10.02 / Harbour for PPC (FTDN)
ADS V.9 / MySql / MariaDB
R&R 12 Infinity / Crystal Report XI R2
(Thailand)
User avatar
dutch
 
Posts: 1535
Joined: Fri Oct 07, 2005 5:56 pm
Location: Thailand

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

Postby dutch » Fri Dec 08, 2017 2:56 am

Dear Mr.Rao,
nageswaragunupudi wrote:
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
Regards,
Dutch

FWH 19.01 / xHarbour Simplex 1.2.3 / BCC73 / Pelles C / UEStudio
FWPPC 10.02 / Harbour for PPC (FTDN)
ADS V.9 / MySql / MariaDB
R&R 12 Infinity / Crystal Report XI R2
(Thailand)
User avatar
dutch
 
Posts: 1535
Joined: Fri Oct 07, 2005 5:56 pm
Location: Thailand

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

Postby nageswaragunupudi » Fri Dec 08, 2017 3:24 am

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

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10206
Joined: Sun Nov 19, 2006 5:22 am
Location: India

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

Postby nageswaragunupudi » Fri Dec 08, 2017 5:32 am

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 )
 
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10206
Joined: Sun Nov 19, 2006 5:22 am
Location: India

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

Postby Marc Vanzegbroeck » Sat Dec 09, 2017 8:46 am

Hi,

To know the last inserted ID, you could do
oQry=oSQL:execute("SELECT LAST_INSERT_ID() AS lastinsert")
msginfo(oQry:fields(0):value)
Regards,
Marc

FWH32+xHarbour | FWH64+Harbour | BCC | DBF | ADO+MySQL | ADO+MariaDB | ADO+SQLite
Marc Vanzegbroeck
 
Posts: 1157
Joined: Mon Oct 17, 2005 5:41 am
Location: Belgium

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

Postby nageswaragunupudi » Sat Dec 09, 2017 8:49 am

Marc Vanzegbroeck wrote: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()
 
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10206
Joined: Sun Nov 19, 2006 5:22 am
Location: India


Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: Google [Bot] and 10 guests