HXTT DBF
Using DBF_JDBC30
Magna
2007-04-25 13:44:05.0
Hello:
I am using DBF_JDBC30.jar . I would like to know how can I create a .dbf file which can be read from MSExcel.
Thanks
Re:Using DBF_JDBC30
HXTT Support
2007-04-25 19:26:32.0
At Connecting to the Database, use versionNumber=03 connection property.
Re:Re:Using DBF_JDBC30
Magna
2007-04-26 07:10:38.0
When I set my properties:
Properties properties = new Properties();
properties.setProperty("versionNumber", "03"); //also tried with:04,30,40
properties.setProperty("DefaultExtension","dbf");

But in Excel i have this message before it opens the file: "The file is not in a recognizable format"....
Re:Re:Re:Using DBF_JDBC30
HXTT Support
2007-04-26 08:16:08.0
The new created dbf file should be able to open by your MS Excel. The older created dbf file without that versionNumber can't be open. But you can use "create table newtable select * from oldtable" to create DBASE III talbe.
Re:Re:Re:Re:Using DBF_JDBC30
Magna
2007-04-26 08:35:19.0
The file, indeed can be opened by MSExcel but after the message "The file is not in a recognizable format".... MSExcel opens the file but not normally as it always does to a dbf file. It shows in the sheet something like this:
0kˆ7IDCNAMECVALUEN3
1 JC 15.3 2 A 14.2 3 J 16.0 4 H 15.0
/******************************************************************************/
try{
Class.forName("com.hxtt.sql.dbf.DBFDriver").newInstance();
String urltable = "jdbc:dbf:///" + strDefaultOutputDir + "";
Properties properties = new Properties();
properties.setProperty("versionNumber", "03");
properties.setProperty("DefaultExtension","dbf");
//properties.setProperty("lockType", "DBASE4");

try {
conOutputFile = DriverManager.getConnection(
urltable,
properties);
stOutputFile = conOutputFile.createStatement();
stOutputFile.execute("CREATE TABLE if not exists "+'"'+strNewOutputTable+'"'+ " ("+ strSQLCols+ ")");
stOutputFile.close();
conOutputFile.close();

} catch (SQLException e) {

System.err.println(e.getMessage()+"-"+e.getErrorCode());
try {

stOutputFile.close();
conOutputFile.close();
return false;
} catch (SQLException e7) {
e7.printStackTrace();
return false;
}
}
}catch (InstantiationException e6) {
e6.printStackTrace();
return false;
} catch (IllegalAccessException e6) {
e6.printStackTrace();
return false;
} catch (ClassNotFoundException e6) {
e6.printStackTrace();
return false;
}
/******************************************************************************/
Re:Re:Re:Re:Re:Using DBF_JDBC30
HXTT Support
2007-04-26 16:30:28.0
>stOutputFile.execute("CREATE TABLE if not exists "+'"'+strNewOutputTable+'"'+ " ("+ strSQLCols+ ")");
What's your sql?
Re:Re:Re:Re:Re:Re:Using DBF_JDBC30
Magna
2007-04-27 08:57:04.0
strSQLCreateTable= "CREATE TABLE if not exists "myTable" ( "ID" VARCHAR (25), "NAME" VARCHAR (25),"VALUE" NUMERIC (4,1))"
Re:Re:Re:Re:Re:Re:Re:Using DBF_JDBC30
HXTT Support
2007-05-03 03:53:43.0
>MSExcel opens the file but not normally as it always does to a dbf file. It shows
> in the sheet something like this:
>strSQLCreateTable= "CREATE TABLE if not exists "myTable" ( "ID" VARCHAR (25), "NAME" VARCHAR (25),"VALUE" NUMERIC (4,1))"
Passted test with Excel 2000. It shows in sheet: ID NAME VALUE

What's your Excel version?
Re:Re:Re:Re:Re:Re:Re:Re:Using DBF_JDBC30
Magna
2007-05-03 05:59:09.0
Excel 2000 (9.0.3821 SR-1)
Re:Re:Re:Re:Re:Re:Re:Re:Using DBF_JDBC30
HXTT Support
2007-05-03 06:47:09.0
Passed test with the following code. What's your OS language? You can try:
properties.setProperty("charSet", "yourCharSet");




import java.sql.*;
import java.util.Properties;

