modes for import

[ajith]

Hi,

I made some changes to Import – to support different modes.
If mode is specified as update, I want the records already in the table (as indicated by the Key) to be replaced by the presumably newer records in the import source.
If append is the mode, the duplicate records in the import source should not over write the records already present.
If afresh is the mode, all records existing in the table should be deleted and only those in the import source should be kept.
If force is the mode, then all records in the import source should be imported even if duplicate record exists in the table.

I needed only update and append modes. I have included “force” as it is the mode existing in the current code. I thought of ‘afresh’ only for completion sake.
After changing the New method to accept mode and defining .Mode, I changed the Output method to

Output(x)

{

if (.Mode is "update" )

{

if (.t isnt false)

.t.Complete()

QueryDelete(.To_query, x)

.t = Transaction(update:)

.q = .t.Query(.To_query)

}

// start a new transaction every 100 records

if (.N++ % 100 is 0)

{

if (.t isnt false)

.t.Complete()

.t = Transaction(update:)

.q = .t.Query(.To_query)

}

count = 0

forever

{

try

{

.q.Output(x)

break

}

catch (err, "*duplicate key")

{

if .Mode is "force"

.Make_unique(x, err)

if .Mode is "append"

{

//	.t.Complete()

break

}

}

if (++count > 100)

throw "error during import"

}

}

for mode ‘afresh’ the code is at the end of New method as

if .Mode is "afresh"

QueryDo( 'delete ' $ .To_query)

I found the code above after some trial and error, without really understanding it, but apparently is working as I want it. But, I don’t know much about Transactions, so want opinion on correctness of the code.

In particular,

1) .q being Transaction.Query, I couldn’t find any document on Transaction.Query.Output() method. Are there other sub-methods for Transaction.Query?

2) Why is it that I have to call .t.Complete() before I call QueryDelete in case of update mode? The .t.Query().Output() has not been called yet; so how does a conflict arise between transaction that is outputting the record and that QueryDelete is trying to delete?

3) When the mode is append, why is there no need to Complete the transaction? Trying to do that gives an error, but works apparently fine without it.

Thanks,
ajith

[ajith]

Hi,

Missed this post?
Waiting for your comments.
ajith

[j_ferguson]

Hi Ajith,

Look in the User’s Manual under Database > Reference > Transaction, and Database > Reference > Query.

When you do a request like QueryDelete or QueryDo, it is using a brand new transaction even though it isn’t completely apparent. When you do this and the transaction overlaps with your .t transaction you open up opportunity for transaction conflicts. Without getting into all the details about what exactly causes conflicts, I would suggest you try to use the transaction you already have rather that starting new ones to do requests. Again, if you look at the references in the User’s Manual above you will hopefully find methods you can use within the transaction (.t) that you have rather than doing QueryDelete or QueryDo.

Hope that helps

[ajith]

Hi Jeff,

Thanks. I will read the User Manual and get back.
ajith

[Author]

Posts