java.sql.SQLSyntaxErrorException: incompatible data type in conversion with HSQLDB but not with Oracle.

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

java.sql.SQLSyntaxErrorException: incompatible data type in conversion with HSQLDB but not with Oracle.

Gabriel Rossetti-6
Hi all,
 
I’m writing unit tests and using HSQLDB as my test DB. I have turned on Oracle compatibility and it’s been working quite well so far. I am getting the following error :
 
java.sql.SQLSyntaxErrorException: incompatible data type in conversion
 
when setting a string to a RAW oracle type. I have also tried setting bytes, I always get the same error. I have tried setting the Oracle DB as the test DB to see if I get the same error and I don’t, so it looks like an HSQLDB problem, please correct me if I’m wrong. Does anyone have an idea of why this is happening?
 
Thanks,
Gabriel
 

  ________________________________  

This email and any attachments are confidential and access to this email or attachment by anyone other than the addressee is unauthorised. If you are not the intended recipient please notify the sender and delete the email including any attachments. You must not disclose or distribute any of the contents to any other person. Personal views or opinions are solely those of the author and not of Trafigura. Trafigura does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. By communicating with anyone at Trafigura by email, you consent to the monitoring or interception of such email by Trafigura in accordance with its internal policies. Unless otherwise stated, any pricing information given in this message is indicative only, is subject to change and does not constitute an offer to deal at any price quoted.

------------------------------------------------------------------------------
Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create
new or port existing apps to sell to consumers worldwide. Explore the
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: java.sql.SQLSyntaxErrorException: incompatible data type in conversion with HSQLDB but not with Oracle.

Fred Toussi-2
I checked the translation of the RAW type and found an error. This has just been fixed and committed to SVN /base/trunk (the class is org.hsqldb.ParserDQL)
 
With the correction, RAW(lengh) is translated to VARBINARY(length). You can assign a byte[] to a column of this type with a JDBC PreparedStatement, or if you are using a direct SQL statement, use a binary string such as X'abcdef0123456789'.
 
If your usage is different, please report and I'll try to support it.
 
Thanks for reporting.
 
Fred
 
 
On Wed, Dec 21, 2011, at 13:38, Gabriel Rossetti wrote:
Hi all,
 
I’m writing unit tests and using HSQLDB as my test DB. I have turned on Oracle compatibility and it’s been working quite well so far. I am getting the following error :
 
java.sql.SQLSyntaxErrorException: incompatible data type in conversion
 
when setting a string to a RAW oracle type. I have also tried setting bytes, I always get the same error. I have tried setting the Oracle DB as the test DB to see if I get the same error and I don’t, so it looks like an HSQLDB problem, please correct me if I’m wrong. Does anyone have an idea of why this is happening?
 
Thanks,
Gabriel
 
 
  ________________________________  

This email and any attachments are confidential and access to this email or attachment by anyone other than the addressee is unauthorised. If you are not the intended recipient please notify the sender and delete the email including any attachments. You must not disclose or distribute any of the contents to any other person. Personal views or opinions are solely those of the author and not of Trafigura. Trafigura does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. By communicating with anyone at Trafigura by email, you consent to the monitoring or interception of such email by Trafigura in accordance with its internal policies. Unless otherwise stated, any pricing information given in this message is indicative only, is subject to change and does not constitute an offer to deal at any price quoted.
------------------------------------------------------------------------------
Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create 
new or port existing apps to sell to consumers worldwide. Explore the 
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

 

------------------------------------------------------------------------------
Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create
new or port existing apps to sell to consumers worldwide. Explore the
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: java.sql.SQLSyntaxErrorException: incompatible data type in conversion with HSQLDB but not with Oracle.

Gabriel Rossetti-6

Ok, thank you!

Gabriel

 

From: Fred Toussi [mailto:[hidden email]]
Sent: 21 December 2011 15:42
To: User discussions about HyperSQL Database Engine.
Subject: Re: [Hsqldb-user] java.sql.SQLSyntaxErrorException: incompatible data type in conversion with HSQLDB but not with Oracle.

 

I checked the translation of the RAW type and found an error. This has just been fixed and committed to SVN /base/trunk (the class is org.hsqldb.ParserDQL)

 

With the correction, RAW(lengh) is translated to VARBINARY(length). You can assign a byte[] to a column of this type with a JDBC PreparedStatement, or if you are using a direct SQL statement, use a binary string such as X'abcdef0123456789'.

 

