PDA

View Full Version : Active link table questions


ABC123

Tom Cone Jr
03-16-2008, 09:02 PM
1) Are the active link tables created on the local workstation, or in the database folder on the server? If you're running Alpha Five on a standalone machine I think it makes no difference. But if you're running network optimized from a shadow folder on a local workstation, I'm curious. Where does the "active link table" actually exist?


2) I see ODBC as an option that possibly could be used to create an active-link table. Might this be a way to create an active-link table from an existing native Alpha Five table? Would there be situations where this might be advisable, or would this most likely be a bad idea in almost all cases?

Thanks.

-- tom

ps. I apologize if these have been covered in earlier threads. The forum's search capability seems to have been turned off.

Selwyn Rabins
03-17-2008, 04:58 PM
1) Are the active link tables created on the local workstation, or in the database folder on the server? If you're running Alpha Five on a standalone machine I think it makes no difference. But if you're running network optimized from a shadow folder on a local workstation, I'm curious. Where does the "active link table" actually exist?


2) I see ODBC as an option that possibly could be used to create an active-link table. Might this be a way to create an active-link table from an existing native Alpha Five table? Would there be situations where this might be advisable, or would this most likely be a bad idea in almost all cases?

.

active-link tables are created wherever you tell a5 to create them. it is up to you.

in a traditional dbf application, the master database on the fileserver has all of the local tables in it and then when a user on a work station uses network optimization to create a local shadow of the master database, local 'shadow' tables are created on the work station that point to the real table on the file server.

active-links are the same. the developer will create active-link tables in the master database on the server, and then when the network optimized database is created on each user's workstation, a5 will just copy the active-link table from the master to the shadow on the user's work station.

on the second question:
i can't see any advantage in accessing local a5 tables as active-link tables rather than native tables. i have never tried it. in theory it might work using the Visual Fox Pro odbc driver. But if you did, you would loose long field names, all field rules and any extensions that a5 makes to the .dbf format.

Tom Cone Jr
03-17-2008, 05:15 PM
Selwyn, thanks. That helps me understand things a bit better.

Since the client workstations in a network optimized setting will be sharing the active link table created by the developer on the server, if one of the workstations refreshes the active link data it should be visible to the other workstations, too, right?

Selwyn Rabins
03-17-2008, 05:31 PM
Selwyn, thanks. That helps me understand things a bit better.

Since the client workstations in a network optimized setting will be sharing the active link table created by the developer on the server, if one of the workstations refreshes the active link data it should be visible to the other workstations, too, right?

no.

each user who opens the active link table gets to see a 'live' snapshot of the server data.

i say 'live' snapshot becase before editing any field in the table, that row is automatically refreshed.

but if two different users are looking at an active-link table that is based on say a customer table and both are looking at the record for customerid = 'abcd' which happens to have a customername of 'alpha software'.

if some other user (using some totally different product for example, or using a5 - does not matter) updates the record on ther server and changes the company name to 'gamma software', ***neither*** of the a5 users who have open active-link tables will see the change until:

1. user 1 presses the F5 key - that will refresh the CURRENT row in the open active-link, or
2. user 1 presses Shift+F5 - refresh all rows in the currently open active-link table, or
3. start editing a field in that row of the active-link table - which automatically does a refresh of that row.

However, none of the above 3 actions has ANY effect on user 2's view of the data. His already open active-link table will not reflect any changes make by other users until one of the above 3 things happes in HIS open active link table.

aschone
03-17-2008, 07:01 PM
From the sounds of this discussion and the discussion brought forward by Anis Fakhoury it sounds like if you are gonna have multiple individuals working in the same active link table you will need to perform a table refresh periodically throughout the day. Otherwise you may be working on old records and not seeing the most recent records.

Not being in a position to test it, what is the performance hit like for performing a refresh of the active link tables? Of course it will depend upon table size and network conditions, but in an ideal situation is a refresh of an active link table something that can occur while the user is working? Or does the user need to stop work and wait for the refresh to complete?

My only experience in this area is in regards to how QReportBuilder grabs its data from the QuickBooks database. Depending on size of the database the import process may need to run several hours.

NoeticCC
03-17-2008, 07:38 PM
From the sounds of this discussion and the discussion brought forward by Anis Fakhoury it sounds like if you are gonna have multiple individuals working in the same active link table you will need to perform a table refresh periodically throughout the day. Otherwise you may be working on old records and not seeing the most recent records..

I was under the impression that when you open a record, you always get the up to date version... that SEEMS to be the case with my test browses at any rate...

Selwyn Rabins
03-17-2008, 08:37 PM
I was under the impression that when you open a record, you always get the up to date version... that SEEMS to be the case with my test browses at any rate...


not sure what 'open a record' means.

if you mean 'open a table' then yes, you get up to date data.

once the active-link table cache is populated, however, a record in the cache is only refreshed when a refresh command on that record is executed, or when the record is edited (which causes an automatic refresh).

