Hi Denis, Oh my. That looks tricky on the SQL Server side. Thanks for all that info. I'll try to stay clear of XML in the database, then :)
In any case, let me know if you run into any issues with the Binding implementation. Cheers, Lukas 2016-05-27 15:31 GMT+02:00 Denis Miorandi <[email protected]>: > Ok about your suggestion. If a binding is needed that could be a good > general solution. In my case, I could get an SQLXml field an get stream > from it with the right encoding... > > About SqlServer, I give you some more info from my experience... > I've observed that any xml file that has passed via a sqlserver comes in > UTF-16 Little Endian with BOM > , so that you have to take care about reading , if it's a file using > UTF-16 encoding i.e. > *FileUtils.readFileToString(new > File("src/test/resources/testUtf16.xml"),"UTF-16")* > this take care to clean string from BOM, zero bytes (due to 2 bytes > encoding), and convert right *grave and others special characters... > > If you need insteadto insert into sqlserver via mgmtstudio an UTF-8 xml > you need to remove > <?xml version="1.0" encoding="utf-8"?> > part or to set encoding="utf-16" otherwise is refused. > Also sql server doesn't manage cdata sections in right way cause it remove > cdata and convert it in an xml encoded section and is not symmetric on > reading... > All in all sql server has not a good xml implementation, but I need it... > > > > > > Il giorno venerdì 27 maggio 2016 13:38:05 UTC+2, Lukas Eder ha scritto: >> >> Hi Denis, >> >> Interesting, I didn't know that about SQL Server. Doesn't it consult the >> encoding specified in the XML's leading processing instruction? Or the byte >> order mark, if any? >> >> In any case, you're on the right track with jOOQ's Binding, that's where >> your logic will go. In particular, you'll have to implement your logic in >> all three get() methods for ResultSet, Statement, and SQLInput (although >> the latter is currently not used with SQL Server). Since all of these >> parameter objects (such as BindingGetResultSetContext) extend >> org.jooq.Scope, you can access the SQLDialect, or the SQLDialect family >> directly from it: >> >> - http://www.jooq.org/javadoc/latest/org/jooq/Scope.html#dialect-- >> - http://www.jooq.org/javadoc/latest/org/jooq/Scope.html#family-- >> >> This will allow you to implement a single data type binding for XML types >> that can work with any database. >> >> I hope this helps. If you have any further specific questions about this, >> please do not hesitate to ask, and I'll be very happy to help. >> >> Best Regards, >> Lukas >> >> 2016-05-27 12:28 GMT+02:00 Denis Miorandi <[email protected]>: >> >>> Probably is something known that SqlServer store xml type as UTF-16. >>> You can insert UTF-8 but select always extract UTF-16 xml field. >>> I've got a binding to convert query xml field into String, see below... >>> I would like this binder to generate String as *new >>> String(bytes,"UTF-16")* instead of default, I suppose default is >>> something like *new String(bytes).* >>> I want also to do this just on sqlserver but not on other databases, >>> cause this binding is in my parent pom (all db kinds). >>> >>> How can I achieve this? Is it possible manage two difference forcedType >>> choosing by database engine? Or is is possible to write a binder that define >>> different constructor of String on different db engines? Other >>> possibilities? >>> >>> >>> In my maven configuration I've got >>> >>> <customType><name>XmlStringBinding</name><type>java.lang.String</type><binding>it.clesius.jooq.bindings.XmlStringBinding</binding></customType> >>> >>> >>> <forcedType> >>> <name>XmlStringBinding</name> >>> <expression>(\w+\.)?(?!UDT_PREFIX|PROC_PREFIX)\w+(\.\w+)?</expression> >>> <types>XML</types></forcedType> >>> >>> >>> and java code for binding is: >>> >>> >>> public class XmlStringBinding implements Binding<Object, String> { >>> /** * Generated UID */ >>> private static final long serialVersionUID = 358789452467943117L; >>> >>> @Override >>> public Converter<Object, String> converter() { >>> return new Converter<Object, String>() { >>> private static final long serialVersionUID = >>> 5005180607894678030L; >>> >>> @Override >>> public String from(Object databaseObject) { >>> return null; // Not needed in the example >>> } >>> >>> @Override >>> public Object to(String userObject) { >>> return null; // Not needed in the example >>> } >>> >>> @Override >>> public Class<Object> fromType() { >>> return Object.class; >>> } >>> >>> @Override >>> public Class<String> toType() { >>> return String.class; >>> } >>> }; >>> } >>> >>> @Override >>> public void sql(BindingSQLContext<String> ctx) throws SQLException { >>> ctx.render().sql("?"); >>> } >>> >>> @Override >>> public void register(BindingRegisterContext<String> ctx) throws >>> SQLException { >>> ctx.statement().registerOutParameter(ctx.index(), Types.SQLXML); >>> } >>> >>> @Override >>> public void set(BindingSetStatementContext<String> ctx) throws >>> SQLException { >>> if (ctx.value() == null) >>> ctx.statement().setObject(ctx.index(), null); >>> else >>> ctx.statement().setString(ctx.index(), ctx.value()); >>> } >>> >>> @Override >>> public void set(BindingSetSQLOutputContext<String> ctx) throws >>> SQLException { >>> if (ctx.value() == null) >>> ctx.output().writeObject(null); >>> else >>> ctx.output().writeString(ctx.value()); >>> } >>> >>> @Override >>> public void get(BindingGetResultSetContext<String> ctx) throws >>> SQLException { >>> ctx.value(ctx.resultSet().getString(ctx.index())); >>> } >>> >>> @Override >>> public void get(BindingGetStatementContext<String> ctx) throws >>> SQLException { >>> ctx.value(ctx.statement().getString(ctx.index())); >>> } >>> >>> @Override >>> public void get(BindingGetSQLInputContext<String> ctx) throws >>> SQLException { >>> ctx.value(ctx.input().readString()); >>> }} >>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "jOOQ User Group" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to [email protected]. >>> For more options, visit https://groups.google.com/d/optout. >>> >> >> -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
