On Friday 23 February 2007 20:18, Ken Dibble wrote:
> In my application, I'm faced with many situations where I have to store
> many-to-many data in link tables. In order for this to make sense to the
> user, I'm going to have to "translate" the FKs in these link tables to
> something meaningful both when displaying data and saving entries back to
> the database. You know:
>
> Table books
> id I, ;
> title C(100)
>
> Table authors
> id I, ;
> author C(100)
>
> Table bookauthors
> id I, ;
> bookid I, ;
> authorid I
>
> In VFP I can do (code off the top of my head, leaving out all the OO
> stuff):
>
> FUNCTION GetBookAuthor(nPK)
>
> SELECT books.title, authors.author FROM books ;
> JOIN bookauthors ON bookauthors.id = nPK ;
> JOIN authors ON authors.id = bookauthors.authorid ;
> WHERE books.id = bookauthors.bookid ;
> INTO CURSOR myData
>
> SELECT myData
>
> MyForm.MyTextbox1.Value = myData.title
> MyForm.MyTextbox2.Value = myData.author
>
> A common scenario, for sure--but one for which I can't find Dabo demo code.
>
> I've been trying to wade through the classes and code for a while now to
> figure this out but I just can't find it. I don't want to use any of the
> stock forms. I want, for now, to create a simple form so I can understand
> how to call a custom query of my database for a particular purpose.
>
> I have a form with a spinner, a button, and a textbox. When I choose a
> number in the spinner, representing a PK value, I want to be able to press
> the button and have a value from a record with that PK show up in the
> textbox.
>
> It seems to me that in order to do this, I need a biz object that manages
> the table, with a method like:
>
> getValForPK(self,pk=0)
> self.AddWhere("id = " + str(pk))
>
> self.Requery()
>
> # somehow here I access the returned dataset and pluck out the field
> value
> # using a split function I imagine....
>
> return myValue
>
> Then my button's onHit can do (yes, I know it should call a form method
> that does the following):
>
> self.Form.MyTextbox.Value =
> self.Form.MyBizObj.getValForPK(pk=self.Form.mySpinner.value)
>
> That's as far as I'm able to get. Can someone fill in the blanks?
>
> Thanks very much.
>
> Ken Dibble
Let me say I don't completely understand but I'll give a go by looking at your
code.
With respect to the VFP sql statement:
You can send any SQL statement to your backend by issuing a execute("select
statement). However, Ed has suggested that is not the correct/best/proper
way. The preferred way is to issue a requery(). So let's set it up do it
using the requery().
First you need your "Bizobj". To get that you code:
myBizO = self.Form.getBizobj("theTableName")
Now you can set the attribute "UserSQL" to the select statement like
myBizO.UserSQL = "select something from someTable where order by etc.."
or
myBizO.UserSQL = ("select %s from %s where order by etc.." % (myparms,))
Now issue:
myBizO.requery()
So you see I setup any select statement I need with above routine.
Now what your doing is sightly different. You just want to keep the Bizobj
dataset as is but want only sightly different info each time. So to do that
all that is needed is to change the 'Where' statement.
Your statement below is almost right:
self.AddWhere("id = " + str(pk))
But "AddWhere()" adds to the where statement and each call will add
more 'Where' statements.
You want
self.biz.setWhereClause()
this will reset the 'Where' everytime you want to change. Make note that I
used the BizObj to access the method.
As far as the PKid and the many to many stuff I'm not to sure what you mean.
It appears that you have the tables so what is the issue?
BTW the business objects need to contain the fields required. So in your
business objects you need to the fields even if you are not going to display
them. See what the ClassDesigner provides for business objects.
I think you first play with the info I have provided and then move on to
setting the many to many relationships.
Also what DB are you using?
--
John Fabiani
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