If your usage is different, please report and I'll try to support it.

 

Thanks for reporting.

 

Fred

 

 

On Wed, Dec 21, 2011, at 13:38, Gabriel Rossetti wrote:

Hi all,

 

I’m writing unit tests and using HSQLDB as my test DB. I have turned on Oracle compatibility and it’s been working quite well so far. I am getting the following error :

 

java.sql.SQLSyntaxErrorException: incompatible data type in conversion

 

when setting a string to a RAW oracle type. I have also tried setting bytes, I always get the same error. I have tried setting the Oracle DB as the test DB to see if I get the same error and I don’t, so it looks like an HSQLDB problem, please correct me if I’m wrong. Does anyone have an idea of why this is happening?

 

Thanks,

Gabriel

 

 

  ________________________________  


This email and any attachments are confidential and access to this email or attachment by anyone other than the addressee is unauthorised. If you are not the intended recipient please notify the sender and delete the email including any attachments. You must not disclose or distribute any of the contents to any other person. Personal views or opinions are solely those of the author and not of Trafigura. Trafigura does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. By communicating with anyone at Trafigura by email, you consent to the monitoring or interception of such email by Trafigura in accordance with its internal policies. Unless otherwise stated, any pricing information given in this message is indicative only, is subject to change and does not constitute an offer to deal at any price quoted.

 
------------------------------------------------------------------------------
Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create 
new or port existing apps to sell to consumers worldwide. Explore the 
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev
 
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
 

 


------------------------------------------------------------------------------
Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create
new or port existing apps to sell to consumers worldwide. Explore the
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: java.sql.SQLSyntaxErrorException: incompatible data type in conversion with HSQLDB but not with Oracle.

Gabriel Rossetti-6

(Sorry if you get this twice, I got my original msg back)

 

Oh, and forgot to ask, will this fix be out in the next version? If so do you already have a release schedule?

 

Thanks!

Gabriel

 

 

From: Gabriel Rossetti
Sent: 21 December 2011 15:44
To: User discussions about HyperSQL Database Engine.
Cc: Gabriel Rossetti
Subject: RE: [Hsqldb-user] java.sql.SQLSyntaxErrorException: incompatible data type in conversion with HSQLDB but not with Oracle.

 

Ok, thank you!

Gabriel

 

From: Fred Toussi [mailto:[hidden email]]
Sent: 21 December 2011 15:42
To: User discussions about HyperSQL Database Engine.
Subject: Re: [Hsqldb-user] java.sql.SQLSyntaxErrorException: incompatible data type in conversion with HSQLDB but not with Oracle.

 

I checked the translation of the RAW type and found an error. This has just been fixed and committed to SVN /base/trunk (the class is org.hsqldb.ParserDQL)

 

With the correction, RAW(lengh) is translated to VARBINARY(length). You can assign a byte[] to a column of this type with a JDBC PreparedStatement, or if you are using a direct SQL statement, use a binary string such as X'abcdef0123456789'.

 

If your usage is different, please report and I'll try to support it.

 

Thanks for reporting.

 

Fred

 

 

On Wed, Dec 21, 2011, at 13:38, Gabriel Rossetti wrote:

Hi all,

 

I’m writing unit tests and using HSQLDB as my test DB. I have turned on Oracle compatibility and it’s been working quite well so far. I am getting the following error :

 

java.sql.SQLSyntaxErrorException: incompatible data type in conversion

 

when setting a string to a RAW oracle type. I have also tried setting bytes, I always get the same error. I have tried setting the Oracle DB as the test DB to see if I get the same error and I don’t, so it looks like an HSQLDB problem, please correct me if I’m wrong. Does anyone have an idea of why this is happening?

 

Thanks,

Gabriel

 

 

  ________________________________  


This email and any attachments are confidential and access to this email or attachment by anyone other than the addressee is unauthorised. If you are not the intended recipient please notify the sender and delete the email including any attachments. You must not disclose or distribute any of the contents to any other person. Personal views or opinions are solely those of the author and not of Trafigura. Trafigura does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. By communicating with anyone at Trafigura by email, you consent to the monitoring or interception of such email by Trafigura in accordance with its internal policies. Unless otherwise stated, any pricing information given in this message is indicative only, is subject to change and does not constitute an offer to deal at any price quoted.

 
------------------------------------------------------------------------------
Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create 
new or port existing apps to sell to consumers worldwide. Explore the 
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev
 
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
 

 