public class testDBFExcel16 {
public static void main(String argv[]) {
String strDefaultOutputDir="d:/test/lib";
String strNewOutputTable="myTable";
Connection conOutputFile;
Statement stOutputFile;
String strSQLCols="\"ID\" VARCHAR (25), \"NAME\" VARCHAR (25),\"VALUE\" NUMERIC (4,1)";
try {
Class.forName("com.hxtt.sql.dbf.DBFDriver").newInstance();
String urltable = "jdbc:dbf:///" + strDefaultOutputDir + "";
Properties properties = new Properties();
properties.setProperty("versionNumber", "03");
properties.setProperty("DefaultExtension", "dbf");
//properties.setProperty("lockType", "DBASE4");

conOutputFile = DriverManager.getConnection(
urltable,
properties);
stOutputFile = conOutputFile.createStatement();
stOutputFile.execute("CREATE TABLE if not exists " + '"' +
strNewOutputTable + '"' + " (" + strSQLCols +
")");
stOutputFile.close();
conOutputFile.close();

stOutputFile.close();
conOutputFile.close();
}
catch (SQLException sqle) {
do {
System.out.println(sqle.getMessage());
System.out.println("Error Code:" + sqle.getErrorCode());
System.out.println("SQL State:" + sqle.getSQLState());
sqle.printStackTrace();
}
while ( (sqle = sqle.getNextException()) != null);
}
catch (Exception e) {
System.out.println(e.getMessage());
e.printStackTrace();
}
}

}

Re:Re:Re:Re:Re:Re:Re:Re:Using DBF_JDBC30
HXTT Support
2007-05-03 06:48:34.0
If your issue exists still, please email us your dbf sample.

BTW, because there's "if not exists" clause, you should remove manually myTable.dbf if you repeat your test.
Re:Re:Re:Re:Re:Re:Re:Re:Using DBF_JDBC30
Magna
2007-05-04 12:21:27.0
Thank you for the code. i have tried with your example code and it created the table well, but when i opened it in Excel, same problem. The language of my OS is english.
I read something like this in the first line of the Sheet : "0kˆ7IDCNAMECVALUEN3"
I am enclosing the code of the code example i used to this new try (i will email you my .dbf file i got):

import java.sql.*;
import java.util.Properties;

public class MyCreateTable {

public static void main(String argv[]) {

String strDefaultOutputDir="D:/instalados/eclipse/workspaceDIVA2007_RC8/GridIO";
String strNewOutputTable="myTable";
Connection conOutputFile;
Statement stOutputFile;
String strSQLCols="\"ID\" VARCHAR (25), \"NAME\" VARCHAR (25),\"VALUE\" NUMERIC (4,1)";

try {
Class.forName("com.hxtt.sql.dbf.DBFDriver").newInstance();
String urltable = "jdbc:dbf:///" + strDefaultOutputDir + "";
Properties properties = new Properties();
properties.setProperty("versionNumber", "03");
properties.setProperty("DefaultExtension", "dbf");
//properties.setProperty("lockType", "DBASE4");
conOutputFile = DriverManager.getConnection(
urltable,
properties);

stOutputFile = conOutputFile.createStatement();
stOutputFile.execute("CREATE TABLE if not exists " + '"' +
strNewOutputTable + '"' + " (" + strSQLCols +
")");
/* stOutputFile = conOutputFile.createStatement();
stOutputFile.execute("INSERT INTO " +strNewOutputTable+" (ID, NAME, VALUE) VALUES ('a','a',2.50)"); */

stOutputFile.close();
conOutputFile.close();
stOutputFile.close();
conOutputFile.close();
}

catch (SQLException sqle) {
do {
System.out.println(sqle.getMessage());
System.out.println("Error Code:" + sqle.getErrorCode());
System.out.println("SQL State:" + sqle.getSQLState());
sqle.printStackTrace();
}

while ( (sqle = sqle.getNextException()) != null);
}

catch (Exception e) {
System.out.println(e.getMessage());
e.printStackTrace();
}
}
}
Re:Re:Re:Re:Re:Re:Re:Re:Using DBF_JDBC30
Magna
2007-05-04 12:24:06.0
Excuse me, which email can I use to send my .dbf file as an attachment?
Thanks,
Magna
Re:Re:Re:Re:Re:Re:Re:Re:Using DBF_JDBC30
HXTT Support
2007-05-04 17:46:00.0
You can email webmaster@hxtt.com .
Re:Re:Re:Re:Re:Re:Re:Re:Using DBF_JDBC30
HXTT Support
2007-05-07 07:11:24.0
Checked, and found your issue:

>BTW, because there's "if not exists" clause, you should remove manually
> myTable.dbf if you repeat your test.
You should remove your myTable.dbf file manually, then run your test case again. I found that your sample is using VFP format. I runned your MyCreateTable.java and got a DBASE III format:)
Re:Re:Re:Re:Re:Re:Re:Re:Using DBF_JDBC30
Magna
2007-05-07 07:47:53.0
I did that , but have the same issue. And when i tried to save the created file from Excel to dbf3 format, for example, i lose data.
Re:Re:Re:Re:Re:Re:Re:Re:Using DBF_JDBC30
Magna
2007-05-07 07:49:57.0
I also tried this (of course, first I delete manually the previous file):
import java.sql.*;

