Hi Thomas, thanks for the quick answer! This clarifies things greatly.
Cheers, Bart On Sat, Jan 10, 2009 at 3:24 AM, Thomas Mueller < [email protected]> wrote: > > Hi, > > Short answer: I suggest to use the X'...' syntax instead of 0x... > > Long answer: > > The problem is that H2 interprets 0x... as a number and not as a > binary. Example: > > select 0x35 from dual; > returns one row, one column, data type INTEGER (java.lang.Integer), > value 53. MySQL returns a varchar of length 1, value '5'. > > select 0x3535353535 from test; > returns one row, one column, data type DECIMAL (java.math.BigDecimal), > value 228'525'946'165. > > See also http://www.h2database.com/html/grammar.html#hexnumber > Then the database tries to convert that value to a binary, which > doesn't work correctly > > Currently, if you want that H2 interprets it as a binary, you need to > write X'..'. Example: > > select X'3535353535' from dual; > > See also http://www.h2database.com/html/grammar.html#bytes > > MySQL interprets the 0x.. syntax as binary, H2 as a number (like in > Java). I didn't find other databases that support this syntax. > > The X'...' syntax is supported by H2, PostgreSQL, MySQL, Derby. > > Something funny happens if you run this in Apache Derby: > > drop table test; > create table test(id int); > insert into test values(1); > select 0x35 from test; > > This returns one column with the label 'X35', value 0. That means it's > parsed as: > > select 0 x35 from test; > > Regards, > Thomas > > > > On Thu, Jan 8, 2009 at 8:55 PM, Rabatjes <[email protected]> wrote: > > > > > > Hi, > > > > I'm running into the following issue: > > > > I have an insert query on a table with several blob type columns (as > > well as columns of other types). The insert query was generated by a > > MySQL dump, where the blob values are represented in the final string > > as hexidecimal values. For example, my code would be something like > > > > String query = "INSERT INTO `my table` (`field`, `field2`, field3`) > > VALUES > > ('value1', value2, > > 0x3565303263666530353561383039346435336331373734643233623266363039)" > > > > I then run this query using: > > > > Statement s = connection.createStatement(); > > s.executeUpdate(query); > > > > The insert then fails with the following error: > > > > Hexadecimal string with odd number of characters: > > > 24151365170745754550414754817295888861833632876261387367437141647564379402297 > > [90003-106] > > > > I get an identical error if I replace the hexidecimal value with a > > real value, e.g. > > > > String query = "INSERT INTO `my table` (`field`, `field2`, field3`) > > VALUES > > ('value1', value2, 'value3')" > > > > Can H2 only read in hexidecimals via s.executeUpdate? Or am I missing > > something? Currently I'm working around this issue using prepared > > statements, but I'm just wondering if there is a more straightforward > > way of solving this issue? > > > > Thanks in advance to anyone who can shed some light on this! > > > > Bart > > > > > > > > > > > > > > > -- The new is always the old. Everything that's old was once new, and everything that's new will one day be old. The oldest thing of all is the present, because there's never been anything else but the present. No one has ever lived in the past, and no one lives in the future, either. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/h2-database?hl=en -~----------~----~----~----~------~----~------~--~---