------------------------------------------------------------------------------
Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create
new or port existing apps to sell to consumers worldwide. Explore the
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: java.sql.SQLSyntaxErrorException: incompatible data type in conversion with HSQLDB but not with Oracle.

Fred Toussi-2
Yes, the fix will be in the next release. We release when there are no issues with the code, rather than forcing a release date. The next release is likely to come out very soon.
 
However, building the jar from the trunk is very easy using Gradle.
 
Fred
 
On Wed, Dec 21, 2011, at 14:51, Gabriel Rossetti wrote:

(Sorry if you get this twice, I got my original msg back)

 

Oh, and forgot to ask, will this fix be out in the next version? If so do you already have a release schedule?

 

Thanks!

Gabriel

 

 

From: Gabriel Rossetti
Sent: 21 December 2011 15:44
To: User discussions about HyperSQL Database Engine.
Cc: Gabriel Rossetti
Subject: RE: [Hsqldb-user] java.sql.SQLSyntaxErrorException: incompatible data type in conversion with HSQLDB but not with Oracle.

 

Ok, thank you!

Gabriel

 

From: Fred Toussi [mailto:[hidden email]]
Sent: 21 December 2011 15:42
To: User discussions about HyperSQL Database Engine.
Subject: Re: [Hsqldb-user] java.sql.SQLSyntaxErrorException: incompatible data type in conversion with HSQLDB but not with Oracle.

 

I checked the translation of the RAW type and found an error. This has just been fixed and committed to SVN /base/trunk (the class is org.hsqldb.ParserDQL)

 

With the correction, RAW(lengh) is translated to VARBINARY(length). You can assign a byte[] to a column of this type with a JDBC PreparedStatement, or if you are using a direct SQL statement, use a binary string such as X'abcdef0123456789'.

 

If your usage is different, please report and I'll try to support it.

 

Thanks for reporting.

 

Fred

 

 

On Wed, Dec 21, 2011, at 13:38, Gabriel Rossetti wrote:

Hi all,

 

I’m writing unit tests and using HSQLDB as my test DB. I have turned on Oracle compatibility and it’s been working quite well so far. I am getting the following error :

 

java.sql.SQLSyntaxErrorException: incompatible data type in conversion

 

when setting a string to a RAW oracle type. I have also tried setting bytes, I always get the same error. I have tried setting the Oracle DB as the test DB to see if I get the same error and I don’t, so it looks like an HSQLDB problem, please correct me if I’m wrong. Does anyone have an idea of why this is happening?

 

Thanks,

Gabriel

 

 

  ________________________________  


This email and any attachments are confidential and access to this email or attachment by anyone other than the addressee is unauthorised. If you are not the intended recipient please notify the sender and delete the email including any attachments. You must not disclose or distribute any of the contents to any other person. Personal views or opinions are solely those of the author and not of Trafigura. Trafigura does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. By communicating with anyone at Trafigura by email, you consent to the monitoring or interception of such email by Trafigura in accordance with its internal policies. Unless otherwise stated, any pricing information given in this message is indicative only, is subject to change and does not constitute an offer to deal at any price quoted.

 
------------------------------------------------------------------------------
Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create 
new or port existing apps to sell to consumers worldwide. Explore the 
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev
 
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
 

 

------------------------------------------------------------------------------
Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create 
new or port existing apps to sell to consumers worldwide. Explore the 
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

 

------------------------------------------------------------------------------
Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create
new or port existing apps to sell to consumers worldwide. Explore the
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: java.sql.SQLSyntaxErrorException: incompatible data type in conversion with HSQLDB but not with Oracle.

Gabriel Rossetti-6

Ok, thank you Fred!

 

Cheers,

Gabriel

 

From: Fred Toussi [mailto:[hidden email]]
Sent: 21 December 2011 16:19
To: User discussions about HyperSQL Database Engine.
Subject: Re: [Hsqldb-user] java.sql.SQLSyntaxErrorException: incompatible data type in conversion with HSQLDB but not with Oracle.

 

Yes, the fix will be in the next release. We release when there are no issues with the code, rather than forcing a release date. The next release is likely to come out very soon.

 

However, building the jar from the trunk is very easy using Gradle.

 

Fred

 

On Wed, Dec 21, 2011, at 14:51, Gabriel Rossetti wrote:

(Sorry if you get this twice, I got my original msg back)

 

