Cursor Adapters and TableUpdates... 1

[stanlyn]

Hi,

I'm trying to understand the usage of cursor adapters in VFP9sp2 and am having some issues...

I have the adapter created and attached to a form with an edit box bound to one of its fields... It is populating as expected.

Next I modified the data in the editbox and cannot get it to update the actual sql table.

Here is the setup properties:

Tag = "Driver={SQL Server};Server=DAS; Integrated Security=SSPI; Database=DEEDROOM;"
Top = 20
Left = 10
Height = 90
Width = 91
SelectCmd = select Page.county_pagekey, Page_Type, BOOK_NUM, Page_Prefix, Page_Num, SubPage_Num, Page_Date, IMAGE_PATH, ocr_text1, ocr_text2, Page.identity_id from Page where left(Page.county_pagekey,5)=?gcCounty
UpdateCmd = update Page set Page.ocr_text2 = crsPage.ocr_text2 where Page.identity_id = crsPage.identity_id
AllowUpdate = .T.
CursorSchema = COUNTY_PAGEKEY C(14), PAGE_TYPE C(40), BOOK_NUM C(24), PAGE_PREFIX C(24), PAGE_NUM C(12), SUBPAGE_NUM C(4), PAGE_DATE C(27), IMAGE_PATH C(146), OCR_TEXT1 M, OCR_TEXT2 M, IDENTITY_ID I
Alias = "crsPage"
DataSourceType = "ODBC"
Flags = 0
FetchAsNeeded = .F.
FetchSize = 10
MaxRecords = 15
WhereType = 2
KeyFieldList = IDENTITY_ID
Tables = Page
UpdatableFieldList = OCR_TEXT2
UpdateNameList = OCR_TEXT2 Page.ocr_text2, IDENTITY_ID Page.IDENTITY_ID
Name = "caPage"

The docs says that I need to have an update command which I do.

1. Do I still need to issue a TableUpdate(1, .T., 'crsPage') even though I have the editbox control bound to crsPage?

2. Why is the TableUpdate command operating on the VFP cursor named "crsPage" and not the underlying SQL table which is "Page"?

I'm getting errors like;
Connectivity error: [Microsoft][ODBC SQL Server Driver][SQL Server] The multi-part identifier "crsPage.identity_id" cound not be bound. This occurs when I move off the changed record, at which my choices are revert.

Batch update is set to "1".

I've read Jordanovia's excellent CursorAdapter article and reviewed her examples that she presented at SouthWest 2011, and I'm using it as a guide, but still falling short....

Any suggestions?

Thanks, Stanley

 

[Olaf Doschke]

1. Yes, Tableupdate is needed. Cursoradapters work like Views, which need a tableupdate. If you create the CA cursor with row buffering implict updates are deon.

2. The connection to the sql table is done by the cursoradapter object, you work on the cursor, that's what an adapter is, it adapts to the sql server table as a cursor you work on.

The error you get is unclear, you set updatenamelist correct, alias is "crsPage", so crsPage.IDENTITY_ID maps to page.IDENTITY_ID. But if this is an integer identity field you can't write to it, like you can't write to a foxpro autoinc integer field, those values are generated by sql server. You rather would go for uniqueidentifier as primary key, then you can also generate c(37) values on the foxpro side and make this keyfield an updatable field.

Bye, Olaf.

 

[stanlyn]

Hi Olaf,

The only field that is updateable is OCR_TEXT2 Page.ocr_text2, while the IDENTITY_ID Page.IDENTITY_ID fields were added as the docs says the primary key must be added to this list, and that is what the identity_id field is (primary int).

Also, when a TableUpdate() is issued, what is getting updated, the cursor or the sql table?

Is there ever a need to update the cursor?

Thanks, Stanley

 

[Olaf Doschke]

If you TableUpdate(1, .T., 'Viewname'), what is updated? The view or the underlying table? The table.

Tableupdate(1,.t.,'tablename') is the exception to the rule, if you only buffer the table, but don't use a view or remoteview or spt cursor or cursoradapter to adapt to a table.

Tablupdate() of course updates the table, but through something else in the normal case. Something having an foxpro alias loaded into a workarea, that's that third parameter, not the underlying table.

