Jump to content

[ADO]How to use Oracle's XMLTYPE / CLOB with VB6 ?


tostinni

Recommended Posts

Hi everybody,I recently discover the XMLTYPE Datatype in Oracle and found it very handy to manage XML document I have to deal with.So I built my little app to analyze and store them, but I found a problem dealing with big documents.As you may know, Oracle have a big drawback when inserting string larger than 4000 characters. To overcome this limitation, we need to use bind variables.So I looked for exemples over the web and couldn't find anything which will satisfy me.Regarding DataTypes available in ADO (MDAC 2.8), I found nothing approching Oracle's Clob nor XMLTYPE. In W3Schools ADO tutorial, it says that adLongVarChar seems to fit Clob.After many tests, I wasn't able to bind a string to insert it in an XMLTYPE column, nor in a CLOB :)It seems that MDAC/ADO doesn't support such DataTypes which is a very annoying problem.So I ended doing the following

SQL = "INSERT INTO long_tab (my_long) values (  ?  )"cmd.CommandText = SQLcmd.Parameters.Append cmd.CreateParameter("XML", adLongVarChar, adParamInput, 20000, xmlResponse)cmd.ExecuteSQL = "INSERT INTO clob_tab (my_clob) select to_lob(my_long) from long_tab  "cmd.CommandText = SQLcmd.ExecuteSQL = "INSERT INTO XML_TAB (my_XML)  select xmltype(my_clob) from clob_tab "cmd.CommandText = SQLcmd.Execute

Each X_TAB is a table with one column of X DataType (LONG_TAB -> LONG column :) )So what's done here, is that first I insert a binded variable into a Long column, which works fine using "adLongVarChar" from ADO, and then I made an insert converting this LONG into a CLOB and end by converting this CLOB into an XMLTYPE column.It seems that doing this in one query doesn't work.

INSERT INTO xml_tab (my_xml) SELECT XMLTYPE(TO_LOB(?)) FROM DUAL

Note: Oracle can only use TO_LOB function in the SELECT part of an INSERT...Well, maybe someone had struggle with this...PS: I'm already looking for another solution using OO4O.PPS: I successfuly made this with VB.NET, but I need to work with VB6 :)

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...