Oh, and forgot to ask, will this fix be out in the next version? If so do you already have a release schedule?

 

Thanks!

Gabriel

 

 

From: Gabriel Rossetti
Sent: 21 December 2011 15:44
To: User discussions about HyperSQL Database Engine.
Cc: Gabriel Rossetti
Subject: RE: [Hsqldb-user] java.sql.SQLSyntaxErrorException: incompatible data type in conversion with HSQLDB but not with Oracle.

 

Ok, thank you!

Gabriel

 

From: Fred Toussi [mailto:[hidden email]]
Sent: 21 December 2011 15:42
To: User discussions about HyperSQL Database Engine.
Subject: Re: [Hsqldb-user] java.sql.SQLSyntaxErrorException: incompatible data type in conversion with HSQLDB but not with Oracle.

 

I checked the translation of the RAW type and found an error. This has just been fixed and committed to SVN /base/trunk (the class is org.hsqldb.ParserDQL)

 

With the correction, RAW(lengh) is translated to VARBINARY(length). You can assign a byte[] to a column of this type with a JDBC PreparedStatement, or if you are using a direct SQL statement, use a binary string such as X'abcdef0123456789'.

 

If your usage is different, please report and I'll try to support it.

 

Thanks for reporting.

 

Fred

 

 

On Wed, Dec 21, 2011, at 13:38, Gabriel Rossetti wrote:

Hi all,

 

I’m writing unit tests and using HSQLDB as my test DB. I have turned on Oracle compatibility and it’s been working quite well so far. I am getting the following error :

 

java.sql.SQLSyntaxErrorException: incompatible data type in conversion

 

when setting a string to a RAW oracle type. I have also tried setting bytes, I always get the same error. I have tried setting the Oracle DB as the test DB to see if I get the same error and I don’t, so it looks like an HSQLDB problem, please correct me if I’m wrong. Does anyone have an idea of why this is happening?

 

Thanks,

Gabriel

 

 

  ________________________________  


This email and any attachments are confidential and access to this email or attachment by anyone other than the addressee is unauthorised. If you are not the intended recipient please notify the sender and delete the email including any attachments. You must not disclose or distribute any of the contents to any other person. Personal views or opinions are solely those of the author and not of Trafigura. Trafigura does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. By communicating with anyone at Trafigura by email, you consent to the monitoring or interception of such email by Trafigura in accordance with its internal policies. Unless otherwise stated, any pricing information given in this message is indicative only, is subject to change and does not constitute an offer to deal at any price quoted.

 
 
 
------------------------------------------------------------------------------
 
Write once. Port to many.
 
Get the SDK and tools to simplify cross-platform app development. Create 
 
new or port existing apps to sell to consumers worldwide. Explore the 
 
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
 
http://p.sf.net/sfu/intel-appdev
 
 
 
_______________________________________________
 
Hsqldb-user mailing list
 
[hidden email]
 
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
 
 

 

 
------------------------------------------------------------------------------
Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create 
new or port existing apps to sell to consumers worldwide. Explore the 
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev
 
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
 

 


------------------------------------------------------------------------------
Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create
new or port existing apps to sell to consumers worldwide. Explore the
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: java.sql.SQLSyntaxErrorException: incompatible data type in conversion with HSQLDB but not with Oracle.

Blaine Simpson
BTW, if you aren't a Subversion user, you can get what you heed by downloading
http://hsqldb.svn.sourceforge.net/viewvc/hsqldb/base/trunk/?view=tar
If you're on Windows, 7Zip, available for free, can gunzip the tar then extract
from the tar.


On 12/21/2011 10:23 AM, Gabriel Rossetti wrote:

>
> Ok, thank you Fred!
>
>  
>
> Cheers,
>
> Gabriel
>
>  
>
> *From:*Fred Toussi [mailto:[hidden email]]
> *Sent:* 21 December 2011 16:19
> *To:* User discussions about HyperSQL Database Engine.
> *Subject:* Re: [Hsqldb-user] java.sql.SQLSyntaxErrorException: incompatible
> data type in conversion with HSQLDB but not with Oracle.
>
>  
>
> Yes, the fix will be in the next release. We release when there are no issues
> with the code, rather than forcing a release date. The next release is likely
> to come out very soon.
>
>  
>
> However, building the jar from the trunk is very easy using Gradle.
>
>  
>
> Fred
>