the docs says the primary key must be added to this list.

Where do they say that and what do they say exactly?

There is a difference of setting a field keyfield and setting it updatable. In the case I already gave as alternative a uniqueidentifier field can be both primary and updatable. An integer identity can't be updatable.

If you use the cursoradapter builder, the auto update tab offers auto-update and if you check that you can independently specify which field(s) are/is key and which is updatable. And this goes into a) Keyfieldlist b) Updatablefieldlist and c) Updatename list. a) is independant on b) and c), every field in b) must be in c) too. The builder does this for you, just specify key and/or updatable. But you will never overcome an int identity to not be updatable, you can specify it in CA, but it will never work.

Bye, Olaf.

 

[stanlyn]

Olaf,

>> Where do they say that and what do they say exactly?

Source: #1

http://www.redware.com/handbooks/vfpcursoradaptor/vfpcursoradaptor_cursoradaptor.html

loPubs.TABLES='authors'
loPubs.KEYFIELDLIST='au_id'
loPubs.UPDATABLEFIELDLIST='au_lname,au_fname'
* Primary key must be defined in updatenamelist
loPubs.UPDATENAMELIST = 'au_id authors.au_id, au_lname authors.au_lname, au_fname authors.au_fname'
BROWSE TITLE 'au_lname and au_fname are updatable'
? TABLEUPDATE(.T.,.T.)

Note: The primary key fields must be specified in the UPDATENAMELIST property to allow the update commands to be automatically created by the system. They do not need to be updatable if the key values are automatically generated on the server when adding a new record.

Source #2:

http://www.devx.com/codemag/Article/15699/1954

The final page (labeled "Auto Update") configures how changes are applied to the base table(s) of the class. For the most basic updates, choose the "Auto-update" and "Update all fields" checkboxes. This will direct the CursorAdapter class to automatically build the appropriate Update, Insert or Delete statements for any changes that are made to the data in the cursor. However, you must still choose the primary key field(s) for the cursor, so that these statements know how to uniquely identify the base table records. For this example, the CustomerID field is the primary key, so check the box in the column under the key.

Source #3:
Whats New in Visual FoxPro 8

KeyFieldList, Tables, UpdatableFieldList, and UpdateNameList
These properties serve the same purpose as the identically-nam ed CURSORSETPROP() properties for views. They are required if you want VFP to automatically update the data source with changes made in the cursor. KeyFieldList is a comma-delimited list of fields (without aliases) that make up the primary key for the cursor. Tables is a comma-delimited list of tables the cursor is based on. UpdatableFieldList is a comma-delimited list of fields (without aliases) that can be updated. UpdateNameList is a comma-delimited list that matches field names in the cursor to field names in th e table. The format for UpdateNameList is as follows: CURSORFIELDNAME1 TABLE.FIELDNAME1, CURSORFIELDNAME2
TABLE.FIELDNAME2, ... Note that even if UpdatableFieldList doesn’t contain the name of the primary key of the table (because you don’t wa nt that field updated), the primary key must still be included in UpdateNameList or updates won’t work.

Thanks Olaf!
Stanley

 

[stanlyn]

Olaf,

>> Tablupdate() of course updates the table, but through something else in the normal case. Something having an foxpro alias loaded into a workarea, that's that third parameter, not the underlying table.

So, when issuing a TableUpdate that updates the remote SQL table, which should I use, TableUpdate() or TableUpdate(1,.T.,'localcursor'), or would they both do the same thing, less what the first 2 params do?


>> There is a difference of setting a field keyfield and setting it updatable. In the case I already gave as alternative a uniqueidentifier field can be both primary and updatable. An integer identity can't be updatable.

I understand that my primary key identity_id (int) is not updatable, nor would I want to, and that when creating a new record, SQL Server will automatically assign this to it. Is this correct?


>> If you use the cursoradapter builder, the auto update tab offers auto-update and if you check that you can independently specify which field(s) are/is key and which is updatable. And this goes into a) Keyfieldlist b) Updatablefieldlist and c) Updatename list. a) is independant on b) and c), every field in b) must be in c) too. The builder does this for you, just specify key and/or updatable. But you will never overcome an int identity to not be updatable, you can specify it in CA, but it will never work.

