Hi,

I'm working from the AppWizard generated project. I want to import a csv

file to two linked tables.

1. Contact table, which has an INTEGER PRIMARY KEY (so it autoincrements)

2. Attribute table, which has a composite PK, including a reference to the

Contact table

Here is what I did so far:

- In biz/Attribute.py , I added 'self.AutoPopulatePK = False'

- My import script looks like this:

> self.bizContact = app.biz.Contact(app.dbConnection)

> self.bizAttribute = app.biz.Attribute(app.dbConnection)

> self.bizContact.addChild(self.bizAttribute)

> for contact in rows:

> self.bizContact.new()

> self.bizContact.setFieldVals(contact)

> self.bizContact.save() # How can I avoid this line?

> contactid = self.bizContact.getPK()

> for attr in attributes:

> self.bizAttribute.new()

> self.bizAttribute.setFieldVals(attr)

> self.bizContact.saveAll()

My problem is that it performs commits at each row, instead of one big

commit at the end of the importation. I tried to remove the save() in the

loop, but then it set Attribute.Contact to a negative ID, which is wrong.

My questions are:

- Am I using the bizObject in the right way?

- How can I insert data in child without commiting the parent first to get

its PK?

Thank you,

*Charles Brunet, B. Ing.*

Webmestre.

Étudiant à la maîtrise, génie informatique, Université Laval.

--- StripMime Report -- processed MIME parts ---

multipart/alternative

text/plain (text body -- kept)

text/html

---

_______________________________________________

Post Messages to: Dabo-users@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users

Searchable Archives: http://leafe.com/archives/search/dabo-users

This message: http://leafe.com/archives/byMID/CAJPgzxmKFw30z+zmP3EdnAD1cJLrrHmtS-9f0Oa7ef-5JKnbvQ@mail.gmail.com

On 5/2/12 6:24 PM, Charles Brunet wrote:

> My problem is that it performs commits at each row, instead of one big

> commit at the end of the importation.I tried to remove the save() in the

> loop, but then it set Attribute.Contact to a negative ID, which is wrong.

The negative id is only temporary. Take the save() out of the loop, and move it to

the end. When save() is called, all the temporary (negative) id's will get filled

from the backend, including the foreign keys in the child table.

Actually, that's how it should work but now that I think about it I'm not sure it

actually works that way. Personally, I generate UUID's for my PK's at the dabo layer

instead of delegating it to the db.

Paul

_______________________________________________

Post Messages to: Dabo-users@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users

Searchable Archives: http://leafe.com/archives/search/dabo-users

This message: http://leafe.com/archives/byMID/4FA1F44D.9080601@ulmcnett.com

2012/5/2 Paul McNett <p@ulmcnett.com>

> On 5/2/12 6:24 PM, Charles Brunet wrote:

> > My problem is that it performs commits at each row, instead of one big

> > commit at the end of the importation.I tried to remove the save() in the

> > loop, but then it set Attribute.Contact to a negative ID, which is wrong.

>

> The negative id is only temporary. Take the save() out of the loop, and

> move it to

> the end. When save() is called, all the temporary (negative) id's will get

> filled

> from the backend, including the foreign keys in the child table.

>

> Actually, that's how it should work but now that I think about it I'm not

> sure it

> actually works that way. Personally, I generate UUID's for my PK's at the

> dabo layer

> instead of delegating it to the db.

>

No, it seems foreign keys aren't updated on the children rows and keep the

temp negative value. I will try to manually set the PK value, as you

suggest. But how can I get the last insert ID? This function exists in the

cursor class, but not in the bizobj class. Or should I query the DB to get

the max value for the PK field? Even that I'm not sure how to do it.

Charles.

--- StripMime Report -- processed MIME parts ---

multipart/alternative

text/plain (text body -- kept)

text/html

---

_______________________________________________

Post Messages to: Dabo-users@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users

Searchable Archives: http://leafe.com/archives/search/dabo-users

This message: http://leafe.com/archives/byMID/CAJPgzxmvsUWwi84OHs8NvSAL0Q23zoN3QdX9yJWTYU-FrHVsVw@mail.gmail.com

On May 3, 2012, at 7:09 AM, Charles Brunet wrote:

> No, it seems foreign keys aren't updated on the children rows and keep the

> temp negative value. I will try to manually set the PK value, as you

> suggest. But how can I get the last insert ID? This function exists in the

> cursor class, but not in the bizobj class. Or should I query the DB to get

> the max value for the PK field? Even that I'm not sure how to do it.

That should be done for you if you have a) AutoPopulatePK set to True for the parent bizobj and b) FillLinkFromParent set to True for the child bizobj(s). When those are both set, saving a new parent record will propagate the new parent PK to the child bizobj's foreign key field.

-- Ed Leafe

_______________________________________________

Post Messages to: Dabo-users@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users

Searchable Archives: http://leafe.com/archives/search/dabo-users

This message: http://leafe.com/archives/byMID/CE833CA0-7815-4A5D-9721-E939C50A29FD@leafe.com

Ok, it works now! For the records, here is how I did:

- In biz/Attribute.py:

> self.AutoPopulatePK = False

> self.FillLinkFromParent = True

> self.LinkField = "Contact"

- My import script looks like this:

> self.bizContact = app.biz.Contact(app.dbConnection)

> self.bizAttribute = app.biz.Attribute(app.dbConnection)