------------------------------------------------------------------------------
Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create
new or port existing apps to sell to consumers worldwide. Explore the
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: java.sql.SQLSyntaxErrorException: incompatible data type in conversion with HSQLDB but not with Oracle.

Gabriel Rossetti-6
In reply to this post by Fred Toussi-2

Hi,

 

I’m still getting this, even after upgrading to 2.2.7:

 

Caused by: org.hsqldb.HsqlException: incompatible data type in conversion

 

When I try (in oracle mode) to set the value for a prepared statement:

 

string id = “D1CDB8FE163547ACB3DFB647826C5455”;

 

long  msb = new BigInteger(id.substring(0,16), 16).longValue();

long lsb = new BigInteger(id.substring(16,32), 16).longValue();

val bos: ByteArrayOutputStream = new ByteArrayOutputStream();

val dos : DataOutputStream  = new DataOutputStream(bos);

dos.writeLong(msb);

dos.writeLong(lsb);

dos.flush();

byte[] bytes = bos.toByteArray();

 

String query = "select COUNT(*) from MY_TABLE where ID = ?";

PreparedStatement stmt = conn.prepareStatement(query)

stmt.setBytes(1, bytes);

 

I checked and the type for the ID field is generated as a BIGINT and not a VARBINARY(length) as stated below. I have the mapping (annotations) setup as a UserType (that writes & reads a byte array) and I wonder if the DDL generation doesn’t understand that it’s supposed to be an oracle RAW datatype, and thus HSQLDB doesn’t generate the right type?

 

Thanks,

Gabriel

 

 

From: Fred Toussi [mailto:[hidden email]]
Sent: 21 December 2011 16:19
To: User discussions about HyperSQL Database Engine.
Subject: Re: [Hsqldb-user] java.sql.SQLSyntaxErrorException: incompatible data type in conversion with HSQLDB but not with Oracle.

 

Yes, the fix will be in the next release. We release when there are no issues with the code, rather than forcing a release date. The next release is likely to come out very soon.

 

However, building the jar from the trunk is very easy using Gradle.

 

Fred

 

On Wed, Dec 21, 2011, at 14:51, Gabriel Rossetti wrote:

(Sorry if you get this twice, I got my original msg back)

 

Oh, and forgot to ask, will this fix be out in the next version? If so do you already have a release schedule?

 

Thanks!

Gabriel

 

 

From: Gabriel Rossetti
Sent: 21 December 2011 15:44
To: User discussions about HyperSQL Database Engine.
Cc: Gabriel Rossetti
Subject: RE: [Hsqldb-user] java.sql.SQLSyntaxErrorException: incompatible data type in conversion with HSQLDB but not with Oracle.

 

Ok, thank you!

Gabriel

 

From: Fred Toussi [mailto:[hidden email]]
Sent: 21 December 2011 15:42
To: User discussions about HyperSQL Database Engine.
Subject: Re: [Hsqldb-user] java.sql.SQLSyntaxErrorException: incompatible data type in conversion with HSQLDB but not with Oracle.

 

I checked the translation of the RAW type and found an error. This has just been fixed and committed to SVN /base/trunk (the class is org.hsqldb.ParserDQL)

 

With the correction, RAW(lengh) is translated to VARBINARY(length). You can assign a byte[] to a column of this type with a JDBC PreparedStatement, or if you are using a direct SQL statement, use a binary string such as X'abcdef0123456789'.

 

If your usage is different, please report and I'll try to support it.

 

Thanks for reporting.

 

Fred

 

 

On Wed, Dec 21, 2011, at 13:38, Gabriel Rossetti wrote:

Hi all,

 

I’m writing unit tests and using HSQLDB as my test DB. I have turned on Oracle compatibility and it’s been working quite well so far. I am getting the following error :

 

java.sql.SQLSyntaxErrorException: incompatible data type in conversion

 

when setting a string to a RAW oracle type. I have also tried setting bytes, I always get the same error. I have tried setting the Oracle DB as the test DB to see if I get the same error and I don’t, so it looks like an HSQLDB problem, please correct me if I’m wrong. Does anyone have an idea of why this is happening?

 

Thanks,

Gabriel

 

 

  ________________________________  


This email and any attachments are confidential and access to this email or attachment by anyone other than the addressee is unauthorised. If you are not the intended recipient please notify the sender and delete the email including any attachments. You must not disclose or distribute any of the contents to any other person. Personal views or opinions are solely those of the author and not of Trafigura. Trafigura does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. By communicating with anyone at Trafigura by email, you consent to the monitoring or interception of such email by Trafigura in accordance with its internal policies. Unless otherwise stated, any pricing information given in this message is indicative only, is subject to change and does not constitute an offer to deal at any price quoted.

 
 
 
------------------------------------------------------------------------------
 
