Working with MySql

[mschoi]

Is this correct???

To edit mysql tables from an A5 'form' requires xbasic codeing...
meaning most of the action-scripts are virtually useless...
so If I code the entire form in xbasic....
and it makes the appropriate changes to the sql table....
I still have the refress the passive-link table...
and then navigate to that specific record to see the changes I just made.

Is this the only way I can see the sql data in a a5v8 form..make edits to the sql data from that from then see the changes I just made???

[Al Buchholz]

Re: Working with MySql

Mark

A5v8 offers direct communication with SQL type databases via ADO Xdialogs and ADO connections via the application server (web pages).

Forms do not work directly with SQL data in a5v8. Forms can work with data imported and passive linked tables where SQL tables are the source(imports and passive-linked tables are similar concepts but varied methods).

Watch for future releases to have an active linked table or as reports do now, an <SQL database source>.

Yes, you can code the reading and writing via ADO xbasic, but then you'd be doing the same thing that Alpha is preparing for a future version. (If you get that done, give me a call... ;) )

[mschoi]

Re: Working with MySql

Thank you al for your reply.

So if I wanted to create all my a5 tables in mysql and rebuild my database using mysql tables with a5 controlling the data...the forms would be virtually useless?

For example i have a 7-11 type concession store where the POS's are built with a5 forms. if I had to redesign this system using mysql tables....could the a5 form still be an effective tool?

Or would it take longer because after each cashier transaction entry that gets committed to the back-end table, the passive-link table would have to be refreshed before the cashier can see the result?

....may have long lag-time, no??

This is a major problem for me.
Right now I am required to build an application using a5 with mysql as the back-end database for a chain of retail stores.
Is this even possible with the current a5v8?

[Doug Page]

Re: Working with MySql

Mark,

This can not be done with standard forms - yet! You will need to have a good look at the XDialog component of Alpha Five and see how to create your own forms that can use the AlphaDAO to directly connect with your MySQL database. It is certainly not as easy as using the standard forms, but there are a lot of advantages with one of the main ones being that XDialog forms do not need to be associated with a table.

Suggest you search the form for AlphaDAO and XDialog and see what results you find. Also read those sections in the manual.

[dzimmer]

Re: Working with MySql

Hmmm, reading this may have just put the brakes on my using A5 V8 for any of the development. They make it sound like you can do everything with everything and there aren't any limitations.

Are there any tutorials available to show how to make a hybrid (or even just desktop ) application using a backend that the Enterprise version is said to support.

I read this on their website and was part of my decision to purchase:

Alpha Five Version 8 Enterprise Edition

• includes everything in the Professional Edition,
• offers the ability to work with SQL databases through ADO or the new Alpha DAO,
• the Alpha Five Enterprise Report Builder - previously this was separately priced at $199,
• the Alpha Five Database Explorer

Build enterprise applications against Alpha�s built in dbf engine or any back-end data source using all of Alpha Five's proven web and desktop development tools.

You would want this version if you intend working with any kind of SQL data either on desktop applications or web applications.
Take advantage of the advanced browse options.

Where does it say that half the capabilities will not work if you use "any back-end data source".

Or (hopefully) I'm readin git incorrectly and just don't know what I'm doing yet.

[Peter.Wayne]

Re: Working with MySql

It's certainly possible to use MySQL as a backend -- I'm doing it now with some tables on both a web-based and desktop basis -- but you can't at present use Alpha Five forms. You have to use Xdialogs or the Web Application Server, and either one requires coding.
Alpha Software is definitely working on creating "active link tables" which should let you work transparently with Alpha Five forms and SQL database engines.

[gagsoft]

Re: Working with MySql

It's certainly possible to use MySQL as a backend -- I'm doing it now with some tables on both a web-based and desktop basis -- but you can't at present use Alpha Five forms. You have to use Xdialogs or the Web Application Server, and either one requires coding.
Alpha Software is definitely working on creating "active link tables" which should let you work transparently with Alpha Five forms and SQL database engines.

