Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v5.2
  HXTT Cobol v2.1
  HXTT DBF v5.2
  HXTT Excel v4.2
  HXTT Paradox v5.2
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Text(CSV) v5.2
  HXTT XML v1.2
Offshore Outsourcing
Oracle Data Import/Export
DB2 Data Import/Export
Sybase Data Import/Export
Free Resources
  Firewall Tunneling
  Search Indexing Robot
  Conditional Compilation
  Password Recovery for MS Access
  Password Recovery for Corel Paradox
  Checksum Tool for MD5
  Character Set Converter
  Pyramid - Poker of ZYH
   
   
   
Hongxin Technology & Trade Ltd. of Xiangtan City (abbr, HXTT)

HXTT Paradox
Problems with setting the content of a Memo
Pablo Sanz
2006-04-07 12:23:31.0
Hello, my name is Pablo Sanz and I am in charge of the computing section at the Servei de Lleng��es of the Universitat Aut��noma de Barcelona. We've just purchased your product for Paradox.

We have a web application running against a (main) Firebird/Interbase database. We also have a Delphi version of the same product running against a Paradox 7 database.

From time to time we have to update the Paradox version from the Firebird one. Up to now the update process was rather cumbersome, but fortunately we have your product.

Everything seems to go ok. We list some records. No problem. We update them from the Firebird DB. Apparently no problem. We try to list the just updated records and then we get this error:

java.sql.SQLException: type 03 blob at 24576 is unexpected.

We think the problem has to do with te Memo fields. How do you treat them? like Clobs, like Strings, like Blobs?

From Firebird it's very easy. We can extract the info with getString, but it seems we can't put it into Paradox with setString.

What should we do? Can you help us?

Re:Problems with setting the content of a Memo
HXTT Support
2006-04-07 20:48:39.0
According to the error information, HXTT Paradox met an unexpected data at the 24576 postion of atable.mb file so that it failed to refuse that blob value because it thought it's invalid value. If possible, you can send us the zipped paradox sample to webmaster@hxtt.com. BTW, Delphi product is updating that database when HXTT Paradox are reading data from that file?
Re:Re:Problems with setting the content of a Memo
HXTT Support
2006-04-07 23:16:44.0
Dear Pablo Sanz,

Please download and try the latest package, which has complemented code for that unexpected occasion. Your issue should disappear now. If not, please email us a sample.
Re:Re:Re:Problems with setting the content of a Memo
Pablo Sanz
2006-04-09 11:03:07.0
Hello, it seems the problem has been fixed. I don't get the

java.sql.SQLException: type 03 blob at 24576 is unexpected.

anymore. I can even browse through the pdx database once the data has been imported.

Unfortunately I'll be off for a week and I won't be able to go thoroughly through the whole process until April the 18th. I'll tell you then if the problem is definitely sorted out.


Thanks a lot
Re:Re:Re:Re:Problems with setting the content of a Memo
Pablo Sanz
2006-04-24 04:01:41.0
Hello again.

After a short break I'm back at work, and after having gone through the whole process, I think that unfortunately the migration of data from Interbase/Firebird is not going on perfect.

I have a set of records in Paradox which have to be updated. The main fields are:

Oid: long
frase: Memo
a: Memo
b: Memo
c: Memo
d: Memo
e: Memo
sol: string
nivel: long
idtipo: long

This is what happens:

1. Before updating them I list them and everything goes ok. All the information from the original Paradox table is recovered correctly.
2. I update them from the Interbase/Firebird database.
3. If I revise all the records using the Delphi SQL Explorer I happen to see that one of the Memos is empty. If I have a look at that record using the old Delphi app (the one we used to modify, update... the database), I get the error: BLOB has been modified!
4. If I list the records using the java app there seem to be:
5 A Memos empty
1 B Memo empty
2 C Memos empty
5 D Memos empty
29 frase Mmeos empty.

In fact, the updated information seems to be in the table, because if I browse through the records using the SQL Explorer, all the Memos seem ok.

This are the java methos involved in extracting the info:

public void extractItem() {
records.clear();
try {
PreparedStatement ps = null;
ps = conx.prepareStatement("select * from crac where oid=?");
ps.setLong(1, this.pool);

ResultSet res = ps.executeQuery();

while(res.next()) {
ICracRecord icr = new CracRecordExt(res);
records.add(icr);
}

ps.close();
res.close();
conx.close();
} catch (SQLException sqle) {
MyMesses.warn(("Imposible obtener los datos para el ��tem: " + this.pool));
sqle.printStackTrace();
}
}

...

public CracRecordExt(ResultSet res) throws SQLException {
super();
setFrase(res.getString(5));
setA(res.getString(10));
setB(res.getString(11));
setC(res.getString(12));
setD(res.getString(13));
setE(res.getString(14));
setSol(res.getString(15));
setNivel(String.valueOf(res.getString(16)));
setIdTipo(res.getLong(17));

id = res.getLong(1);
idInfo = res.getLong(4);
idAudio = res.getLong(6);
idVideo = res.getLong(7);
idImagen = res.getLong(8);
idTexto = res.getLong(9);
idExer = res.getLong(18);
idLengua = res.getLong(19);
orden = res.getLong(20);
nul = "";
}

...

CracRecord() {
idTipo = 0;
frase = "";
a = "";
b = "";
c = "";
d = "";
e = "";
sol = "";
nivel = "";
}

When debugging I've checked out that res.getString(5) (corresponding to Frase) is empty.


Pablo Sanz
Re:Re:Re:Re:Re:Problems with setting the content of a Memo
HXTT Support
2006-04-24 08:18:31.0
I understood that your issue is:
1st. Updated some memo fields by update sql through HXTT Access.
2nd. SQL Explorer can fetch all memo values normally.
3rd. Your old Delphi app will prompt "BLOB has been modified" error.
4th. HXTT Access can't see those value again.

We will recur your issue soon. Thanks for your patience.
Re:Re:Re:Re:Re:Re:Problems with setting the content of a Memo
HXTT Support
2006-04-24 19:55:35.0
Please download the latest package, which should work normal.
Re:Re:Re:Re:Re:Re:Re:Problems with setting the content of a Memo
Pablo Sanz
2006-04-25 08:11:48.0
Once downloaded the new version, I'm sorry to tell you that the problem still persists. I've followed the same actions and I've gotten the same results.

I've done two more things that could help you understand the problems:

If I modify the Fb with

update crac
set frase='EOOOOOOOOOOOOOOOOOOOOOOOOO'
where idtipo=121