Write once. Port to many.
 
Get the SDK and tools to simplify cross-platform app development. Create 
 
new or port existing apps to sell to consumers worldwide. Explore the 
 
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
 
http://p.sf.net/sfu/intel-appdev
 
 
 
_______________________________________________
 
Hsqldb-user mailing list
 
[hidden email]
 
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
 
 

 

 
------------------------------------------------------------------------------
Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create 
new or port existing apps to sell to consumers worldwide. Explore the 
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev
 
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
 

 


------------------------------------------------------------------------------
Try before you buy = See our experts in action!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-dev2
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: java.sql.SQLSyntaxErrorException: incompatible data type in conversion with HSQLDB but not with Oracle.

Fred Toussi-2
In the latest version (2.2.8) if you use the RAW type in table creation, it is translated to VARBINARY. For example, with:
 
create table t(r raw(16))
 
The .script file will contain
 
CREATE MEMORY TABLE PUBLIC.T(R VARBINARY(16))
 
It seems the software that handles the annotation is not creating the right type. For HSQLDB it should use VARBINARY directly, not the RAW of BIGINT types.
 
Fred
 
On Mon, Feb 6, 2012, at 14:06, Gabriel Rossetti wrote:

Hi,

 

I’m still getting this, even after upgrading to 2.2.7:

 

Caused by: org.hsqldb.HsqlException: incompatible data type in conversion

 

When I try (in oracle mode) to set the value for a prepared statement:

 

string id = “D1CDB8FE163547ACB3DFB647826C5455”;

 

long  msb = new BigInteger(id.substring(0,16), 16).longValue();

long lsb = new BigInteger(id.substring(16,32), 16).longValue();

val bos: ByteArrayOutputStream = new ByteArrayOutputStream();

val dos : DataOutputStream  = new DataOutputStream(bos);

dos.writeLong(msb);

dos.writeLong(lsb);

dos.flush();

byte[] bytes = bos.toByteArray();

 

String query = "select COUNT(*) from MY_TABLE where ID = ?";

PreparedStatement stmt = conn.prepareStatement(query)

stmt.setBytes(1, bytes);

 

I checked and the type for the ID field is generated as a BIGINT and not a VARBINARY(length) as stated below. I have the mapping (annotations) setup as a UserType (that writes & reads a byte array) and I wonder if the DDL generation doesn’t understand that it’s supposed to be an oracle RAW datatype, and thus HSQLDB doesn’t generate the right type?

 

Thanks,

Gabriel

 

 

From: Fred Toussi [mailto:[hidden email]]
Sent: 21 December 2011 16:19
To: User discussions about HyperSQL Database Engine.
Subject: Re: [Hsqldb-user] java.sql.SQLSyntaxErrorException: incompatible data type in conversion with HSQLDB but not with Oracle.

 

Yes, the fix will be in the next release. We release when there are no issues with the code, rather than forcing a release date. The next release is likely to come out very soon.

 

However, building the jar from the trunk is very easy using Gradle.

 

Fred

 

On Wed, Dec 21, 2011, at 14:51, Gabriel Rossetti wrote:

(Sorry if you get this twice, I got my original msg back)

 

Oh, and forgot to ask, will this fix be out in the next version? If so do you already have a release schedule?

 

Thanks!

Gabriel

 

 

From: Gabriel Rossetti
Sent: 21 December 2011 15:44
To: User discussions about HyperSQL Database Engine.
Cc: Gabriel Rossetti
Subject: RE: [Hsqldb-user] java.sql.SQLSyntaxErrorException: incompatible data type in conversion with HSQLDB but not with Oracle.

 

Ok, thank you!

Gabriel

 

From: Fred Toussi [mailto:[hidden email]]
Sent: 21 December 2011 15:42
To: User discussions about HyperSQL Database Engine.
Subject: Re: [Hsqldb-user] java.sql.SQLSyntaxErrorException: incompatible data type in conversion with HSQLDB but not with Oracle.

 

I checked the translation of the RAW type and found an error. This has just been fixed and committed to SVN /base/trunk (the class is org.hsqldb.ParserDQL)

 