Yes, I understand this...

Thanks,
Stanley

 

[Olaf Doschke]

Stanyln, you are right about the Primary Key Field being part of the UpdateName list, it's just not part of the UpdatableFieldlist, if you only tick "key", but not "updatable".

I tend to forget this, as this is all done correctly by the builders.

Makes sense, the CA will need to info about cursor field name and remote name for any field it needs to address, not only updatable fields, but also keyfield(s).

Bye, Olaf.

 

[stanlyn]

Olaf,

Great, now I'm wondering about this... If I construct the SeleCmd statement via the builder and save it appears ok. However when I hack the .scx the data has a lot of foreign chars. Here are the results:

Tag = "Driver={SQL Server};Server=DAS; Integrated Security=SSPI; Database=DEEDROOM;"
Top = 20
Left = 10
Height = 90
Width = 91
SelectCmd =




































































































































































































































































































































































































































































































































303select county_pagekey, Page_Type, BOOK_NUM, Page_Prefix, Page_Num, SubPage_Num, Page_Date, IMAGE_PATH, ocr_text1, ocr_text2, identity_id from Page where left(Page.county_pagekey,5)=?gcCounty .and. Page.page_type = ?gcPageType .and. Page.book_num = ?gcBookNum .and. Page.page_num >= ?str(gnStartPage,6,0)
CursorSchema = COUNTY_PAGEKEY C(14), PAGE_TYPE C(40), BOOK_NUM C(24), PAGE_PREFIX C(24), PAGE_NUM C(12), SUBPAGE_NUM C(4), PAGE_DATE C(27), IMAGE_PATH C(146), OCR_TEXT1 M, OCR_TEXT2 M, IDENTITY_ID I
Alias = "crsPage"
DataSourceType = "ODBC"
UpdateCmdDataSource = "Driver={SQL Server};Server=DAS; Integrated Security=SSPI; Database=DEEDROOM;"
UpdateCmdDataSourceType = "ODBC"
Flags = 0
FetchSize = 25
MaxRecords = 50
WhereType = 3
KeyFieldList = IDENTITY_ID
Tables = Page
UpdatableFieldList = OCR_TEXT2
UpdateNameList = OCR_TEXT2 Page.ocr_text2, IDENTITY_ID Page.IDENTITY_ID
BreakOnError = .T.
UseCursorSchema = .T.
Name = "caPage"

You are asking "why are you hacking the .scx", its because the form will no longer load complaining about record #13 which is unrelated...

I can close the hack without removing of the extras, and open the builder and the extras chars are not shown. I've removed the extra chars several times and if I run the builder, it puts it back in. What is correct, a valid sql ststement or one that is prepended with a lot of extra non ascii chars (maybe extended chars)?

Is using the .AND. and .OR. notation used in the builders sql commands (the periods).

Thanks,
Stanley

Any ideas?
Stanley

 

[Olaf Doschke]

No idea, I never hacked an scx including a Ca, I create VCXes for CA classes, for reusability and inheritance this is the only way to go, don't add CAs to form DEs and work on them there, this is fruitless, as this concrete CA only is used within that form and you would need to redo everything in the next form.

Besides that you dont' use .AND. and .OR for remote database access, you use the sql dialect of the remote database. You can use .AND. and .OR with DBFs only, but this is legacy, you can also use AND and OR in VFP and I would put away with the dots. The expression 1=0 for false or .F. and 1=1 for True or .T. also is better in that sense, as that works with any database. MSSQL for example knows false and true keywords, but you cannot select ... WHERE false, you need to write WHERE 1=0 - (for exampe to get an empty resultset having the structure of the remote table to append to it).

Bye, Olaf.

Bye, Olaf.

 

[stanlyn]

New problem,

Currently I created a new DE class that contains 2 cas. Originally, the DE did not have a datasource, as the datasource was defined in each CA.

I've now changed that the other way around by checking the "Use DE Source" checkbox in each of the CAs and removed all datasource references in all the other places, like the optional update, insert and delete pages. The same datasource is now defined in the DE.