import java.util.Properties;
public class MyCreateTable2 {

/**
* @param args
*/
public static void main(String[] args) {
String strDefaultOutputDir="D:/instalados/eclipse/workspaceDIVA2007_RC8/GridIO";

String strNewOutputTable="example1";

Connection conOutputFile;

Statement stOutputFile;

String strSQLCols="\"ID\" VARCHAR (25), \"NAME\" VARCHAR (25),\"VALUE\" NUMERIC (4,1)";

try {

Class.forName("com.hxtt.sql.dbf.DBFDriver").newInstance();

String urltable = "jdbc:dbf:///" + strDefaultOutputDir + "";

Properties properties = new Properties();

//properties.setProperty("versionNumber", "04");

properties.setProperty("DefaultExtension", "dbf");

properties.setProperty("lockType", "DBASE4");



conOutputFile = DriverManager.getConnection(

urltable,

properties);

stOutputFile = conOutputFile.createStatement();

stOutputFile.execute("CREATE TABLE " + '"' +

strNewOutputTable + '"' + " (" + strSQLCols +

")");

stOutputFile.close();

conOutputFile.close();



stOutputFile.close();

conOutputFile.close();

}

catch (SQLException sqle) {

do {

System.out.println(sqle.getMessage());

System.out.println("Error Code:" + sqle.getErrorCode());

System.out.println("SQL State:" + sqle.getSQLState());

sqle.printStackTrace();

}

while ( (sqle = sqle.getNextException()) != null);

}

catch (Exception e) {

System.out.println(e.getMessage());

e.printStackTrace();

}

}

}
Re:Re:Re:Re:Re:Re:Re:Re:Using DBF_JDBC30
HXTT Support
2007-05-07 07:57:49.0
>I did that , but have the same issue. And when i tried to save the created file
> from Excel to dbf3 format, for example, i lose data.
I tried your code, and MS Excel can open it. Your DBASE III sample should be opend by MS Excel. If you failed, please email me the dbf sample. The received sample is VFP format, and I can recur your issue:)


>//properties.setProperty("versionNumber", "04");
You should remove the //, but I guess that MS Excel maybe don't know DBASE IV format.

>properties.setProperty("DefaultExtension", "dbf");
You needn't to set that default value.

>properties.setProperty("lockType", "DBASE4");
Wrong. It hasn't any relation with database format. It's used for lock type. You can mask that line.


Re:Re:Re:Re:Re:Re:Re:Re:Using DBF_JDBC30
Magna
2007-05-07 08:08:04.0
I am sending also the *.dbf file i got with this new try (still cannot opened it properly in Excel :()


import java.sql.*;
import java.util.Properties;


public class MyCreateTable2 {

/**
* @param args
*/
public static void main(String[] args) {
String strDefaultOutputDir="D:/instalados/eclipse/workspaceDIVA2007_RC8/GridIO";

String strNewOutputTable="example1";

Connection conOutputFile;

Statement stOutputFile;

String strSQLCols="\"ID\" VARCHAR (25), \"NAME\" VARCHAR (25),\"VALUE\" NUMERIC (4,1)";

try {

Class.forName("com.hxtt.sql.dbf.DBFDriver").newInstance();

String urltable = "jdbc:dbf:///" + strDefaultOutputDir + "";

Properties properties = new Properties();

conOutputFile = DriverManager.getConnection(

urltable,

properties);

stOutputFile = conOutputFile.createStatement();

stOutputFile.execute("CREATE TABLE " + '"' +

strNewOutputTable + '"' + " (" + strSQLCols +

")");

stOutputFile.close();

conOutputFile.close();



stOutputFile.close();

conOutputFile.close();

}

catch (SQLException sqle) {

do {

System.out.println(sqle.getMessage());

System.out.println("Error Code:" + sqle.getErrorCode());

System.out.println("SQL State:" + sqle.getSQLState());

sqle.printStackTrace();

}

while ( (sqle = sqle.getNextException()) != null);

}

catch (Exception e) {

System.out.println(e.getMessage());

e.printStackTrace();

}

}

}
Re:Re:Re:Re:Re:Re:Re:Re:Using DBF_JDBC30
HXTT Support
2007-05-07 20:14:52.0
>Properties properties = new Properties();
>conOutputFile = DriverManager.getConnection(urltable,properties);
Wrong. It should be:
Properties properties = new Properties();
properties.setProperty("versionNumber", "03");
conOutputFile = DriverManager.getConnection(urltable,properties);


Google