With the correction, RAW(lengh) is translated to VARBINARY(length). You can assign a byte[] to a column of this type with a JDBC PreparedStatement, or if you are using a direct SQL statement, use a binary string such as X'abcdef0123456789'.

 

If your usage is different, please report and I'll try to support it.

 

Thanks for reporting.

 

Fred

 

 

On Wed, Dec 21, 2011, at 13:38, Gabriel Rossetti wrote:

Hi all,

 

I’m writing unit tests and using HSQLDB as my test DB. I have turned on Oracle compatibility and it’s been working quite well so far. I am getting the following error :

 

java.sql.SQLSyntaxErrorException: incompatible data type in conversion

 

when setting a string to a RAW oracle type. I have also tried setting bytes, I always get the same error. I have tried setting the Oracle DB as the test DB to see if I get the same error and I don’t, so it looks like an HSQLDB problem, please correct me if I’m wrong. Does anyone have an idea of why this is happening?

 

Thanks,

Gabriel

 

 

  ________________________________  


This email and any attachments are confidential and access to this email or attachment by anyone other than the addressee is unauthorised. If you are not the intended recipient please notify the sender and delete the email including any attachments. You must not disclose or distribute any of the contents to any other person. Personal views or opinions are solely those of the author and not of Trafigura. Trafigura does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. By communicating with anyone at Trafigura by email, you consent to the monitoring or interception of such email by Trafigura in accordance with its internal policies. Unless otherwise stated, any pricing information given in this message is indicative only, is subject to change and does not constitute an offer to deal at any price quoted.

 
 
 
------------------------------------------------------------------------------
 
Write once. Port to many.
 
Get the SDK and tools to simplify cross-platform app development. Create 
 
new or port existing apps to sell to consumers worldwide. Explore the 
 
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
 
http://p.sf.net/sfu/intel-appdev
 
 
 
_______________________________________________
 
Hsqldb-user mailing list
 
[hidden email]
 
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
 
 

 

 
------------------------------------------------------------------------------
Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create 
new or port existing apps to sell to consumers worldwide. Explore the 
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev
 
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
 

 

------------------------------------------------------------------------------
Try before you buy = See our experts in action!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-dev2
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

 

------------------------------------------------------------------------------
Try before you buy = See our experts in action!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-dev2
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: java.sql.SQLSyntaxErrorException: incompatible data type in conversion with HSQLDB but not with Oracle.

Gabriel Rossetti-6

Ok, I worked around it by creating the scheme manually instead of asking for it to be generated.

 

Gabriel

 

From: Fred Toussi [mailto:[hidden email]]
Sent: 06 February 2012 18:24
To: User discussions about HyperSQL Database Engine.
Subject: Re: [Hsqldb-user] java.sql.SQLSyntaxErrorException: incompatible data type in conversion with HSQLDB but not with Oracle.

 

In the latest version (2.2.8) if you use the RAW type in table creation, it is translated to VARBINARY. For example, with:

 

create table t(r raw(16))

 

The .script file will contain

 

CREATE MEMORY TABLE PUBLIC.T(R VARBINARY(16))

 

It seems the software that handles the annotation is not creating the right type. For HSQLDB it should use VARBINARY directly, not the RAW of BIGINT types.

 

Fred

 

On Mon, Feb 6, 2012, at 14:06, Gabriel Rossetti wrote:

Hi,

 

I’m still getting this, even after upgrading to 2.2.7:

 

Caused by: org.hsqldb.HsqlException: incompatible data type in conversion

 

When I try (in oracle mode) to set the value for a prepared statement:

 

string id = “D1CDB8FE163547ACB3DFB647826C5455”;

 

long  msb = new BigInteger(id.substring(0,16), 16).longValue();

long lsb = new BigInteger(id.substring(16,32), 16).longValue();

val bos: ByteArrayOutputStream = new ByteArrayOutputStream();

val dos : DataOutputStream  = new DataOutputStream(bos);

dos.writeLong(msb);

dos.writeLong(lsb);

dos.flush();

byte[] bytes = bos.toByteArray();

 

String query = "select COUNT(*) from MY_TABLE where ID = ?";

PreparedStatement stmt = conn.prepareStatement(query)

stmt.setBytes(1, bytes);

 

I checked and the type for the ID field is generated as a BIGINT and not a VARBINARY(length) as stated below. I have the mapping (annotations) setup as a UserType (that writes & reads a byte array) and I wonder if the DDL generation doesn’t understand that it’s supposed to be an oracle RAW datatype, and thus HSQLDB doesn’t generate the right type?

 

