Duplicating a record in MySQL /Xojo

What is the best way to duplicate a record (But increment the ID field ) in MySQL?

Get the recordset and iterate through the fields of that recordset and grab the name, value, field type and use a DatabaseRecord to insert an additional one. Make sense?

FWIW, the record ID field should be something the database does and NOT you.

Thanks Bob. I am close. The following code gets a duplicate record and ignores ant filed called “ID” But if it hits a NULL value it craps out. I can seem to figure out how to check for NULL in the data (Ill bet if you use Pictures or BLOBS or other exotic data it will fail also)

[code]Sub DB_DuplicateRecord(myTable as String, myID as integer)

dim rec as new DatabaseRecord
dim myInsertID as integer =-1
[Database].InsertRecord myTable, rec
if [Database].Error then
//Handle Error
else
myInsertID= [Database].GetInsertID
[Database].Commit
end if

Dim FieldsRS As RecordSet
FieldsRS = [Database].FieldSchema (myTable)

Dim rsNew, rsOld as RecordSet
dim stmt as string
dim myRecCount as integer
stmt=“Select * From “+myTable+” WHERE ID =”+str(myInsertID)+";"
rsNew=[Database].SQLSelect(stmt)
stmt=“Select * From “+myTable+” WHERE ID =”+str(myID)+";"
rsOld=[Database].SQLSelect(stmt)
if rsNew=nil then
//Handle Error
else
myRecCount=rsNew.recordcount
if myRecCount <>1 then
//Handle Error
else
Dim FieldName as string
If FieldsRS <> Nil Then
rsNew.edit
While Not FieldsRS.EOF
FieldName=FieldsRS.IdxField(1).StringValue
IF FieldName <> “ID” THEN
If rsOld.field(FieldName) = Nil then //<----------This is the offending line!

          rsNew.field(FieldName).stringvalue=rsOld.field(FieldName).stringvalue
          
        end if
      end if
      FieldsRS.MoveNext
    Wend
    rsNew.Update
    If [Database].Error then
        //Handle Error
    end if
  End If
end if

end if

FieldsRS.Close
rsNew.close
rsOld.close

End Sub
[/code]

I’d use an insert with a select totally in mysql
no need to drag all the data down & put it all back

Thanks Norman But I since XOJO is not tightly coupled with the DB I have to code hunt if I add a field.

never do this by code its not only slow but also full of errors when running on concurrent / multi-user enviroment. Always let the DBMS do this kind of work for you… just run this query with SQLExecute, no hassle!

INSERT contacts (Shortname, Company, City, Street) SELECT Shortname, Company, City, Street FROM contacts WHERE Identifier = X

Tomas:

Help me understand:

1, How slow can a single record duplication be?

  1. If Im acuity calling session.datatbase where can the corruption come in?

FWIW This seems to work:

[code]Sub DB_DuplicateRecord(myTable as String, myID as integer)

dim rec as new DatabaseRecord
dim myInsertID as integer =-1
[Database].InsertRecord myTable, rec
if [Database].Error then
//Handle Error
else
myInsertID= [Database].GetInsertID
[Database].Commit
end if

Dim FieldsRS As RecordSet
FieldsRS = [Database].FieldSchema (myTable)

Dim rsNew, rsOld as RecordSet
dim stmt as string
dim myRecCount as integer
stmt=“Select * From “+myTable+” WHERE ID =”+str(myInsertID)+";"
rsNew=[Database].SQLSelect(stmt)
stmt=“Select * From “+myTable+” WHERE ID =”+str(myID)+";"
rsOld=[Database].SQLSelect(stmt)
if rsNew=nil then
//Handle Error
else
myRecCount=rsNew.recordcount
if myRecCount <>1 then
//Handle Error
else
Dim FieldName as string
If FieldsRS <> Nil Then
rsNew.edit
While Not FieldsRS.EOF
FieldName=FieldsRS.IdxField(1).StringValue
IF FieldName <> “ID” THEN
If (rsOld.field(FieldName).value = nil) then
rsNew.field(FieldName).stringvalue=rsOld.field(FieldName).stringvalue
end if
end if
FieldsRS.MoveNext
Wend
rsNew.Update
If [Database].Error then
//Handle Error
end if
End If
end if
end if

