Reading Foxpro database files

[kraigyb]

Our sales/inventory software stores data in foxpro database files (.dbf). I've been trying to figure out how to access the files through AMS to create custom reports and possibly manipulate some data. We currently use Report Writer to create custom reports, but its not always as flexible as I would like.

I've looked at the LuaSQL project (www.keplerproject.org/luasql), but I've been unable to implement in AMS. Not sure it if will even work.

I think what I'm looking for is something similar to the xMDB.dll, but for foxpro.

Any thoughts?
thanks,
craig

[Ulrich]

I am working on a action plugin for ODBC databases. Currently I am able to connect successfully to and retrieve data from MySQL and Firebird SQL servers, but this is still work in progress.

I used MySQL ODBC 3.51 and Firebird ODBC 2.0.0 drivers and they worked fine with my plugin.

I don't have used dBase or FoxPro on my computer, so I am unable to test/debug this properly currently. But as long as you have a working connection to your database set up through a DSN in your ODBC Data Sources, this plugin might already work correctly for you.

I am attaching a small sample project here with the plugin, I ask that if somebody downloads it to please give feedback.

Ulrich

[Dermot]

I am working on a action plugin for ODBC databases. Currently I am able to connect successfully to and retrieve data from MySQL and Firebird SQL servers, but this is still work in progress.

I used MySQL ODBC 3.51 and Firebird ODBC 2.0.0 drivers and they worked fine with my plugin.

I don't have used dBase or FoxPro on my computer, so I am unable to test/debug this properly currently. But as long as you have a working connection to your database set up through a DSN in your ODBC Data Sources, this plugin might already work correctly for you.

I am attaching a small sample project here with the plugin, I ask that if somebody downloads it to please give feedback.

Ulrich
This is very interesting. This is something AMS has needed for a long time. It would be nice to be able to use DNS-Less connections like:
"Data Source=maydatabase.mdb;Provider=Microsoft.Jet.OLED B.4.0;"

Just tested this with a remote MySQL db and it worked perfectly. :yes:yes

[Ulrich]

This is very interesting. This is something AMS has needed for a long time. It would be nice to be able to use DNS-Less connections like:
"Data Source=maydatabase.mdb;Provider=Microsoft.Jet.OLED B.4.0;"
Just tested this with a remote MySQL db and it worked perfectly. :yes:yes

Hello Dermot, thank you for your post. I hadn't thought of making the connection with an explicit string, passing the provider name, but I don't see why this shouldn't work. I definitely will try to implement this, so the user could have different ways to open the connection to the database - with or without having a DSN in the local computer. Thank you for the suggestion!

Ulrich

[Dermot]

Hello Dermot, thank you for your post. I hadn't thought of making the connection with an explicit string, passing the provider name, but I don't see why this shouldn't work. I definitely will try to implement this, so the user could have different ways to open the connection to the database - with or without having a DSN in the local computer. Thank you for the suggestion!

Ulrich
It would much easier to distribute because you can build the connection string dynamically and you don't have to worry about having a DNS setup. Just ask the user to choose a provider (MySQL, SQL Server, Access etc.), choose or enter the database, enter username and password. Then build the connection string and connect.

I tested it with Access and it works great.

[Ulrich]

I tested it with Access and it works great.

So we have data connection and retrieval to Access, MySQL and Firebird confirmed to work so far. Great.

I just sent you a newer version with the alternative connection method, please have a look at your private messages. Thank you!

Ulrich

[Ulrich]

My ODBC action plugin is almost finished, so I removed the former version from the earlier message. I'll create a new topic for this plugin after I finish the license file administration and the final tests. It will work with any database with an ODBC driver, like FoxPro, Access, Firebird SQL, MySQL, Oracle, Sybase, etc.
For now, the help file can be seen online (http://www.mindquake.com.br/files/ams/ODBC_Help.htm).

Ulrich

[arb]

Sounds great :yes

[jfxwave]

I think i might be missing something to connect to a mysql database.

I was using Lua 5.1 package but it has that crash but on Vista.

server = myserver.com
port = 3306
database = mydatabase
username = myusername
password = mypassword

I did was trying to use this but didn't connect

result = ODBC.OpenDirectConnection("Driver={SQL Server};Server=myserver.com;Database=mydatabase;"
.. "Uid=myusername;Pwd=mypassword");

Question
How would i connect to mysql database using this plugin?

Thanks for any help
Jean

[Ulrich]

I think i might be missing something to connect to a mysql database.

server = myserver.com
port = 3306
database = mydatabase
username = myusername
password = mypassword

I did was trying to use this but didn't connect
How would i connect to mysql database using this plugin?

After you installed a ODBC driver for your database you are trying to connect to, there are at least two valid ways to connect to a database using the plugin.

To establish the connection using ODBC, you may use an existing DSN. This is the standard way to connect. All you have to know is the name of the DSN that was formerly created in the ODBC Data Source Administrator. If you didn't create an entry there, create one and test the connection.

http://www.mindquake.com.br/files/ams/SNAG-080815-00.png

http://www.mindquake.com.br/files/ams/SNAG-080815-01.png

Once you have this set up according to your preferences, simply connect using the OpenDSNConnection action. If you have the username and password in your DSN, you can leave them blank. Or you can override them with a different pair, if/when needed.

result = ODBC.OpenDSNConnection("IR_Forum", "", "");

The second way to connect to a database would be the DSN-less connection. Using this way, you still have to install the correct ODBC driver for the database, but is isn't required to create an entry in the ODBC DSN Administrator before establishing a connection. Instead, you will have to pass the driver name and all parameters in the connection string. For the MySQL database using your example, this would be like this:

result = ODBC.OpenDirectConnection("DRIVER={MySQL ODBC 3.51 Driver};SERVER=myserver.com;DATABASE=mydatabase;US ER=myusername;PASSWORD=mypassword;");


Any method will work fine, if you follow these instructions. If you install any different ODBC driver, it is clear that you wil have to adjust your OpenDirectConnection string accordingly.

Ulrich

[bule]

Any hints on the price of this plugin?

[Ulrich]

Any hints on the price of this plugin?
Not sure exactly what you mean. The ODBC plugin for AMS (http://www.indigorose.com/forums/showthread.php?t=24159) is available for some time now, and you seem to be are aware of it, because you posted in that announcement thread. To unlock the plugin in the development environment I ask for US$ 10,00.

Ulrich

[bule]

Well I have always had a suspected of a dementia syndrome
affecting my memory, here is an another proof. :(

[jfxwave]

Thanks for all the info ;)

I got it to connect but I'm having other problems but like always i will try to fix them (learn) before i post another question.


Thanks for all the help

Jean