Thanks,

Gabriel

 

 

From: Fred Toussi [mailto:[hidden email]]
Sent: 21 December 2011 16:19
To: User discussions about HyperSQL Database Engine.
Subject: Re: [Hsqldb-user] java.sql.SQLSyntaxErrorException: incompatible data type in conversion with HSQLDB but not with Oracle.

 

Yes, the fix will be in the next release. We release when there are no issues with the code, rather than forcing a release date. The next release is likely to come out very soon.

 

However, building the jar from the trunk is very easy using Gradle.

 

Fred

 

On Wed, Dec 21, 2011, at 14:51, Gabriel Rossetti wrote:

(Sorry if you get this twice, I got my original msg back)

 

Oh, and forgot to ask, will this fix be out in the next version? If so do you already have a release schedule?

 

Thanks!

Gabriel

 

 

From: Gabriel Rossetti
Sent: 21 December 2011 15:44
To: User discussions about HyperSQL Database Engine.
Cc: Gabriel Rossetti
Subject: RE: [Hsqldb-user] java.sql.SQLSyntaxErrorException: incompatible data type in conversion with HSQLDB but not with Oracle.

 

Ok, thank you!

Gabriel

 

From: Fred Toussi [mailto:[hidden email]]
Sent: 21 December 2011 15:42
To: User discussions about HyperSQL Database Engine.
Subject: Re: [Hsqldb-user] java.sql.SQLSyntaxErrorException: incompatible data type in conversion with HSQLDB but not with Oracle.

 

I checked the translation of the RAW type and found an error. This has just been fixed and committed to SVN /base/trunk (the class is org.hsqldb.ParserDQL)

 

With the correction, RAW(lengh) is translated to VARBINARY(length). You can assign a byte[] to a column of this type with a JDBC PreparedStatement, or if you are using a direct SQL statement, use a binary string such as X'abcdef0123456789'.

 

If your usage is different, please report and I'll try to support it.

 

Thanks for reporting.

 

Fred

 

 

On Wed, Dec 21, 2011, at 13:38, Gabriel Rossetti wrote:

Hi all,

 

I’m writing unit tests and using HSQLDB as my test DB. I have turned on Oracle compatibility and it’s been working quite well so far. I am getting the following error :

 

java.sql.SQLSyntaxErrorException: incompatible data type in conversion

 

when setting a string to a RAW oracle type. I have also tried setting bytes, I always get the same error. I have tried setting the Oracle DB as the test DB to see if I get the same error and I don’t, so it looks like an HSQLDB problem, please correct me if I’m wrong. Does anyone have an idea of why this is happening?

 

Thanks,

Gabriel

 

 

  ________________________________  


This email and any attachments are confidential and access to this email or attachment by anyone other than the addressee is unauthorised. If you are not the intended recipient please notify the sender and delete the email including any attachments. You must not disclose or distribute any of the contents to any other person. Personal views or opinions are solely those of the author and not of Trafigura. Trafigura does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. By communicating with anyone at Trafigura by email, you consent to the monitoring or interception of such email by Trafigura in accordance with its internal policies. Unless otherwise stated, any pricing information given in this message is indicative only, is subject to change and does not constitute an offer to deal at any price quoted.

 
 
 
 
 
 
 
------------------------------------------------------------------------------
 
 
 
Write once. Port to many.
 
 
 
Get the SDK and tools to simplify cross-platform app development. Create 
 
 
 
new or port existing apps to sell to consumers worldwide. Explore the 
 
 
 
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
 
 
 
http://p.sf.net/sfu/intel-appdev
 
 
 
 
 
 
 
_______________________________________________
 
 
 
Hsqldb-user mailing list
 
 
 
[hidden email]
 
 
 
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
 
 
 
 

 

 
 
 
------------------------------------------------------------------------------
 
Write once. Port to many.
 
Get the SDK and tools to simplify cross-platform app development. Create 
 
new or port existing apps to sell to consumers worldwide. Explore the 
 
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
 
http://p.sf.net/sfu/intel-appdev
 
 
 
_______________________________________________
 
Hsqldb-user mailing list
 
[hidden email]
 
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
 
 

 

 
------------------------------------------------------------------------------
Try before you buy = See our experts in action!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-dev2
 
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
 

 


------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user