this should not be construed as a 'limitation' however. its how all products work. it is how the enterprise manager for sql server works. it is how access works, etc.

NoeticCC
03-18-2008, 05:34 AM
not sure what 'open a record' means.

if you mean 'open a table' then yes, you get up to date data.
Yes that is what I mean, e.g. you open a browse or a form that pulls data from the active link table... and thus open a record/row in that table.

I get the impression some posters in this thread seem to think the whole table is only refreshed when you open an application/database and then not again until you close and re-open it :)

Bob Arbuthnot
03-18-2008, 01:55 PM
Am I right, then, in my thinking that to take advantage of optimistic record locking a developer would have to use a non .dbf table format such as MySQL?

Selwyn Rabins
03-18-2008, 02:00 PM
Am I right, then, in my thinking that to take advantage of optimistic record locking a developer would have to use a non .dbf table format such as MySQL?

yes. native tables use pessimistic locking and active-link tables use optimistic record locking.

however, it should be noted that an active link table can be configured (if you want) to not use optimistic record locking. if you edit the active-link definition, and you change the UPDATE statement's WHERE clause from

'primary key and updateable fields'

to

'primary key only'


then you have effectively turned off optimistic record locking for that particular active link table.

Raymond Lyons
03-18-2008, 07:55 PM
Just a suggestion: Some of the questions and answers here would make a very good FAQ for active link tables in the new help file (and maybe reduce the number of future forum entries!).

Ray

Selwyn Rabins
03-18-2008, 10:12 PM
My only experience in this area is in regards to how QReportBuilder grabs its data from the QuickBooks database. Depending on size of the database the import process may need to run several hours.

you can't compare quickbooks and a real sql database.
a real sql database is hundreds, if not thousands of time faster than quickbooks.

so, opening an active-link table on a sql database table will be substantially faster than anything done against quickbooks.

Bill Parker
03-18-2008, 11:38 PM
once the active-link table cache is populated, however, a record in the cache is only refreshed when a refresh command on that record is executed, or when the record is edited (which causes an automatic refresh).

this should not be construed as a 'limitation' however. its how all products work. it is how the enterprise manager for sql server works. it is how access works, etc.

I guess that is why I never found "find by key" in Access! ;) Those of us without much SQL experience need to get right with proper techniques for accessing data in active link tables. I look forward to more discussions like this, with best practice techniques. (OK, OK, I could take a SQL class.)

Bill.

Tommy Thompson
03-19-2008, 03:20 PM
I guess that is why I never found "find by key" in Access! ;) Those of us without much SQL experience need to get right with proper techniques for accessing data in active link tables. I look forward to more discussions like this, with best practice techniques. (OK, OK, I could take a SQL class.)