> self.bizContact.addChild(self.bizAttribute)

> for contact in rows:

> self.bizContact.new()

> self.bizContact.setFieldVals(contact)

> for attr in attributes:

> self.bizAttribute.new()

> self.bizAttribute.setFieldVals(attr) # Don't touch to 'Contact'

> field here

> self.bizContact.saveAll()

Thank you for your help,

Charles.

--- StripMime Report -- processed MIME parts ---

multipart/alternative

text/plain (text body -- kept)

text/html

---

_______________________________________________

Post Messages to: Dabo-users@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users

Searchable Archives: http://leafe.com/archives/search/dabo-users

This message: http://leafe.com/archives/byMID/CAJPgzxnmVNUD0wV0EK3axAzr40dYuo7wYA99YZNSABS=RXJc-g@mail.gmail.com

On 5/3/12 5:09 AM, Charles Brunet wrote:

> I will try to manually set the PK value, as you

> suggest. But how can I get the last insert ID? This function exists in the

> cursor class, but not in the bizobj class. Or should I query the DB to get

> the max value for the PK field? Even that I'm not sure how to do it.

You can't ask the database for the last insert id until the database has inserted the

record. The database won't insert the record until you call biz.save(), which you

don't want to do inside the loop. You also don't want to guess at what the database

will assign for the key because another user could have beaten you to it, among other

reasons.

Do you want me to tell you how to use uuid's for your key fields? This would require

redefining the database to use char(40) keys and not auto-increment.

Paul

_______________________________________________

Post Messages to: Dabo-users@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users

Searchable Archives: http://leafe.com/archives/search/dabo-users

This message: http://leafe.com/archives/byMID/4FA2BAA8.6080004@ulmcnett.com

2012/5/3 Paul McNett <p@ulmcnett.com>

> On 5/3/12 5:09 AM, Charles Brunet wrote:

> > I will try to manually set the PK value, as you

> > suggest. But how can I get the last insert ID? This function exists in

> the

> > cursor class, but not in the bizobj class. Or should I query the DB to

> get

> > the max value for the PK field? Even that I'm not sure how to do it.

>

> You can't ask the database for the last insert id until the database has

> inserted the

> record. The database won't insert the record until you call biz.save(),

> which you

> don't want to do inside the loop. You also don't want to guess at what the

> database

> will assign for the key because another user could have beaten you to it,

> among other

> reasons.

>

> Do you want me to tell you how to use uuid's for your key fields? This

> would require

> redefining the database to use char(40) keys and not auto-increment.

Well, I misunderstood what you meant by UUID. Now I understand.

What I was looking for was to get the last insert id before importing the

data, and then simply to increment the id for each imported row. But Ed's

solution is simpler, safer, and better.

Charles.

--- StripMime Report -- processed MIME parts ---

multipart/alternative

text/plain (text body -- kept)

text/html

---

_______________________________________________

Post Messages to: Dabo-users@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users

Searchable Archives: http://leafe.com/archives/search/dabo-users

This message: http://leafe.com/archives/byMID/CAJPgzx=qvyOkrO3a5WUV=eHr0ebVmYq6UOgX=otBe4QZW81uPg@mail.gmail.com

On 5/3/12 10:39 AM, Charles Brunet wrote:

> What I was looking for was to get the last insert id before importing the

> data, and then simply to increment the id for each imported row. But Ed's

> solution is simpler, safer, and better.

Yes, that was the feature I'd mentioned but then couldn't remember if we'd actually

implemented it that way. I'm glad it just worked!

Paul

_______________________________________________

Post Messages to: Dabo-users@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users

Searchable Archives: http://leafe.com/archives/search/dabo-users

This message: http://leafe.com/archives/byMID/4FA2C59E.7080801@ulmcnett.com

On Thursday, May 03, 2012 10:04:40 AM Paul McNett wrote:

> You can't ask the database for the last insert id until the database has

> inserted the record. The database won't insert the record until you call

> biz.save(), which you don't want to do inside the loop. You also don't want

> to guess at what the database will assign for the key because another user

> could have beaten you to it, among other reasons.

FYI - postgres does not work as you suggest.

You are able to retrieve the PK without commit (within the same session).

So if I insert a record I can retrieve the PK without a commit.

see Currval()

Johnf

_______________________________________________

Post Messages to: Dabo-users@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users

Searchable Archives: http://leafe.com/archives/search/dabo-users

This message: http://leafe.com/archives/byMID/4914167.CYJvVD1Zxy@linux-12

On 5/3/12 11:02 AM, John Fabiani wrote:

> On Thursday, May 03, 2012 10:04:40 AM Paul McNett wrote:

>> You can't ask the database for the last insert id until the database has

>> inserted the record. The database won't insert the record until you call

>> biz.save(), which you don't want to do inside the loop. You also don't want

>> to guess at what the database will assign for the key because another user

>> could have beaten you to it, among other reasons.

>

> FYI - postgres does not work as you suggest.

> You are able to retrieve the PK without commit (within the same session).

>

> So if I insert a record I can retrieve the PK without a commit.

We were talking about retrieving the pk before biz.save(), which is before the

database knows anything about the new record.

Paul

_______________________________________________

Post Messages to: Dabo-users@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users

Searchable Archives: http://leafe.com/archives/search/dabo-users

This message: http://leafe.com/archives/byMID/4FA2C8E3.4030106@ulmcnett.com