Android Question How to connect Database FoxPro With JdbcSQL and jRDC2

[jeb med]

Hello everyone;

I need help I want connect to FoxPro database.

using method : JdbcSQL Directly Connect And using jRDC2

Thank You and Best Regards

 

[PaulMeuris]

How old is the FoxPro database?
Is it a Visual FoxPro database (Microsoft)?
Can you migrate the data to an Access database?

 

[jeb med]

How old is the FoxPro database?
Is it a Visual FoxPro database (Microsoft)?
Can you migrate the data to an Access database?

First release 1984 January 2015 the latest update version "Visual FoxPro 9.0"
Yes is Microsoft
Yes, I can migrate data to an Access database, but the client already has a program linked to this database

 

[PaulMeuris]

So then you should look for a suitable (ODBC) driver and the correct connection string...
Foxpro connectionstrings
Then you can use the information from this thread: JdbcSQL directly connect
Hope this will help you find a solution.

 

[AnandGupta]

This one can read dbf files, but is in B4J

[DBF] Read/Write DBF file using jDBF

https://www.b4x.com/android/forum/threads/aceesing-dbf-files.47068/ The example above works but the characters are not displaying correctly. With some trial-and-error, I finally get it works like the Test.java 'Non-UI application (console / server application) #Region Project Attributes...

www.b4x.com

 

[OliverA]

This may help: https://dbschema.com/jdbc-driver/FoxPro.html

 

[jeb med]

So then you should look for a suitable (ODBC) driver and the correct connection string...
Foxpro connectionstrings
Then you can use the information from this thread: JdbcSQL directly connect
Hope this will help you find a solution.

Thank you for replay
I will need Provider compatible with JdbcSQL

 

[jeb med]

This one can read dbf files, but is in B4J

[DBF] Read/Write DBF file using jDBF

https://www.b4x.com/android/forum/threads/aceesing-dbf-files.47068/ The example above works but the characters are not displaying correctly. With some trial-and-error, I finally get it works like the Test.java 'Non-UI application (console / server application) #Region Project Attributes...

www.b4x.com

I tried this code but it doesn't work

Public Sub SQLConnect

Private driver As String = "com.dbschema.dbf.JdbcDriver"
Private jdbcUrl As String = "jdbc:dbschema:dbf:\\192.168.225.254:1433\\atls\\MP2022"
sql1.Initialize(driver, jdbcUrl)

End Sub

 

[OliverA]

Private driver As String = "com.dbschema.dbf.JdbcDriver"

According to the site it's

• Java Driver Class: com.dbschema.xbase.DbfJdbcDriver

It may not be free though...

 

[aeric]

JdbcSQL Directly Connect And using jRDC2

You want either one or both?

 

[jeb med]

You want either one or both?

both

 

[aeric]

I suggest you try to make it work with jRDC2 first.
Create a small example with sample database file and try access using a B4J client. Once succeed, try with B4A.

I think this post is more helpful.
https://www.b4x.com/android/forum/threads/dbf-reading-visual-foxpro-dbf-using-jdbc.136840/

 

[jeb med]

I suggest you try to make it work with jRDC2 first.
Create a small example with sample database file and try access using a B4J client. Once succeed, try with B4A.

I think this post is more helpful.
https://www.b4x.com/android/forum/threads/dbf-reading-visual-foxpro-dbf-using-jdbc.136840/

Now I tried to connect using jRDC2 in B4j
And it's been done
I will apply the same in B4a

 

[jeb med]

I tried to connect to the database on my computer
But I think the way i wrote it is incorrect :

Sub Class_Globals
Private Root As B4XView
Private xui As XUI

Public dbf As JdbcSQL
Private driver As String = "com.dbschema.dbf.JdbcDriver"
Private jdbcUrl As String = "jdbc:dbschema:dbf://192.168.225.42/C:/data/?[charset=GBK]"

Private B4XTable1 As B4XTable
End Sub

I suggest you try to make it work with jRDC2 first.
Create a small example with sample database file and try access using a B4J client. Once succeed, try with B4A.

I think this post is more helpful.
https://www.b4x.com/android/forum/threads/dbf-reading-visual-foxpro-dbf-using-jdbc.136840/

 

[OliverA]

The JDBC driver is for local access only. If you want to use the driver to allow remote devices to access your DBF files, you'll have to use the driver with jRDC2.

 

[jeb med]

The JDBC driver is for local access only. If you want to use the driver to allow remote devices to access your DBF files, you'll have to use the driver with jRDC2.

But I've already remotely connected via JDBC
with the database ms sql server
And it works


Public Sub SQLConnect

ConnStr = "//192.168.225.4:1433"
DBName = "hi4200"
UserName = "sa"
PasswordTb = "us@2023"

sql1.InitializeAsync("sql2", "net.sourceforge.jtds.jdbc.Driver", "jdbc:jtds:sqlserver:" & ConnStr & ";databaseName=" & DBName & ";user=" & UserName & ";password=" & PasswordTb & ";appname=SKMJL;wsid=TEST;loginTimeout=10;socketTimeout=10", "", "")
sql1.Close

End Sub

 

[jeb med]

The JDBC driver is for local access only. If you want to use the driver to allow remote devices to access your DBF files, you'll have to use the driver with jRDC2.

Problem here, I need a remote device connection method to access DBF files

 

[aeric]

ms sql server
And it works

You can't treat different databases as same. The project I shared above required a server such as jRDC2. You may need to modify it to meet your needs.

 

[aeric]

[DBF] Read/Write DBF file using jDBF

[DBF] Reading Visual Foxpro DBF using JDBC

Since you never asked me about the difference between the both Code Snippets above, I assume you have read the details.
However, please let me point out again some important points.

1. Both solutions are B4J projects
2. It is not meant to work straight away in B4A
3. They are depends on third party Java libraries which access through JavaObject
4. The first snippet is simple as it only depends on one library. The drawback is it doesn't support some column types such as datetime field.
5. The second snippet is more complicated as the jdbc driver provided by dbschema requires H2 library. It depends on 3 third party libraries. What the driver actually do is, it created a clone of the original dbf file into a cache. Any SQL query or command operations doesn't have effect to the original dbf file. To write back the changes, you may also need to use JavaObject to execute a command as stated in the Github readme. (I think many wouldn't have read it)

Statement st = connection.createStatement();
st.execute("save dbf to <folder_path>");

At that time I shared the snippets, I was creating a POC for a potential customer. So I only focus on reading the data out of the DBF file. Very unfortunate, I didn't get the project.