Problem is the vfp select 'alias' is now failing with object not found or something like that. And of-course the "replace name with alias.name" type commands all fail too.

1. Wonder what went wrong? Any addition switches that needs turned on? All of the other CAs definations are still in place, its that I only removed the datasources via the builder.

2. What is best practice in where the data sources go?

3. In VFP, I can use the "USE" command so I can view live data sources... How do I get that functionality when using CAs?

4. Shouldn't I be able to select table1, or select table2, append blank, replace, browse and etc, to this data?

Thanks,
Stanley

 

[Olaf Doschke]

The problem probably is no datasource is set at the DE level and if, DE.AutoOpenTable is maybe not set .T. and/or AutoOpen Method of CAs does not do or cause a CursorFill, so no aliases are created. Eg Buidlder does insert code, that makes AutoOpen call Init(), if the status is not set to having generated and filled the CA cursor.

And no, there is no USE of remote tables. You can test SQL using the SQL Server Management Studio and see if that fails and you can look into SQL Server data with the mnagement studio. But if CAs worked before the problem most probably is the connection only.

Did you set up DE datasource in any way? There is no automatism doing that. If you let CA classes rely on DE.DatSource, this has to be set eg in DEClass Init().

I'd suggest you have central code connecting to the Server and storing a handle in goApp.nSQLServerCommandhandle, then you can set DE.DataSourceType = ODBC and DE.Datasource = goApp.nSQLServerCommandhandle or all CA classes. Indeed there is no need to connect with any CA, this can be centralised even more general than on the DE level this way.

Bye, Olaf.

 

[Olaf Doschke]

Overall, you go too many steps in one go, concentrate on one aspect first. I'd suggeest you get a CA going on the command line without any DE, you can start with a CA class in a VCX, use the builder to setup connection and build query etc. then in the command window do
Set Procedure To your.vcx and o=Createobject("myCA") and see, if the cursor is filled, if you can use the browse window to edit and delete and add data and use Tableupdate(1,.f."alias") to save.

Then you can inspect nondefault values of the DE Class in the property window and inspect code generated by the builder to learn and understand principles, then put some of the code not specific to the sample table into a CA base class and build your own way of doing things this way...

In the first place you don't need any DE to develop and test a CA and a baseclass.

Bye, Olaf.

 

[Olaf Doschke]

...I meant Set Classlib, not Set Procedure, of course...

 

[stanlyn]

Hi Olaf,

>> Overall, you go too many steps in one go,

!!! amen brother !!!


The problem is there are too many options and ways of doing this. One tutorial creates the CA as part of a form with it embedded. Others say create a DE class and do it from there, and others say other ways... (you get the point).

I'm looking for a way that is most flexible for use in forms, reports and programs, and where I can change any aspect of it in a central program.prg file. I normally use builders... All the other ways I mentioned earlier just seems like a lot of work to initially create and a nightmare to maintain...

I'm really interested in how you are doing it as it looks like what I'm looking for, if I can just get a handle on it.