FieldsRS.Close
rsNew.close
rsOld.close

End Sub
[/code]

Interesting. I would not expect this to work. First time I’ve seen someone be able to insert data via the Recordset object.

I will reiterate, again, that if you are managing the database record id yourself you are doing something horribly, horribly wrong and are setting yourself up for failure in the long run.

Im not maintaining the ID
That is why: IF FieldName <> “ID” THEN

Im also creating the record with ONLY the ID field filled by the DBMS

then I reopen it to fill it in.

[quote=159979:@Jay Menna]Im not maintaining the ID
That is why: IF FieldName <> “ID” THEN

Im also creating the record with ONLY the ID field filled by the DBMS

then I reopen it to fill it in.[/quote]

you’re chasing a misconception… think twice…

Q: “What is the best way to duplicate a record (But increment the ID field ) in MySQL?”
A: INSERT MyTable (Fieldlist-comma-seperated) SELECT Fieldlist-comma-seperated FROM MyTable WHERE ID = myID

From StackOverflow, this answer might be simpler and keeps the work on the DB server:

CREATE TEMPORARY TABLE tmptable_1 SELECT * FROM table WHERE primarykey = 1; UPDATE tmptable_1 SET primarykey = NULL; INSERT INTO table SELECT * FROM tmptable_1; DROP TEMPORARY TABLE IF EXISTS tmptable_1;

[quote=159983:@Paul Lefebvre]From StackOverflow, this answer might be simpler and keeps the work on the DB server:

CREATE TEMPORARY TABLE tmptable_1 SELECT * FROM table WHERE primarykey = 1; UPDATE tmptable_1 SET primarykey = NULL; INSERT INTO table SELECT * FROM tmptable_1; DROP TEMPORARY TABLE IF EXISTS tmptable_1;[/quote]

…ja but for this you need sufficient rights (CREATE) granted. ALTER , DROP and CREATE are the first priviledges I am always removing before letting any user in.

In my example you just take all fields in the fieldlist without the auto-incremented ID value. The new recordset gets a new ID automatically by DBMS…

I use something like this :

ch = "insert into "+aTableName+" ("+colsexceptpk+")" ch = ch + " select "+colsexceptpk+" from "+aTableName+" where "+pk+"='"+str(aRowID)+"'" VNSGetDatabase( aDBIndex).SQLExecute( ch)

where colsexceptpk is a list of all fields names except the primarykey field separated by columns
and pk is the field of the primarykey, and arowid the rowid of the record we want to duplicate
the database does the autoincrement itself.
works fine till now.

One of my constrains is I don’t know is a field has been added to the DB. There fore listing the field in the SQLis not an option .

jean-yves has an interesting solution here as does Paul

Thanks All. Now I have to do is test them…

i use this code to duplicate x number of records.


  dim rs1 as RecordSet, rec as DatabaseRecord
  dim cnt as integer
  dim totcnt as integer
  dim fldcnt as integer
  dim totfldcnt as integer
  dim fldname as String
  
  totcnt=val(Num2Duplicate.text)
  rs1=DoDbOpenRS("SELECT * FROM tblStock WHERE StockID = " + DuplicateID.text )
  
  IF rs1<>nil THEN
    totfldcnt=rs1.FieldCount
    FOR cnt=1 to totcnt
      rec = New DatabaseRecord
      FOR fldcnt=1 to totfldcnt
        fldname=rs1.IdxField(fldcnt).Name
        SELECT CASE fldName
        CASE "StockID" 
        CASE ELSE
          rec.column(fldname)=rs1.Field(fldname).value
        END SELECT
      next fldcnt
      Call DoDBInsertRec("tblStock",rec)

    NEXT cnt
    
    
  END IF

[quote=160462:@Jay Menna]One of my constrains is I don’t know is a field has been added to the DB. There fore listing the field in the SQLis not an option .

jean-yves has an interesting solution here as does Paul

Thanks All. Now I have to do is test them…[/quote]
my answer is almost the same as thomas’…:wink:

.SQLSelect("SHOW COLUMNS FROM " + myTable + ";")

will get you a list of field names.

is there something similar for SQLite??

PRAGMA table_info(table_name);

http://www.sqlite.org/pragma.html#pragma_table_info