Are there code examples somewhere that one can look at, of Xdialogs that use MySQL as a backend.

[Finian Lennon]

Re: Working with MySql

Open the "Learning Xdialog" application (it's located in the A5V8 program folder) and navigate to the end of the scripts "What's New in V8". The last example is "Working with Alpha DAO" or something like that.

The examples use the installed Access file but the point of AlphaDAO is that, once you successfully create your connection to the back end, everything else should be the same.

[gagsoft]

Re: Working with MySql

Hi Peter
The scenario: Bookings are made to a MySQL database via a web interface (Not Alpha) An Alpha application is used to pull the bookings using passive-link tables (OrdersRM and OrderLinesRM) At the moment refresh oprations is used to update the passive-link tables based on primary keys to ignore already downloaded records. 2 Questions
1. I want to deploy this application at two sites using Atrum - Where do I find the ODBC connection string files. 2. What sort of response can I expect when refreshing a passive-link table from a MySQL table that has more than 70 000 records.

Thanks
Peter Gagiano

[Peter.Wayne]

Re: Working with MySql

One question is whether records, once entered, are ever changed? If so, then the "refresh" must refresh all records, not just new records.
70,000 records is not all that many but it could take a while on a internet network. If you find that refreshes are too slow, there's a better alternative. You can use MySQL's replication abilities to set up a local slave database on the same local network (or even the same computer) on which Alpha Five resides. MySQL will only send updates to the slave based on the logging of changes to the tables. You can then run whatever Alpha Five reports you need to run directly off the MySQL local tables -- you don't even need to create passive link tables (the report writer will invisibly create passive link tables for you).

- Peter

[gagsoft]

Re: Working with MySql

Peter
Thank you for advice. My question 1 was: - I want to deploy this application at two sites using Atrum - Where do I find the ODBC connection string files so that I can compile it into the install set. What do you think of the following in terms of the bookings via the Internet?:- From the passive-link tables actual working tables are updated, The archiving and storing of the data is done on a local machine, and the booking tables on the remote MySQL database are flushed once a month to keep the records low. I did a dummy run and it seems to work fine. Can you anticipate any issues with this configuration?
Thanks
Peter gagiano

[Peter.Wayne]

Re: Working with MySql

Peter,
I,m not clear about your configuration. I don't know how Atrum will work -- from what I could find, it seems to be set up for internet relay chat and not for remote access, but I assume you know better if you think you can get it to work for your application. The ODBC connection string for MySQL would be something on the order of


dim cs as c="{A5API=MySQL,Server='hostName',Port='3306',UserName='userName
',Password='password',Database='databaseName'}"

where, of course, hostName is the actual host or IP address, and similarly for the other parameters.
Port 3306 is the default port for MySQL.

I don't see any problems with your setup otherwise, but I'm not sure what you mean by "updating the working tables from the passive link tables." Are you updating some (large) Alpha Five tables from the (smaller) passive link tables that are in turn populated from the (recycled) MySQL tables?

[gagsoft]

Re: Working with MySql

Hi Peter
I use seperate working tables because the passive-link tables get overwritten with every refresh. Then I use update operations to update the working tables and only new records are appended from the passive-link tables. Please see the attachement, a schematic depicting data flow.

Thank you for alll the help. It is really appreciated.

Peter Gagiano

[gagsoft]

Re: Working with MySql

Peter
I want to create a passive-link table that I want to limit the amount of records with the WHERE clause(A date field that has a date on or after the current date). My problem is that it does not seem to accept the Curdate() function, only an argument like :thedate. and I cannot map a variable to an argument liek with the insert, update and delete scripts. Is there a way arrount this or am I doing something wrong?

Thanks
Peter Gagiano

[Peter.Wayne]

Re: Working with MySql

You should be able to use
dim args as sql::arguments
args.set(":thedate",date())

and then submit the SQL statement.
- Peter