The good news is that I'm learning a lot about this subject which will be useful later, I'm sure... (the dos and do'nts, the use and stay away from)


>> I'd suggest you have central code connecting to the Server and storing a handle in goApp.nSQLServerCommandhandle

Like, in the apps main.prg, I put the connection defination setup and calls and save the connection handle to a public variable that will be used as long as the app is running.

And add a connection disconnect in my shutdown routine.

?? But, wouldn't it be better on the fly create a connection, get the data, and close the connection? Just seems like keeping the connection open for the duration of the app session is NOT a good thing... Is this correct, and if so, then putting this in the main.prg is a bad thing...

I'm thinking of having a procedure in my masterproc.prg file for creating the connection and one for closing it... Then I can open and close at will... Are you good with this? Would I be missing something here that I don't know to ask?

Just for clarity,

1. If the app uses 10 tables/cursors, would there be 10 CAs?

2. Each CA represents only a single result set, whether it is a single table or a joined set of tables?

3. Are you avocating the use of only CAs instead of DEs? If not, where would you use each?

4. In a form that displayed customers and orders, normally I'd create a CA whose SelectCmd would include a join for both the customers and orders tables.

5. And in line 4 above, issuing a replace on both customer.field1 and orders.field1 would automatically update the sql table?

Thanks, Stanley

 

[Olaf Doschke]

It's always nice to have numbers to reference answers to questions. Way to go.

0. (the question about always staying connected): You can do that, I do it.

As a connection can be shared, so all your queries using the same connection handle run in parallel on sql server and queries of other clients also run, perhaps sharing that connection, while you don't use it. So you don't use up many connections (a limit in SQLExpress for example). In contrast each CA having it's own sqlstringconnect() is opening a connection per CA.

1. There could be more, there could be less.

Most probably you will have 1 CA for each table for simply accessing that table records purely and several more for resultsets joining data. But in case you have several tables 1:1 related, you perhaps would do one CA for them with a JOIN to treat these tables as one.

Take an internationalisation example:

You might split one table:
Languages(id int, languagecode char(5), languagename varchar(50))

And instead have two tables:
Languages(id int, languagecode char(5))
Langaugenames(id int, languageid int, inlangugageid int, languagename varchar(50)

This way you can store the language names in different languages, eg (de-DE) german, deutsch, allemand and (fr-FR) french, französisch, français. The language codes are in the head table, as they don't differ in each language, but the language has a different name in each language.

Then you can have a CA querying a language list in one language, eg

SELECT Langages.id, Langages.countrycode, Languagenames.languagename FROM Languages Left JOIN Languagenames On Languagenames.languageid = Languages.id and Languagenames.inlanguageid = ?goApp.iCurrentlanguageID

Tis way depending on goApp.iCurrentlanguageID you get

1 de-DE german
2 fr-FR french
3 en-US american english

or

1 de-DE deutsch
2 fr-FR französisch
3 en-US amerikanisches english

or

1 de-DE allemand
2 fr-FR français
3 en-US anglais américain


You have no use for a CA on each table alone, so you have one CA for two tables and query a result in one (the current) language.


2. Very good observation, a CA does represent a resultset as far as I am concerned and not a single query. You know I showed you can change the query, unlike with views, if just the resultset has the same composition/schema.

Actually you can also vary the schema, not using the cursorschema property or also dynamically changing it, of course. I do have programmed myself a universal CA class accepting any query in init, not using cursorschema, that works quite like an SQLEXEC() of that query, but has the same flaws with the automatic schema it generates as SQLEXEC() has.

Also, yes, a CA always has one cursor as it's result, SQLEXEC can have many results, as you can send over many queries, that's not the case in one Cursorfill() go. You can actually Detach a CA cursr, then change Ca.Alias, change the query, do another CursorFill and have generated two cursors with the same CA object. But that's advanced stuff, forget about it, has little practical use. You can alwys instanciate as many CAs as you need to create as many cursors and keep them connected to the remote backend.

3. I do use DEs to group togther CAs of related tables, but I could also do without, DEclasses really are less important. While they are the only container class able to contain CAs you can also instanciate CAs independantly. I already gave the ide with a CACollection. DEs just can visually represent a group of CAs you drag into them, so give you an overview.

4. Not necessarily. Using two grids, one displaying customers, one orders, you rather would use a CA for customers and one for orders and in custmiergrid.rowcolchange would set a parameter of the orders CA/query to the current customer and requery to display that customers orders in the second grid.

The orders CA would perhaps query SELECT * from orders where customerid = ?This.icustomerid and you would set CA.icustomerid = crscustomers.id and requery("crsorders").

The initial value of the orders CA.icustomerid would be some nonexistant id, eg -1 or 0. Because you won't want to query gazillions of orders, initially. You may even not query all customers, but only customers starting with "A" etc.

You limit what you retrieve, because you really read in that data, unlike a USE of a table just creates a handle towards the dbf file, sort of. This is really the luxury of foxpro you don't have anymore. But that's not only true for CAs, it's true for any remote database access.

5. In the case of a join you will not have two aliases customers and orders, but would join both tables into one CA.alias, wouldn't you? That case would be ok to print a report of customerorders, eg, but be less good for editing customer and order data, wouldn't it?

You CAN specify both id fields as keyfields, edit the one CA cursor and store back to both tables. And by the way even though you get a customer with two orders two times into the ca resultcuror joining from customers to orders, of course, just editing one record will update the customer, all the other unedited records cause no update towards the sql server, only a modification of data adds to the cursor buffer processed by Tableupdate().

As an example:
Keyfieldlist="customerid, orderid"
UpdateNamelist inlcudes "...customerid customers.id, orderid, orders.id,..."
SelectCmd = "Select customers.id as customerid, orders.id as orderid, ... From customers left join orders..."

Assume customer ALFKI has two orders, so it appears two times in the CA cursor: (ALFKI, ...order1 data...) and (ALFKI, ...order2 data...). You edit one of the ALFKI to ALF and Tableupdate(), then you requery and both records now have ALF in them (beware if you own cats).

It works, but you will want a seperate customer list to display, won't you?

A join CA is good, if you know previous, what data you want, eg joined data for one customer or one order (including customer name from it's parent table).

Besides, you can query (all) customers and (all) orders and then index the cursors and set relations between the CA cursors, as you may be used to from DBF tables. This is still an option. You just won't want to query thousands or even millions of records into CA cursors to then relate them afterwards, this costs way too much time for initial data retrieval.

Bye, Olaf.

 

[stanlyn]

>> each CA having it's own sqlstringconnect() is opening a connection per CA.

1. So how would I test if a connection exists and use it if it was, otherwise issue a new connection?

2. >> As a connection can be shared, so all your queries using the same connection handle run in parallel on sql server and queries of other clients also run, perhaps sharing that connection,

So, are you saying that a different client computer somewhere in the world could use the connection I initiated? I understand that from sql server's perspective, it would be a different connection, however I'm surprised that my connection would be shared...

3. If they (the other client computer) uses the exact connection string, how does the server see that? As the same connection?

4. >> Assume customer ALFKI has two orders, so it appears two times in the CA cursor: (ALFKI, ...order1 data...) and (ALFKI, ...order2 data...). You edit one of the ALFKI to ALF and Tableupdate(), then you requery and both records now have ALF in them (beware if you own cats).

I would expect that, as the query is showing records to cover the "many" side of the results, as I'd expect there is really only one customer as ALFKI.

5. >> In the case of a join you will not have two aliases customers and orders, but would join both tables into one CA.alias, wouldn't you?

Yes, that is how I understand it. You would address the different fields normally because they are all part of the same CA/cursor, and the CA would be responsible for pushing it back to their respective tables...

6. Where is the best place to instantiate the CA when using a form if a DE is not defined? In the form's init or where???

7. >> Besides, you can query (all) customers and (all) orders and then index the cursors and set relations between the CA cursors, as you may be used to from DBF tables. This is still an option. You just won't want to query thousands or even millions of records into CA cursors to then relate them afterwards, this costs way too much time for initial data retrieval.

Yes, this concept of getting small record sets is new to me as for the past 14 years, so for a heads up on what I've done and what I'm trying to do... I have never:
A. Used a view (local or remote) for anything,
B. Always worked with tables,
C. Lots of work with local cursors from sql selects into cursor curCustomer,
D. Used a lot relations,
E. Anything complex, I'd start with a create cursor with all the necessary fields and make passes thru the real tables populating the cursors fields. I have always had excellent performance.
F. Long ago I did play around with some views and once I realized they did not persist (they had to be built on the fly) and it was quite slower than the native tables, I abandonded them because of performance the issues. I was expecting all fields and all records within the view, and the table had several memo fileds containing ocr text and etc. It was dog slow, so I quit them and haven't looked back..................... until now,
a. I have one county that has a 750,000 record table with a 1.5gb .fpt file where the 2gb limit is not too far away, and

b. I'm moving all 7 counties I represent to the web, where I have merged them all into the same database and table and currently it has 2.6 million pages (about 52gb less images) will be shown.

For performance, I had to learn sql filegroups, and there I moved the page images into them. The images filegroups are about 160gb in size and when it was part of the page.mdf file it killed the performance on a 2TB raid 1+0 on a I7 with 24gb mem... And yes, I spent 2+ weeks finding a SQL Server setup solution that was fast and managable. Most of that time was applying a table change and waiting 10 hours for it to complete. Talk about fustraiting... and I'm very unhappy with SQL Server 2008r2 because it has no way of defragmenting itself because the developers had no time to get it in the product. At one point, the .mdf was over 600gb. This fact makes it extremely painful to develope against where changes to tables, indexes, etc are constant, and doing so fragments the database. The upside is that I should not have performance problems later, as I'm dealing with them on the front side.

You writing on when to do this and when to do that is very helpful... Thanks Olaf...

Stanley

 

[stanlyn]

While testing a .prg approach, the debugger is skipping the create class stuff. The degugger start up when it hits the "set step on" line, and while stepping thru it, its next stop is in the "Procedure init" line, and then then it goes to the main programs "read events" line, skipping everything else in the .prg including the last 2 lines. Here is the code...

*********************
* Top of the sqlloadtable_ocrtext2entry.prg file which I'm running from the menu to test with...

SET STEP ON

Define Class CA_OcrText2Entry OF DE_AppMain As CursorAdapter
UseDeDataSource = .F.
SelectCmd = [Select county_pagekey, creation_time, identity_id, qty_characters, sla_clerk_id From SlaClerks.OcrText2Entry]
AllowUpdate = .T.
CursorSchema = [county_pagekey C(14), creation_time C(27), identity_id I, qty_characters I, sla_clerk_id C(11)]
Alias = "crsclerklogger"
Flags = 0
FetchAsNeeded = .F.
FetchSize = 100
MaxRecords = 150
KeyFieldList = "county_pagekey"
Tables = "SlaClerks.OcrText2Entry"
UpdatableFieldList = "county_pagekey, creation_time, qty_characters, sla_clerk_id"
UpdateNameList = "county_pagekey SlaClerks.OcrText2Entry.county_pagekey, creation_time SlaClerks.OcrText2Entry.creation_time, " + ;
"qty_characters SlaClerks.OcrText2Entry.qty_characters, sla_clerk_id SlaClerks.OcrText2Entry.sla_clerk_id"
UseCursorSchema = .T.
Name = "caClerkLogger"
WhereType= 3 && Key and modified

* ConflictCheckCmd =";IF @@ROWCOUNT=0 RAISERROR (' Update conflict.', 16, 1)"

Procedure Init
With This
.DataSourceType = gcDataSourceType
.Datasource = gcDataSource
Endwith
Endproc

Procedure BeforeUpdate
Lparameters cFldState, lForce, nUpdateType, cUpdateInsertCmd, cDeleteCmd

Endproc


Procedure AfterUpdate
Lparameters cFldState, lForce, nUpdateType, UpdateInsertCmd, DeleteCmd, lResult

Endproc

Procedure Destroy
=SQLDisconnect(This.Datasource)
Endproc
Enddefine

SELECT 'CA_OcrText2Entry '
BROWSE



Any ideas?
Stanley

 

[Olaf Doschke]

As answer to your last posting:

You haven't used the CA builder, have you? It also generates code and you don't show that here.

In regard to what the debugger does: This is not a usual PRG, this is a class definition, it's not executed, set procedure makes vfp know about the PRG and read in the DEFINE CLASSes, so CreateObject() can lookup where the CA_OcrText2Entry class is located, at that time the first thing running is the class init(), which is why the debugger jumps there. It's not skipping something. You are surely new to class use, too.

Bye, Olaf.

 

[Olaf Doschke]

Answer to the question list:

1. You would either rely on the connection or prgram an property_access method, simply add a new mthod and name it that way, or better check the option to add an access method when creating the property in an App class. It needs to return the property value, but you can check if it is set or NULL and if NULL make a connection, for example.

I never had an issue with a broken connections, it's not the same as with DBFs and instable LAN, a connection handle is just a number, there is no real permanent connection and pinging going on, the server just takes in that number and knows what you mean, even if the LAN connection was broken.

2. You are seeing this from the client side only. SQL Server is not just a file, like DBFs, it's a service, it remembers to which databases a connection is made, and can hand out the same connection handle to other users, too. This doesn't even need the same connectionstring. By specifying shared when you connect, you say you allow others to also use that connection SQL Server maintains. The job to share the connection is delegated to the server, you just allow it.

Doing a test with two VFP IDEs I get a connection handle of 1 in each IDE, supports what I say, but even if specifying .f. for shareable I get handle 1 for each IDE first connection, so no idea what this means. I just know if you don't share the connection you run out sooner, with SQLEXPRESS.

3. answered in 2. Also note, even if you specify to connect to some database, each connection is just a connection to the server, it doesn't even matter if by nt authentication or SQL Server authentication, your rights in each database determine, if you can "USE somotherdatabase" for example. So there really is only one connection type anyway.

4. Thats the nature of joins, as you join the 1-side with the many-side, the 1-side data is listed multiple times. Wlecome to SQL. That's not multiplying the table record, it's just put multiple times into the resultset. You join data, into one resultset, how else should that be done? This is not SET RELATION. And this is not an error.

6. look back into the structure I suggested. I said I have container biz objects, that load a DE with CAs, instead they could also load CAs. Form loads bizobjects, their inner structure doesn't matter to the form, only the aliases matter to it. The visibility of the CAs is by either the collection or looping through all CA instances, until findeing the one with the correct Alias property.

7. I thought so, you may put in a step 0 and generate one remote view with the view designer, to get a feeling of the view properties also used in cursoradapters, the keyfieldlist updatablefieldlist, updatenamelist. So you have more new things in CAs than someone already used to views.

Bye, Olaf.

 

[stanlyn]

I got the "class in a program" working, and yes I left a bunch of stuff out... I also noticed that drilling down in the object list the loCursor's parent was in fact the For others here is the working one:


SET STEP ON
Local loCursor, ovfp, nhnd, lsuccess
Clear
Set Exclusive Off
Set Multilocks On

loCursor = Createobject('CA_OcrText2Entry')

* Load loCursor with the data specified in SelectCmd and display error message if error occurs.
If !loCursor.CursorFill()
=Aerror(lar)
Messagebox(lar[2])
Endif

BROWSE last


Define Class CA_OcrText2Entry As CursorAdapter
UseDeDataSource = .T.
SelectCmd = [Select county_pagekey, creation_time, identity_id, qty_characters, sla_clerk_id From SlaClerks.OcrText2Entry]
AllowUpdate = .T.
CursorSchema = [county_pagekey C(14), creation_time C(27), identity_id I, qty_characters I, sla_clerk_id C(11)]
Alias = "crsclerklogger"
Flags = 0
FetchAsNeeded = .F.
FetchSize = 100
MaxRecords = 150
KeyFieldList = "county_pagekey"
Tables = "SlaClerks.OcrText2Entry"
UpdatableFieldList = "county_pagekey, creation_time, qty_characters, sla_clerk_id"
UpdateNameList = "county_pagekey SlaClerks.OcrText2Entry.county_pagekey, creation_time SlaClerks.OcrText2Entry.creation_time, " + ;
"qty_characters SlaClerks.OcrText2Entry.qty_characters, sla_clerk_id SlaClerks.OcrText2Entry.sla_clerk_id"
UseCursorSchema = .T.
Name = "caClerkLogger"
WhereType= 3 && Key and modified
ConflictCheckCmd =";IF @@ROWCOUNT=0 RAISERROR (' Update conflict.', 16, 1)"

Procedure Init
With This
.DataSourceType = gcDataSourceType
.Datasource = gnDataSource
Endwith
Endproc

Procedure BeforeUpdate
Lparameters cFldState, lForce, nUpdateType, cUpdateInsertCmd, cDeleteCmd

Endproc


Procedure AfterUpdate
Lparameters cFldState, lForce, nUpdateType, UpdateInsertCmd, DeleteCmd, lResult

Endproc

Procedure Destroy
=SQLDisconnect(This.Datasource)
Endproc
Enddefine


I did work with some remote views before tackling this CA stuff and I saw real quick that doing so in the builder was only maintained in the .dbc. It probably could be done in code also, but I didn't spend the time. I also noticed it was too easy to break it... and too hard to deploy...

Thanks again,
Stanley