|
Heng Xing Tian Tai Lab of Xi'an City (abbr, HXTT)
HXTT ACCESS
|
Corrupted Access 97 DB for some statements. |
Norbert |
2006-03-29 00:00:00 |
Hello,
We have some SQL statements that are running correctly into Access 2000 MDBs via HXTT, into Access 97 MDBs via JDBC-ODBC but not into Access 97 MDBs via HXTT. The used HXTT driver was downloaded at 03/24/2006.
The following SQL Statement results in a currepted Access 97 MDB:
insert into TestTable (ID, Locale, ShortDesc, LongDesc) values (2, 'en', 'Mead-Hatcher - Articulating Keyboard/Mouse Arms with Teflon - Black, Standard Style, 21" W x 17" D x 6" H', 'Mead-Hatcher - Articulating Keyboard/Mouse Arms with Teflon - Color Black - Dimensions Standard Style, 21" W x 17" D x 6" H');
We have some more detailed information (with an ant script) that I will send to webmaster.hxtt@gmail.com.
Thank you in advance for you help,
Norbert
|
Re:Corrupted Access 97 DB for some statements. |
HXTT Support |
2006-03-29 00:00:00 |
Thanks. Fixed a bug for inserting some speical data length into Access97. Please download the latest package.
|
Re:Re:Corrupted Access 97 DB for some statements. |
Norbert |
2006-03-30 03:52:25 |
Looks good now.
Thank you for the quick help.
Norbert
|
Re:Re:Re:Corrupted Access 97 DB for some statements. |
Antoni |
2006-09-13 01:29:34 |
Hi,
I've the same problem with latest version (2006-09-11 10:53) while inserting massive data into a table using a PreparedStatement (running correctly into Access 2000 MDBsvia HXTT, into Access 97 MDBs via JDBC-ODBC, but not into Access 97 MDBs via HXTT).
I think the error is data dependent because doing the data insertion with different data the table is corrupted in different row number.
If you need more detaliled information let me know.
Thank you in advance,
Antoni
|
Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
HXTT Support |
2006-09-13 02:15:26 |
Dear Antoni,
ftp site: ftp.hxtt.com
ftp user: anonymous@hxtt.com
ftp password: (empty)
login mode: normal (not anonymous)
ftp port:21
upload directory: incoming
After upload, you can't see that upload file, but it has been upload.
then notify us through webmaster@hxtt.com .
|
Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
Antoni |
2006-09-13 04:58:08 |
Hi,
I've just uploaded a zip with the empty table in a Access97, the statement used for the insertion and a sample of data (first row is successful insertion, second is corrupted insertion).
I've also the corrupted database with the data deleted but I cannot compact it and its size is greater than 33MB. It's zipped size is only 0.5MB but I will not send it if not needed.
Thank you in advance,
Antoni
|
Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
HXTT Support |
2006-09-13 06:01:23 |
Passed test with two data row sample. I inserted repeatedly 50000 rows (52MB). Pease provide more information.
import java.sql.*;
import java.util.Properties;
import java.util.Vector;
public class testAccessInsert2{
public static void main(String argv[]){
try{
String url="jdbc:access:/f:/mdbfiles/exportacio97.mdb";
Class.forName("com.hxtt.sql.access.AccessDriver").newInstance();
Properties properties=new Properties();
Connection dbCon = DriverManager.getConnection(url,properties);
String valuestr="08559859 $08595530 $110600909 $R.Q.APLICACIONES ELECTMEC.S.A. $ $ $ $ $ $ $ $ $C/ MIQUEL SERVET, 9 NAVE 6 POL. IND. CAM$ $08850 $93 6333990 $ $93 6333991 $ $ $ $ 0$ $ $ $ $ $ $ $ $ $ $ $ $2005/07/21$0$ 0$0$GAVA "
+"$36930833 $08595530 $110600196 $MU���OZ PENDON, JUAN $ $ $1992/03/04$MUNOZ PENDON, JUAN $ $1958/05/11$ $ $RAMBLA CAZADOR, 1-3 TDA. 44 $ $08042 $93 4282987 $639324266 $93 4279181 $ $080142694 $08089996723 $ 0$ 0$ $ $ $ $ $ $ $ $ $ $ $2006/04/21$0$ 0$0$BARCELONA ";
//valuestr=com.hxtt.util.StringUtil.replaceAll(valuestr,"$$","$ $ ");
java.util.StringTokenizer st=new java.util.StringTokenizer(valuestr,"$");
java.util.ArrayList values=new java.util.ArrayList();
while(st.hasMoreTokens()){
String str=st.nextToken();
if(str!=null){
//str = str.trim();
if (str.length() == 0)
str = null;
}
values.add(str);
}
PreparedStatement stmt=dbCon.prepareStatement("INSERT INTO emprasso (NIF_ASSOC,NIF_GREMI,CODI_FERCA,NOM,NOM_COM,D_FUNDACIO,D_INGRES,TITULAR,CARREC_T,D_NAIX,PERS_C,CARREC_C,DOM1,DOM2,CP,TELS,TELS_M,FAX,E_MAIL,REG_IND,NUM_SS,I_POL_RC,NUM_TR,ISO,CLASS,NOTES,EXTRA_1,EXTRA_2,EXTRA_3,EXTRA_4,EXTRA_5,EXTRA_6,EXTRA_7,EXTRA_8,D_ACTUAL,ESBORRAT,RISC,VIAFAX,POBLACIO) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);");
for(int i=0;i<50000;i++){
for(int j=1;j<=39;j++){
stmt.setString(j,(String)values.get((i%2)*39+j-1));
}
stmt.executeUpdate();
}
stmt.close();
dbCon.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:Corrupted Access 97 DB for some statements. |
Antoni |
2006-09-13 06:58:17 |
Hi,
I've uploaded a full insertion data on table emprasso.
Thank you,
Antoni
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
HXTT Support |
2006-09-13 08:13:38 |
It's easy to use HXTT Text (CSV) to import all rows in act file into Access database, but I haven't recur your issue with EmprassoFullDataInsertion.act (1315KB) in EmprassoFullDataInsertion.zip, and insert successfully 1373 rows. For that 992KB file, it can insert 1037 rows. BTW, all upload files can't be seen by other anonymous, and have removed by HXTT at once.
import java.sql.*;
import java.util.Properties;
import java.util.Vector;
public class testAccessInsert3{
public static void main(String argv[]){
try{
String actUrl="jdbc:csv:/f:/mdbfiles/?csvfileExtension=ACT;_CSV_Separator=$;maxScanRows=-1";
String accessUrl="jdbc:access:/f:/mdbfiles/exportacio97.mdb";
Class.forName("com.hxtt.sql.text.TextDriver").newInstance();
Class.forName("com.hxtt.sql.access.AccessDriver").newInstance();
Properties properties=new Properties();
Connection actCon = DriverManager.getConnection(actUrl,properties);
Connection accessCon = DriverManager.getConnection(accessUrl,properties);
Statement actStmt=actCon.createStatement();
ResultSet actRows=actStmt.executeQuery("select * from EmprassoFullDataInsertion.act;");
PreparedStatement accessStmt=accessCon.prepareStatement("INSERT INTO emprasso (NIF_ASSOC,NIF_GREMI,CODI_FERCA,NOM,NOM_COM,D_FUNDACIO,D_INGRES,TITULAR,CARREC_T,D_NAIX,PERS_C,CARREC_C,DOM1,DOM2,CP,TELS,TELS_M,FAX,E_MAIL,REG_IND,NUM_SS,I_POL_RC,NUM_TR,ISO,CLASS,NOTES,EXTRA_1,EXTRA_2,EXTRA_3,EXTRA_4,EXTRA_5,EXTRA_6,EXTRA_7,EXTRA_8,D_ACTUAL,ESBORRAT,RISC,VIAFAX,POBLACIO) ?;");
accessStmt.setObject(1,actRows);
accessStmt.executeUpdate();
accessStmt.close();
actStmt.close();
accessCon.close();
actCon.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:Corrupted Access 97 DB for some statements. |
Antoni |
2006-09-13 08:50:35 |
Hi,
The act file is another data extraction file the application does, it isn't the file used to fullfill the mdb table.
Some data from an Oracle database is inserted to a MDB file because some people need it.
The data insertion is made in two steps
First, the table datatypes are detected using DatabaseMetaData.
Later they are inserted using setXXX in the PreparedStatement (XXX is datatype detected).
May be the problem is in how setXXX are managed by the driver.
Antoni
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
HXTT Support |
2006-09-13 08:55:03 |
>May be the problem is in how setXXX are managed by the driver.
I don't think so.
>I've also the corrupted database with the data deleted but I cannot compact it
> and its size is greater than 33MB. It's zipped size is only 0.5MB but I will not
> send it if not needed.
You can try to run once "reindex all on emprasso;". Maybe it's a index issue.
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
HXTT Support |
2006-09-13 08:58:24 |
>First, the table datatypes are detected using DatabaseMetaData.
>Later they are inserted using setXXX in the PreparedStatement (XXX is datatype detected).
BTW , you needn't detect data type, since HXTT Access can do type convert automatically for string<-->number, string<-->date, and so on.
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
Antoni |
2006-09-14 01:30:17 |
Hi, I'm trying to do the workaround you wrote but now I have double conversion problems. Decimal separator in my language is colon instead of dot and when I try to let the driver do all the conversions I get the error "Failed to get a double value from java.lang.String: 360607,26".
There's any property in the getConnection method to tell the driver those Locale issues? Could this issue be the cause of data corruption?
Thanks in advance,
Antoni
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
HXTT Support |
2006-09-14 02:57:19 |
>Decimal separator in my language is colon instead of dot and when I try to let
>the driver do all the conversions I get the error "Failed to get a double
>value from java.lang.String: 360607,26".
>There's any property in the getConnection method to tell the driver those
>Locale issues? Could this issue be the cause of data corruption?
Supported. You needn't to set any property. It will try to use local format first to parse double if failed. If failed still, it will try many possible local format to parse double value. If failed still, it will throw "Failed to get a double value from java.lang.String:...".
You can download the latest package after two hours.
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
Antoni |
2006-09-14 06:50:10 |
Hi,
Using setString instead of setXXX now works with the latest version, but data stored in mdb file is corrupted again.
Using reindex gave me a timeout exception (Failed to reindex file for Timeout Interrupted Exception: beyond 1000 msecs). Could this timeout be increased?
Thank you in advance,
Antoni
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
HXTT Support |
2006-09-14 07:07:55 |
>Using setString instead of setXXX now works with the latest version,
> but data stored in mdb file is corrupted again.
MDB file is corrupted or only some data rows is abnormal?
>Using reindex gave me a timeout exception (Failed to reindex file for Timeout >Interrupted Exception: beyond 1000 msecs). Could this timeout be increased?
lockTimeout connection property is used for that timeout value. But you should check your code, and it means that table is holding by some open ResultSet, and REINDEX sql need to open it exclusively.
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
Antoni |
2006-09-14 07:15:50 |
Hi,
Data is corrupted from one row (with the same data is always same number) to the end of file.
I will check the code to execute reindex.
Thank you,
Antoni
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
HXTT Support |
2006-09-14 07:25:01 |
>Data is corrupted from one row (with the same data is always same number)
> to the end of file.
But we haven't recurred your issue for Access 97 with your data sample.
>I will check the code to execute reindex.
You can run that sql once from any JDBC browser, for instance, dbpilot, and DbVisualizer.
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
Antoni |
2006-09-15 03:35:08 |
Hi,
Reindex doesn't work.
I've sent the mdb with the corrupted data to FTP.
Thank you,
Antoni
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
Antoni |
2006-09-15 06:47:51 |
Hi,
Reindex doesn't work.
I've sent the mdb with the corrupted data to FTP.
Thank you,
Antoni
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
HXTT Support |
2006-09-15 08:53:18 |
Please download the latest JDBC30 package, and try whether your data can be read, or won't be corrupted when import from Oracle.
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
Antoni |
2006-09-18 01:58:47 |
Hi,
The data is still stored corrupted with the last version of the JDBC 3.0 pakage (2006-09-17 06:47 ). The data retrieved from Oracle is OK and only is stored unsuccessfully when done to mdb 97.
I think it is a index issue but don't know why it happens.
Antoni
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
HXTT Support |
2006-09-18 02:14:21 |
But we can't recur your issue, because we haven't the simulated same data. You can upload two file into our ftp stie:
1st file(mdb 2000): table1 with the imported data.
2nd file(mdb 97): empty table1
Then we can try insert into mdb97.table1 select * from mdb2000.table1 to see whether we can recur your corrupted data issue.
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
Antoni |
2006-09-18 03:10:14 |
Hi,
I've sent mdb97.mdb and mdb2000.mdb zipped together as mdb files.zip.
Antoni
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
HXTT Support |
2006-09-18 05:07:48 |
I tested with "insert into mdb97.emprasso select * from mdb2000.emprasso", and it seems normal. Please try that sql to see whether you can get a normal mdb97 with 1373 data rows? Then we can continue to dig out why.
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
HXTT Support |
2006-09-18 21:24:22 |
v2.0.018 fixed an incompatible issue for MS Access 97's CLOB value with more than 2048 length. HXTT Access can read those older inserted CLOB values, but MS Access will show an error data prompt, then can show correctly those "error" CLOB data. Please download, since your notes colum is such a CLOB column:)
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
Antoni |
2006-09-19 02:02:50 |
Hi,
I've downloaded last driver version but the insetion is still unsuccessfully stored in mdb 97 file.
I've isolated a Oracle row that inserted alone in a file gives corrupted data and I've uploaded that row with the source database definition and the query called to do the insertion.
After calling the query the string obtained is splitted at '$' chars and inserted in the header column of the mdb file.
I hope you could recur the corrupted data issue.
Thank you,
Antoni
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
HXTT Support |
2006-09-19 04:07:08 |
Because I haven't see your exportacio97 mdb file,
I used the following sql:
create database exportacio97;
CREATE TABLE exportacio97.emprasso( NIF_EMPRESA VARCHAR(10) NOT NULL, CODI_FERCA VARCHAR(10) NOT NULL, NOM VARCHAR(60) NOT NULL, NOM_COMERCIAL VARCHAR(60), DATA_FUNDACIO DATE, DATA_INGRES DATE, TITULAR VARCHAR(60), CARREC_TITULAR VARCHAR(30), DATA_NAIXEMENT_TITULAR DATE, PERSONA_CONTACTE VARCHAR(60), CARREC_PERSONA_CONTACTE VARCHAR(30), DOMICILI1 VARCHAR(60), DOMICILI2 VARCHAR(60), CP VARCHAR(5), POBLACIO VARCHAR(50), TELEFONS VARCHAR(60), MOBILS VARCHAR(30), FAX VARCHAR(30), EMAIL VARCHAR(80), WEB VARCHAR(110), PUBLICAR_INFORMACIO_WEB NUMERIC(1, 0) NOT NULL, REGISTRE_INDUSTRIAL VARCHAR(20), NUMERO_SS VARCHAR(20), IMPORT_POLISSA_RC NUMERIC(12, 2), NUM_TREBALLADORS NUMERIC(38, 0), ISO VARCHAR(10), CLASSIFICACIO VARCHAR(40), ESTAT_PLA_RENOVE VARCHAR(2), DATA_CREACIO TIMESTAMP NOT NULL, USUARI_CREACIO VARCHAR(10) NOT NULL, DATA_ULT_MODIFICACIO TIMESTAMP NOT NULL, USUARI_ULT_MODIFICACIO VARCHAR(10) NOT NULL, COMENTARIS longvarchar, NIF_ORIGINAL VARCHAR(10), PRIMARY KEY (NIF_EMPRESA));
to produce the sampe mdb file. Then I have found that MS Access 97 doesn't support NUMERIC type(maybe that's the key for your issue), so that MS Access can't open that table at all. I have changed the map of NUMERIC type into DOUBLE type, and continue to test, and haven't found other issue.
Because I used HXTT Excel with HXTT Access to insert that row from your badInsertedRow.xls sample. I don't know the 9360000 meaning in "19/09/2006 10:16:06,936000" so that HXTT Access can't convert that string value into timestamp value. I removed that 93600000, then succeed in test.
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
HXTT Support |
2006-09-19 04:29:11 |
You can download the latest JDBC30 package, and retry again.
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
Antoni |
2006-09-19 05:06:37 |
Hi,
It still doesn't work.
The exportacio97 mdb file is the same mdb 97 file I sent before. If you need it, I could upload it again.
I've sent you the table creation script to show you the Oracle datatypes used as source for data migration. Excel row is one of the rows contained in the Oracle table, so those data are of the type shown in script creation file.
9360000 means second fractional precision, "19/09/2006 10:16:06,936000" is a TIMESTAMP(6) data type.
Could you create Oracle table using script, fill it with the row in Excel file and then copy it from Oracle database to the mdb file? I think it is the way to recur my issue so this is I'm doing.
Thank you,
Antoni
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
HXTT Support |
2006-09-19 05:56:19 |
>The exportacio97 mdb file is the same mdb 97 file I sent before. If you need
> it, I could upload it again.
But those old mdf files have invalid column names or column count like that badInsertedRow.xls file.
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
HXTT Support |
2006-09-19 06:21:18 |
>9360000 means second fractional precision, "19/09/2006 10:16:06,936000" is a
> TIMESTAMP(6) data type.
It's supported now.
>Could you create Oracle table using script, fill it with the row in Excel file
> and then copy it from Oracle database to the mdb file? I think it is the way
> to recur my issue so this is I'm doing.
We haven't Oracle.
>Excel row is one of the rows contained in the Oracle table, so those data are
> of the type shown in script creation file.
How can you import row from Excel to Oralce? Are you using jdbc-odbc bridge?
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
Antoni |
2006-09-19 06:42:48 |
>>Could you create Oracle table using script, fill it with the row in Excel
>>file and then copy it from Oracle database to the mdb file? I think it is
>>the way to recur my issue so this is I'm doing.
>We haven't Oracle.
Could you emulate my Oracle with another DB?
>>Excel row is one of the rows contained in the Oracle table, so those data
>>are of the type shown in script creation file.
>How can you import row from Excel to Oralce? Are you using jdbc-odbc bridge?
I've two database connections, one to Oracle, another to mdb file. The first does the SQL query to get the data and the other one inserts the rows to the mdb file.
I used jdbc-odbc bridge driver before, but it doesn't work on Unix sistems with mdb files. Then I tried the HXTT access driver.
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
HXTT Support |
2006-09-19 07:11:37 |
>Could you emulate my Oracle with another DB?
I can't now. Can you send the blank and corrupted MDB 97 file? All old mdb97 files haven't the responding columsn with badInsertedRow.xls sample.
>I used jdbc-odbc bridge driver before, but it doesn't work on Unix
>sistems with mdb files. Then I tried the HXTT access driver.
I tried jdbc-odbc bridge with your badInsertedRow.xls sample too, but still failed to recur.
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
Antoni |
2006-09-19 07:42:44 |
>I can't now. Can you send the blank and corrupted MDB 97 file? All old mdb97
>files haven't the responding columsn with badInsertedRow.xls sample.
I've just sent it.
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
HXTT Support |
2006-09-19 09:34:15 |
According to your mdb files, I found that you're using an unknown column map between Excel columns and MDB columns. I have verified most of them, but haven't recur your issue. You issue is still about NOTES column, a LONGVARCHAR (memo) column. Please check the content length of notes content in your Oracle, then I can simulate your data to insert.
import java.sql.*;
import java.util.Properties;
import java.util.Vector;
public class testAccessInsert4{
public static void main(String argv[]){
try{
// String actUrl="jdbc:odbc:testexcel";
String actUrl="jdbc:excel:/f:/excelfiles/";
String accessUrl="jdbc:access:/f:/mdbfiles/mdb97.mdb";
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
Class.forName("com.hxtt.sql.excel.ExcelDriver").newInstance();
Class.forName("com.hxtt.sql.access.AccessDriver").newInstance();
Properties properties=new Properties();
Connection actCon = DriverManager.getConnection(actUrl,properties);
Connection accessCon = DriverManager.getConnection(accessUrl,properties);
Statement actStmt=actCon.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
// ResultSet actRows=actStmt.executeQuery("select NIF_EMPRESA,CODI_FERCA,NOM,NOM_COMERCIAL,DATA_FUNDACIO,DATA_INGRES,TITULAR,CARREC_TITULAR,DATA_NAIXEMENT_TITULAR,PERSONA_CONTACTE,CARREC_PERSONA_CONTACTE,DOMICILI1,DOMICILI2,CP,POBLACIO,TELEFONS,MOBILS,FAX,EMAIL,WEB,PUBLICAR_INFORMACIO_WEB,REGISTRE_INDUSTRIAL,NUMERO_SS,IMPORT_POLISSA_RC,NUM_TREBALLADORS,ISO,CLASSIFICACIO,ESTAT_PLA_RENOVE,DATA_CREACIO,USUARI_CREACIO,DATA_ULT_MODIFICACIO,USUARI_ULT_MODIFICACIO,COMENTARIS,NIF_ORIGINAL from [Sheet 1$];");
ResultSet actRows=actStmt.executeQuery("select NIF_EMPRESA,CODI_FERCA,NOM,NOM_COMERCIAL,DATA_FUNDACIO,DATA_INGRES,TITULAR,CARREC_TITULAR,DATA_NAIXEMENT_TITULAR,PERSONA_CONTACTE,CARREC_PERSONA_CONTACTE,DOMICILI1,DOMICILI2,CP,/*POBLACIO,*/TELEFONS,MOBILS,FAX,EMAIL,/*WEB,*/PUBLICAR_INFORMACIO_WEB,REGISTRE_INDUSTRIAL,NUMERO_SS,IMPORT_POLISSA_RC,NUM_TREBALLADORS,ISO,CLASSIFICACIO,DATA_ULT_MODIFICACIO from badInsertedRow.[Sheet 1];");
// ResultSet actRows=actStmt.executeQuery("select * from badInsertedRow.[Sheet 1];");
PreparedStatement accessStmt=accessCon.prepareStatement("INSERT INTO emprasso (nif_assoc/*NIF_EMPRESA?,nif_gremi*/,CODI_FERCA,NOM,nom_com,d_fundacio,d_ingres,TITULAR,carrec_t,d_naix,pers_c,carrec_c,dom1,dom2,CP,/*POBLACIO?,*/tels,tels_m,FAX,e_mail,/*WEB,PUBLICAR_INFORMACIO_WEB,*/notes,reg_ind,num_ss,i_pol_rc,num_tr,ISO,class,d_actual) ?;");
// PreparedStatement accessStmt=accessCon.prepareStatement("INSERT INTO emprasso (NIF_EMPRESA,CODI_FERCA,NOM,NOM_COMERCIAL,DATA_FUNDACIO,DATA_INGRES,TITULAR,CARREC_TITULAR,DATA_NAIXEMENT_TITULAR,PERSONA_CONTACTE,CARREC_PERSONA_CONTACTE,DOMICILI1,DOMICILI2,CP,POBLACIO,TELEFONS,MOBILS,FAX,e_mail,WEB,PUBLICAR_INFORMACIO_WEB,REGISTRE_INDUSTRIAL,NUMERO_SS,IMPORT_POLISSA_RC,NUM_TREBALLADORS,ISO,CLASSIFICACIO,ESTAT_PLA_RENOVE,DATA_CREACIO,USUARI_CREACIO,DATA_ULT_MODIFICACIO,USUARI_ULT_MODIFICACIO,COMENTARIS,NIF_ORIGINAL) ?;");
// PreparedStatement accessStmt=accessCon.prepareStatement("INSERT INTO emprasso (nif_assoc/*NIF_EMPRESA?,nif_gremi*/,CODI_FERCA,NOM,nom_com,d_fundacio,d_ingres,TITULAR,carrec_t,d_naix,pers_c,carrec_c,dom1,dom2,CP,/*POBLACIO?,*/tels,tels_m,FAX,e_mail,/*WEB,PUBLICAR_INFORMACIO_WEB,*/reg_ind,num_ss,i_pol_rc,num_tr,ISO,class,ESTAT_PLA_RENOVE,DATA_CREACIO,USUARI_CREACIO,d_actual,USUARI_ULT_MODIFICACIO,COMENTARIS,NIF_ORIGINAL) ?;");
// PreparedStatement accessStmt=accessCon.prepareStatement("INSERT INTO emprasso (NIF_EMPRESA,CODI_FERCA,NOM,NOM_COMERCIAL,DATA_FUNDACIO,DATA_INGRES,TITULAR,CARREC_TITULAR,DATA_NAIXEMENT_TITULAR,PERSONA_CONTACTE,CARREC_PERSONA_CONTACTE,DOMICILI1,DOMICILI2,CP,POBLACIO,TELEFONS,MOBILS,FAX,EMAIL,WEB,PUBLICAR_INFORMACIO_WEB,REGISTRE_INDUSTRIAL,NUMERO_SS,IMPORT_POLISSA_RC,NUM_TREBALLADORS,ISO,CLASSIFICACIO,ESTAT_PLA_RENOVE,DATA_CREACIO,USUARI_CREACIO,DATA_ULT_MODIFICACIO,USUARI_ULT_MODIFICACIO,COMENTARIS,NIF_ORIGINAL) ?;");
accessStmt.setObject(1,actRows);
accessStmt.executeUpdate();
accessStmt.close();
actStmt.close();
accessCon.close();
actCon.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:Corrupted Access 97 DB for some statements. |
HXTT Support |
2006-09-19 10:01:50 |
Maybe your test is based on an older package?
Passeed test with:
insert into mdb97.emprasso select * from "mdb97 with corrupted row.mdb".emprasso
update mdb97.emprasso set
notes='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
Antoni |
2006-09-20 00:41:43 |
Hi,
No data is currently being inserted to Notes column of mdb file.
The package used to generate mdb file was the last avaliable yesterday.
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
HXTT Support |
2006-09-20 01:07:15 |
What's your code for insert row to Access 97 DB?
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
Antoni |
2006-09-20 01:33:04 |
Hi,
This is my insertion method.
taula is table name where rows will be inserted
llistaCamps is row names where data will be inserted
llistaDataTypes is unused actually, it was the datatype list of each row
separadorCamps is the token between each row (the '$' simbol)
llistaRegistresTaula is data stored. One element for each row.
public synchronized void insertarRegistres(String taula, List llistaCamps, List llistaDataTypes, String separadorCamps, List llistaRegistresTaula) throws SQLException {
StringBuffer sentenciaSQL = new StringBuffer();
StringBuffer camps = new StringBuffer();
StringBuffer valors = new StringBuffer();
sentenciaSQL.append("INSERT INTO ").append(taula).append(" ");
camps.append("(");
valors.append("VALUES (");
//preparem la consulta
for(Iterator iterCamps=llistaCamps.iterator(); iterCamps.hasNext();) {
String camp = (String) iterCamps.next();
camps.append(camp).append(",");
valors.append("?").append(",");
}
sentenciaSQL.append(camps.substring(0,camps.length()-1));
sentenciaSQL.append(") ").append(valors.substring(0,valors.length()-1));
sentenciaSQL.append(")");
PreparedStatement sentPrep = connexio.prepareStatement(sentenciaSQL.toString());
try {
for (Iterator iter = llistaRegistresTaula.iterator(); iter.hasNext();) {
String registre = (String) iter.next();
String[] arrayFilaResultat = registre.split('\\' + separadorCamps);
List llistaValorsResultat = Arrays.asList(arrayFilaResultat);
//introduim els valors
int indexCol = 1;
for(Iterator iterValors=llistaValorsResultat.iterator()/*,iterTipusDada=llistaDataTypes.iterator()*/; iterValors.hasNext();) {
String valor = ((String) iterValors.next()).trim();
//Integer dataType = (Integer) iterTipusDada.next();
//insertarCamp(sentPrep, indexCol++, valor, dataType.intValue());
sentPrep.setString(indexCol++, valor);
}
sentPrep.executeUpdate();
}
} catch (SQLException sqle) {
log.error("Error en la execuci? de la sentencia " + sentenciaSQL.toString() + ". Causa: " + sqle.getMessage());
if(sqle.getNextException() != null) {
log.error("Mes informaci?: " + sqle.getNextException().getMessage());
}
throw sqle;
} finally {
try {
sentPrep.close();
} catch (SQLException sqle) {
//do nothing
}
sentPrep = null;
}
Statement sent = null;
try {
//realitzem reindexaci?
sentenciaSQL = new StringBuffer();
sentenciaSQL.append("REINDEX ALL ON ").append(taula).append(";");
sent = connexio.createStatement();
sent.execute(sentenciaSQL.toString());
} catch (SQLException sqle) {
log.error("Error en reindexaci? de la taula " + taula + ". Causa: " + sqle.getMessage());
if(sqle.getNextException() != null) {
log.error("Mes informaci?: " + sqle.getNextException().getMessage());
}
throw sqle;
} finally {
try {
sent.close();
} catch (SQLException sqle) {
//do nothing
}
sent = null;
}
}
I hope it may help.
Antoni
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
HXTT Support |
2006-09-20 06:12:45 |
Thranslated your sql from Oracle -> Ms Access 97.
insert into "mdb97 with corrupted row.mdb".emprasso (NIF_ASSOC, NIF_GREMI, CODI_FERCA, NOM, nom_com,d_fundacio, d_ingres, TITULAR, carrec_t, d_naix, pers_c,carrec_c,dom1,dom2,CP, tels,tels_m,FAX,e_mail,reg_ind,num_ss,i_pol_rc,num_tr,ISO,class,notes,EXTRA_1,EXTRA_2,EXTRA_3,EXTRA_4,EXTRA_5,EXTRA_6,EXTRA_7,EXTRA_8,d_actual,ESBORRAT,RISC,VIAFAX,POBLACIO) values(RPAD('36930833D',12),RPAD('36930833',12),RPAD('110600196',12), rpad('Muñoz Pendon, Juan',40),rpad('',40),'2011/11/11','1992-03-04', rpad('Munoz Pendon, Juan',40),rpad('',30),'1958-5-11', rpad('',40),rpad('',30), rpad('Rambla Cazador, 1-3 Tda. 44',40),rpad('',40), rpad('08042',8), rpad('93 4282987',60),rpad('639324266',30),rpad('93 4279181',30),rpad('',40), rpad('080142694',20),rpad('08089996723',20),rpad('0.00',10),rpad('0.00',10),rpad('',10), rpad('',40),rpad('',1),rpad('',64),rpad('',64),rpad('',64), rpad('',12),rpad('',12),rpad('',12),rpad('',12),rpad('',12), '19/09/2006',rpad('0',1),rpad('0',10),rpad('0',1),rpad('Barcelona',32));
insert into "mdb97".emprasso (NIF_ASSOC, NIF_GREMI, CODI_FERCA, NOM, nom_com,d_fundacio, d_ingres, TITULAR, carrec_t, d_naix, pers_c,carrec_c,dom1,dom2,CP, tels,tels_m,FAX,e_mail,reg_ind,num_ss,i_pol_rc,num_tr,ISO,class,notes,EXTRA_1,EXTRA_2,EXTRA_3,EXTRA_4,EXTRA_5,EXTRA_6,EXTRA_7,EXTRA_8,d_actual,ESBORRAT,RISC,VIAFAX,POBLACIO) values(RPAD('36930833D',12),RPAD('36930833',12),RPAD('110600196',12), rpad('Muñoz Pendon, Juan',40),rpad('',40),'2011/11/11','1992-03-04', rpad('Munoz Pendon, Juan',40),rpad('',30),'1958-5-11', rpad('',40),rpad('',30), rpad('Rambla Cazador, 1-3 Tda. 44',40),rpad('',40), rpad('08042',8), rpad('93 4282987',60),rpad('639324266',30),rpad('93 4279181',30),rpad('',40), rpad('080142694',20),rpad('08089996723',20),rpad('0.00',10),rpad('0.00',10),rpad('',10), rpad('',40),rpad('',1),rpad('',64),rpad('',64),rpad('',64), rpad('',12),rpad('',12),rpad('',12),rpad('',12),rpad('',12), '19/09/2006',rpad('0',1),rpad('0',10),rpad('0',1),rpad('Barcelona',32));
According to my comparison for that corrupted row and the correct row, it should be a fixed bug in fact, but you're using an older package. Please run bot sql sample for your mdb file, then you should see two new inserted normal rows. If not, you should be using an older package.
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
Antoni |
2006-09-20 07:44:02 |
Hi,
I've made a test. The test has the folowwing steps:
-Deleted HXTT lib from Apache commons.
-Run process without library (got java.lang.ClassNotFoundException: com.hxtt.sql.access.AccessDriver)
-Downloaded latest packege version (Access JDBC 3.0 Package 941,551 2006-09-19 11:06 )
-Run process with library (got corrupted data)
I'm think I'm not using an older package.
Could I upload any other think to help you recur the issue?
Thanks,
Antoni
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
HXTT Support |
2006-09-20 19:16:44 |
>-Run process with library (got corrupted data)
Please try to run direct two sample sql to see whether your will get a corrupted row.
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
Antoni |
2006-09-21 02:49:04 |
Hi,
Harcoded two rows of data in insertarRegistre() method and tried to store.
if(taula.equalsIgnoreCase("emprasso")) {
llistaRegistresTaula = new ArrayList();
llistaRegistresTaula.add("36930833 $00000000 $110600196 $MU���OZ PENDON, JUAN $ $$1992/03/04$MUNOZ PENDON, JUAN $ $1958/05/11$ $ $RAMBLA CAZADOR, 1-3 TDA. 44 $ $08042 $93 4282987 $639324266 $93 4279181 $ $080142694 $08089996723 $ 0$ 0$ $ $ $ $ $ $ $ $ $ $ $2006/09/19$0$ 0$0$BARCELONA ");
llistaRegistresTaula.add("08590325 $00000000 $11160 $INSTALER, S.A. $ $1900/01/01$1900/01/01$FELICIANO LERMA AYLLON $ADMINISTRADOR $1939/02/02$ $ $MAESTRO CARBO, 4. $L?HOSPITALET DE LLOB. $08902 $933313698 $ $934226172 $ $08/116521 $08031443378 $ 0$ 0$ $ $ $ $ $ $ $ $ $ $ $2006/09/20$0$ 0$0$ ");
}
Got corrupted data.
I've upload the mdb file zipped at ftp.
I hope it may help,
Antoni
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
HXTT Support |
2006-09-21 22:40:39 |
>Harcoded two rows of data in insertarRegistre() method and tried to store.
Recurred and supported. Please download the latest package.
|
Re:Re:Re:Re:Re:Re:Re:Re:Corrupted Access 97 DB for some statements. |
Antoni |
2006-09-22 00:39:18 |
FIXED!!!
Thank you very much for your patience.
Antoni
|
|
|