I bought Mysql for Beginners off of Amazon (http://www.amazon.com/gp/redirect.html?ie=UTF8&location=http%3A%2F%2Fwww.amazon.com%2F&tag=131369-20&linkCode=ur2&camp=1789&creative=390957) for 49 cents ( 3.99 shipping).

mmaisterrena
03-19-2008, 06:19 PM
I suggest that now that active-link tables are in the play it would be very helpful to open a new discusion board exclusively for SQL->Alpha related questions.

MikeC
03-19-2008, 06:34 PM
Mauricio, I agree and added your request to an existing Forum Support thread--

http://msgboard.alphasoftware.com/alphaforum/showthread.php?p=450334#post450334

mmaisterrena
03-21-2008, 02:05 PM
Thank you Mike I hope that someone at Alpha will take our suggestion

jmatienza
07-08-2008, 07:09 PM
on the second question:
i can't see any advantage in accessing local a5 tables as active-link tables rather than native tables. i have never tried it. in theory it might work using the Visual Fox Pro odbc driver. But if you did, you would loose long field names, all field rules and any extensions that a5 makes to the .dbf format.

Hi, all.

The above statement just caught my attention.

Is it saying that I would not be able to set any field rules for active link tables? Aren't active link tables ordinary dbf tables?

NoeticCC
07-09-2008, 04:17 AM
Is it saying that I would not be able to set any field rules for active link tables?

No, you can set field rules in them but if you used an ODBC driver to link to a DBF table, then you would not be able to use or set the field rules in the table you are linking to...

jmatienza
07-09-2008, 11:14 AM
Thanks, Andrea! I see I interpreted the statement wrong.

So through active-link tables you can make field rules for backend database tables, as long as the backend is not itself a dbf table?

This is a powerful feature indeed.

NoeticCC
07-09-2008, 11:22 AM
Thanks, Andrea! I see I interpreted the statement wrong.

So through active-link tables you can make field rules for backend database tables, as long as the backend is not itself a dbf table?

This is a powerful feature indeed.
You can make rules BUT!!! They only get used if you use the active link table to update - or if you build in a routine that updates the field rules/calcs regularly.

jmatienza
07-09-2008, 09:11 PM
Would you clarify this for me, Andrea?


[Field rules] only get used if you use the active link table to update

Field rules come into play mostly when entering new records, less often when editing existing records. Do these circumstances qualify when using active-link tables, without having to do programming outside the field rules facility?

NoeticCC
07-10-2008, 02:12 AM
Field rules come into play mostly when entering new records, less often when editing existing records. Do these circumstances qualify when using active-link tables, without having to do programming outside the field rules facility?

Yes, definitely no need to generally do any programming for inserting or editing records using active link tables - at least as long as you use a SQL database that has auto-increment capability... with Oracle for example you have to do a bit of coding to make auto-increment ID fields work properly but with mySQL it works wonderfully, and personally I find record amendments, queries etc. in Xbasic EASIER on SQL databases (NOT using active link tables) but that may well be because I have far more experience in SQL than with DBF tables.

In fact I have had great success converting several apps from DBF to mySQL with active links (the active link part so far only used for desktop with my developer copy rather than properly distributed tools as I generally tend to use desktop tools for admin stuff and the rest are WAS apps) where appropriate.

Some of the web-only ones I converted in as little as half an hour, once a recent patch made SQL-db stored images that are NOT Bitmaps possible to be used in web grids etc. by allowing you to specify the image type. E.g. our news & RSS feed pages* are 100% run by Alpha5 now (thanks to the rewrite module in Apache -which works almost exactly the same as stritran_smatch() in Xbasic - I can plant these bits painlessly in our main website which runs on a UNIX box), including a short news listing on the home page and more detailed listings with images.

The news items are currently entered or edited through an active link table, although I have not set any field rules because I am hoping to write a full web admin module for it soon so other users can add news items through a web interface.

http://www.charlesworth.com/

*OK the RSS feed is generated from an Alpha5 WAS page rather than BEING an a5w page of course

jmatienza
07-10-2008, 03:36 AM
Thanks, Andrea, for your incredibly helpful comments. What is it with auto-increment fields? They seem to be such a big issue among SQL databases.

I haven't made the plunge to V9 yet, though I find it terribly attractive. I'm still trying to learn Alpha Five on my current investment (V7). While I don't aspire to reach your level of expertise, I find your experiences very illuminating.

Alpha Five is truly a one-of-a-kind product!

NoeticCC
07-10-2008, 05:14 AM
Thanks, Andrea, for your incredibly helpful comments. What is it with auto-increment fields? They seem to be such a big issue among SQL databases.

I must admit in MS Access I always avoided auto-increments like the plague, however with mySQL, similar to Alpha5, when you optimize the table from mySQL Administrator, it resets the AutoIncrement numbers...

In Oracle on the other hand you need to use sequences to get the same auto-inc effect, which isn't as easy to work with from any application written against Oracle. It's not a big deal though, you just have to add a line of code in the CanInsertRecord event on a web grid (or OnSave events in browses for desktop etc) to grab the next sequence if a new item is inserted, e.g.


Dim NextVal as c
NextVal = sql_records_get("::name::TRK", "DUAL", "1=1","ELOGGER_USER_INFO_SEQ.NEXTVAL")

DataSubmitted.EUI_ID = NextVal

And yes, Alpha5 is really rather special - I was impressed with the basic functions, but when I realised just how much you can do with a bit of coding I was blown away! :)

kthibeault1965
09-17-2008, 10:05 AM
I have a client who is working to migrate his fully working dbf based application to client server. As the first engine to try, they chose mysql. Unfortunately the primary function of the desktop application to manage set data (by saving parent id’s in each new child record) does not work. (or I have not configured it properly)

We do a simple one to many, two table set, using active link tables. Then to test, open the default form, add a parent record, then add a child record. The form sometimes give the illusion that it works, depending on the alpha build, but it does not.

Where the parent id should go in the child record, we get a null value, making the set totally unusable obviously. The child record gets created, but the link is never established because the null value never links to the parent.

Selwyn Rabins
09-17-2008, 11:18 AM
I have a client who is working to migrate his fully working dbf based application to client server. As the first engine to try, they chose mysql. Unfortunately the primary function of the desktop application to manage set data (by saving parent id’s in each new child record) does not work. (or I have not configured it properly)

We do a simple one to many, two table set, using active link tables. Then to test, open the default form, add a parent record, then add a child record. The form sometimes give the illusion that it works, depending on the alpha build, but it does not.

Where the parent id should go in the child record, we get a null value, making the set totally unusable obviously. The child record gets created, but the link is never established because the null value never links to the parent.

I an unable to repeat any problem using my test data.

http://screencast.com/t/fH5NslFA

Selwyn Rabins
09-17-2008, 12:34 PM
I was able to repeat the problem using your test data.


you need to change the definition of your child_row table.

you currently have the header_id column set to allow null values.

this does not make sense as this is the linking field in the set.

if you edit the structure of the table and change the header_id column to be 'not null' then it will work.

we will change a5 so that in future, even if the column is set to nullable, it will work.

but in the mean time, the work around is trivial.