and update the pdx DB there is no 'frase' empty, but there are some 'A' empty (and probably B's, C's and D's.


If I modify the Fb with

update crac
set frase='EOOOOOOOOOOOOOOOOOOOOOOOOO',
a = 'AAAAAAAAAAAAAAAAAAAAAAAA',
b = 'BBBBBBBBBBBBBBBBBBBBBB',
c='CCCCCCCCCCCCCCCCCCCCCCCC',
d='DDDDDDDDDDDDDDDDDD'
where idtipo=121

and update the pdx DB THEN there are no differences.

I don't know if it'd be better to send you a sample of the DB's. Let me know if so.
Re:Re:Re:Re:Re:Re:Re:Re:Problems with setting the content of a Memo
HXTT Supports
2006-04-25 20:33:54.0
Passed test with the below sql:
create table crac (Oid int, frase longvarchar(5),a longvarchar(10),b longvarchar(10), c longvarchar(10), d longvarchar(10), e longvarchar, sol varchar(20) ,nivel int, idtipo int);
insert into crac (idtipo,frase,a,b,c,d) values(121,'frfffff','aaasaasfsfds','bbbbbbb','ccdfdfssdfc','ddddffdfdsd');
update crac set frase='EOOOOOOOOOOOOOOOOOOOOOOOOO' where idtipo=121;
update crac set frase='EOOOOOOOOOOOOOOOOOOOOOOOOO', a = 'AAAAAAAAAAAAAAAAAAAAAAAA', b = 'BBBBBBBBBBBBBBBBBBBBBB', c='CCCCCCCCCCCCCCCCCCCCCCCC', d='DDDDDDDDDDDDDDDDDD' where idtipo=121;
select * from crac where idtipo=121 ;

SQL Explorer can browse all memo data befor/after update sql. Because SQL Explorer holds table cache in memory, you can see the changes at once after update, but if you reopen it(or switch to browse another table, then back), you can see those changes.

Re:Re:Re:Re:Re:Re:Re:Re:Problems with setting the content of a Memo
Pablo Sanz
2006-04-26 03:13:44.0
I'm sorry, but it definitely doesn't work.

I've created from scratch the crac.db table adding all the necessary fields.
So I have an empty crac.db table.

private void create() {
try {
PreparedStatement ps = null;
ps = pdx.getConx().prepareStatement("create table crac " +
"(Oid int, idinfo int, frase longvarchar(5), " +
"a longvarchar(10), idaudio int, idvideo int, idimagen int, " +
"idtexto int, b longvarchar(10), c longvarchar(10), d longvarchar(10), " +
"e longvarchar(10), sol varchar(20) , nivel int, idexer int, idlengua int, " +
"idtipo int, orden int)");


int ret = ps.executeUpdate();

ps.close();
pdx.getConx().close();
} catch (SQLException sqle) {
sqle.printStackTrace();
}
}


I've created a new method, similar to the update method, to copy all the records from the Fb table into the Pdx table.


public int insertIntoCrac(Collection lista) {
int ret = 0;
try {
PreparedStatement ps = conx.prepareStatement("insert into crac " +
"(oid, idinfo, frase, idaudio, idvideo, idimagen, " +
"idtexto, a, b, c, d, e, sol, nivel, idexer, " +
"idlengua, idtipo, orden) " +
"values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

for(Iterator it = lista.iterator(); it.hasNext(); ) {
ICracRecord icr = (ICracRecord)it.next();
ps.setLong(1, icr.getId());
ps.setLong(2, icr.getIdInfo());
ps.setString(3, icr.getFrase());
ps.setLong(4, icr.getIdAudio());
ps.setLong(5, icr.getIdVideo());
ps.setLong(6, icr.getIdImagen());
ps.setLong(7, icr.getIdTexto());
ps.setString(8, icr.getA().trim());
ps.setString(9, icr.getB().trim());
ps.setString(10, icr.getC().trim());
ps.setString(11, icr.getD().trim());
ps.setString(12, icr.getE().trim());
ps.setString(13, icr.getSol());
ps.setString(14, icr.getNivel());
ps.setLong(15, icr.getIdExer());
ps.setLong(16, icr.getIdLengua());
ps.setLong(17, icr.getIdTipo());
ps.setLong(18, icr.getOrden());
System.out.println("Copiado Oid: " + icr.getId());

ret += ps.executeUpdate();
}

ps.close();
conx.commit();
conx.close();
return ret;
} catch (SQLException sqle) {
sqle.printStackTrace();
return -1;
}
}

I proceed to copy items. In theory they get copied but when I try to get them through the java app I get this error message:


java.sql.SQLException: Failed to get a long value from [B: [B@15925c0
at com.hxtt.global.SQLState.SQLException(Unknown Source)
at com.hxtt.global.SQLState.transferValueException(Unknown Source)
at com.hxtt.global.ac.else(Unknown Source)
at com.hxtt.sql.cg.getLong(Unknown Source)
at conversorItems.CracRecordExt.(CracRecordExt.java:64)
at conversorItems.Paradox.extract(Paradox.java:117)


Extra info: if I do the 'copy' process with the SQLExplorer open I can't see the data even if I refresh the data (which is not a normal behaviour for the SQLExplorer. It always shows new changes when refreshing from an external app). I have to close the connection and open it again.
Re:Re:Re:Re:Re:Re:Re:Re:Problems with setting the content of a Memo
HXTT Support
2006-04-26 07:01:44.0
>Failed to get a long value from [B: [B@15925c0
It means that HXTT Paradox failed to get a long value from a longvarchar column.
Your code is incorrect on column map. Maybe you have only 18 columns in crac table, but try to access 20 columns.
>select * from crac where oid=?
should be "select oid,2,3,idinfo,frase,idaudio,idvideo,idimagen,idtexto,a,b,c,d,e,sol,nivel,idtipo,idexer,idlengua,orden from crac where oid=?"
Of course you can use "select * from crac where oid=?", but you should change your code from
>setFrase(res.getString(5));
>setA(res.getString(10));
>...
to
setFrase(res.getString("frase"));
setA(res.getString("a"));
...
getString(columnName) is better solution to avoid the possibe wrong column map.

>Extra info: if I do the 'copy' process with the SQLExplorer open I can't see
>the data even if I refresh the data (which is not a normal behaviour for the
> SQLExplorer. It always shows new changes when refreshing from an external
> app). I have to close the connection and open it again.
It's normal. HXTT Paradox is designed to use the same database format with Corel Paradox, but doesn't support to cooperate with SQL Explorer or other application at the same time. That unsupported feature need to know and simulate the unkown native database protocol, and notify SQL Explorer to flush table cache.


Re:Re:Re:Re:Re:Re:Re:Re:Problems with setting the content of a Memo
Pablo Sanz
2006-04-26 09:25:47.0
Ok, sorry! I was so eager to simplify the test that I made silly mistakes in the order of fields.

The actual crac.db structure is (Paradox):

Oid int
vs int
cid int
idinfo int
frase longvarchar(10)
idaudio int
idvideo int
idimagen int
idtexto int
a longvarchar(10)
b longvarchar(10)
c longvarchar(10)
d longvarchar(10)
e longvarchar(10)
sol varchar(1)
nivel int
idtipo int
idexer int
idlengua int
orden int

and the Firebird is:

OID INTEGER NOT NULL,
IDINFO INTEGER,
FRASE BLOB SUB_TYPE 1 SEGMENT SIZE 80,
IDAUDIO INTEGER,
IDVIDEO INTEGER,
IDIMAGEN INTEGER,
IDTEXTO INTEGER,
A BLOB SUB_TYPE 1 SEGMENT SIZE 80,
B BLOB SUB_TYPE 1 SEGMENT SIZE 80,
C BLOB SUB_TYPE 1 SEGMENT SIZE 80,
D BLOB SUB_TYPE 1 SEGMENT SIZE 80,
E BLOB SUB_TYPE 1 SEGMENT SIZE 80,
SOL VARCHAR(1),
NIVEL SMALLINT,
IDTIPO INTEGER,
IDEXER INTEGER,
IDLENGUA INTEGER,
ORDEN INTEGER,
NUL SMALLINT

In spite of them being slightly different, the classes that define each structure work ok, at least for the Firebird DB. There's been no problem in inserting and retrieving data so far.

I've even modified the way the ResulSet values are assigned as you suggest. I'm now using the column name.

Now it seems the trasnference of items is ok, but still, when I try to retrieve the info from the Pdx table I get the same problems:

35 Frase fields empty
5 A fields empty
1 B fields empty
2 C fields empty
5 D fields empty


I've observed that if all the Frase, A, B, C and D fields for each record contain the same information, then once it's been exported into the Pdx table, it can be retrieved without problems.

I've also observed that if I modify manually (in the Firebird DB) one of the fields that cannot be retrieved from the Pdx DB, and proceede with the update again, then that field CAN BE RETRIEVED PROPERLY!!!

And the final evidence. I've prepared a print method to print directly from the DB:

private void print() {
try {
PreparedStatement ps = null;
ps = this.pdx.getConx().prepareStatement("select * from crac where idtipo=121");

ResultSet res = ps.executeQuery();
while (res.next()) {
System.out.println("Oid: " + res.getLong("oid"));
System.out.println("Fra: " + res.getString("frase"));
System.out.println("A : " + res.getString("a"));
System.out.println("B : " + res.getString("b"));
System.out.println("C : " + res.getString("c"));
System.out.println("D : " + res.getString("d"));
System.out.println("E : " + res.getString("e"));
}

ps.close();
pdx.getConx().close();
} catch (SQLException sqle) {
sqle.printStackTrace();
}
}

public static void main(String[] args) {
TestPdx tpx = new TestPdx("c:\\tmp\\crac");

tpx.print();
System.out.println("Proceso acabado correctamente");
}

The print-out of this is:

Oid: 47
Frase: null (Wrong! Real content --> Aren't you _______? Why don't we have a break for a few minutes?)
A : fresh
B : strong
C : tired
D : calm
E : null (Ok. Original field empty)
Oid: 55
Frase: Linda? She's about sixteen, with long curly ______ and brown eyes.
A : legs
B : arms
C : fingers
D : hair
E : null (Ok. Original field empty)

...

Oid: 558
Frase: null (Wrong! Real content --> Thousands of tons of ______ is produced by big cities every day.)
A : spare
B : waste
C : reject
D : refusal
E : null (Ok. Original field empty)


By the way, I'm using Paradox_JDBC30.jar (just in case!!)
Re:Re:Re:Re:Re:Re:Re:Re:Problems with setting the content of a Memo
Pablo Sanz
2006-04-26 09:30:05.0
One more thing!

If I modify manually (in the Paradox DB) one of the fields that cannot be retrieved from the Pdx DB, then that field CAN BE RETRIEVED PROPERLY!!!
Re:Re:Re:Re:Re:Re:Re:Re:Problems with setting the content of a Memo
HXTT Support
2006-04-26 09:39:17.0
It's strange, and we will try to recur your issue.

You can try Bulk Insert A ResultSet from any JDBC driver. If you succeeded in getting some correct rows, then you should have wrong code for icr.getFrase() .
Re:Re:Re:Re:Re:Re:Re:Re:Problems with setting the content of a Memo
HXTT Support
2006-04-26 09:41:40.0
BTW, please download the latest package of today.
Re:Re:Re:Re:Re:Re:Re:Re:Problems with setting the content of a Memo
Pablo Sanz
2006-04-26 10:09:24.0
I've created these two methods to insert, brute force, the resultset from the Firebird table as you suggest

public void bruteForce() {
//rs is an open ResultSet from any JDBC driver.
String sql="insert into crac ?;";
PreparedStatement pstmt;
try {
pstmt = conx.prepareStatement(sql);

pstmt.setObject(1, getRs());//insert a resultSet into table test.
pstmt.executeUpdate();

pstmt.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}

private ResultSet getRs() {
try {
PreparedStatement ps = conx.prepareStatement("select oid, 0 as vs, 0 as cid, " +
"idinfo, frase, " +
"idaudio, idvideo, idimagen, idtexto, a, b, c, d, e, sol, nivel," +
"idtipo, idexer, idlengua, orden " +
"from crac where idtipo=?");
ps.setLong(1, 121);
ResultSet res = ps.executeQuery();

return res;
} catch (SQLException sqle) {
MyMesses.warn("Error SQL!");
sqle.printStackTrace();
return null;
}
}

The result is NO INSERTIONS AT ALL!!!

I insist. Would it be of any help if I send you DB samples?
Re:Re:Re:Re:Re:Re:Re:Re:Problems with setting the content of a Memo
Pablo Sanz
2006-04-26 10:24:45.0
I've created these two methods to insert, brute force, the resultset from the Firebird table as you suggest

public void bruteForce() {
//rs is an open ResultSet from any JDBC driver.
String sql="insert into crac ?;";
PreparedStatement pstmt;
try {
pstmt = conx.prepareStatement(sql);

pstmt.setObject(1, getRs());//insert a resultSet into table test.
pstmt.executeUpdate();

pstmt.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}

private ResultSet getRs() {
try {
PreparedStatement ps = conx.prepareStatement("select oid, 0 as vs, 0 as cid, " +
"idinfo, frase, " +
"idaudio, idvideo, idimagen, idtexto, a, b, c, d, e, sol, nivel," +
"idtipo, idexer, idlengua, orden " +
"from crac where idtipo=?");
ps.setLong(1, 121);
ResultSet res = ps.executeQuery();

return res;
} catch (SQLException sqle) {
MyMesses.warn("Error SQL!");
sqle.printStackTrace();
return null;
}
}

The result is NO INSERTIONS AT ALL!!!

I insist. Would it be of any help if I send you DB samples?
Re:Re:Re:Re:Re:Re:Re:Re:Problems with setting the content of a Memo
HXTT Support
2006-04-26 19:02:20.0
You can send your samples to webmaster@hxtt.com or webmaster.hxtt@gmail.com

Search Key   Search by Last 50 Questions




Google
 

Address: 9 Station Rd., Xiangtan City, Hunan Province, P.R. China
Postcode: 411100
Phone: (86)731-58225727
Fax: (86)731-58225727
Email: webmaster@hxtt.com
Copyright © 1999-2011 Hongxin Technology & Trade Ltd. | All Rights Reserved. | Privacy | Legal | Sitemap