Urgent! Setting very long extendedData with Oracle 10

Hello all! I contacted Opentext support and they suggested I post here:We have had problems inserting long extendedData in nodes. We have come to the conclusion that the size limit is 65k caracters (65534). We have located the problem in all DAPI functions that are somehow related to extendedData.The following error comes up when calling DAPI.UpdateNode() for example:----> Error stack----> 1) E675086366:The requested BindN buffer is too large.:----> 2) E675086344:Error executing an Sql statement.:We do not get this error using MS SQL Server and we believe it can either be a bug in Livelink or related to some configuration in our database.Has anyone encountered this or have any suggestions? We are a bit desperate!We are using the UTF8 version of Livelink 9.5 for Windows and Solaris (both have the same problem) with Oracle 10.1.0.2.0.p.s. My personal opinion is that Opentext have a bug in their interface with Oracle. The datatype used to store extendedData is a LONG, which has 2GB capacity in Oracle. Using JDBC, we have no problems setting the same field.Also, the same error comes up when using CAPI.EXEC(), which I think clearly shows that the Livelink interface to Oracle needs to be fixed!EXAMPLE CODE:The UpdateNode will not work when using an Oracle database:----------String cnctName = $Kernel.SystemPreferences.GetPrefGeneral( 'DftConnection' )Assoc prgAssoc = $LLIApi.PrgSession.CreateNewNamed( cnctName, { "Admin", undefined } )Dynamic prgCtx = prgAssoc.pSessionDAPINode myNode = DAPI.GetNodeById(prgCtx.fdSession.fSession, 20106)Assoc extData = Assoc.CreateAssoc()Dynamic veryLong = ""Integer ifor i=1 to 345 veryLong = veryLong +"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"endextData.text = veryLong[1:65519]echo(Length(str.string(extData)))myNode.pExtendedData = extDataAssoc errList = prgCtx.fDbConnect.CheckError(DAPI.UpdateNode(myNode))

Find more posts tagged with

Comments

Hugh_Ferguson

This is very much like a problem I'm having, the difference is that I'm inserting into a LONG in an external table using CAPI.ExecN() or updating with CAPI.Exec(). Anecdotally, and don't take this as the gospel until someone from development tells me otherwise, I have found that I can insert up to 50K of data this way. That doesn't solve your problem, but at least it gives you an idea of what you can do.I have a client that needs a way of getting up to 90K into a long field. When I have a solution, I'll post it here.Regards-Hugh Ferguson, DeveloperOpen Text Global Services

ATHANASSIOS_FAMELIARIS

(First of all, thank you for your reply!)I have come to the conclusion that this is a bug in Livelink's driver for Oracle. This is not a problem on Oracle's side, as we have managed to insert very large amounts of data on LONG fields using java.The upper limit is actually 64k, and it's consistent. This is not a random number. Actually, I think it is the limit plSQL imposes on the data inserted into a LONG field. Now, this is a guestimate, but I think Opentext are using plSQL to implement the interface to Oracle, which, of course, is a VERY BAD APPROACH. This, along with the fact that LONG should not be used anymore, since Oracle has CLOB now, demonstrates bad development practices on opentext's side and exposes them to their clients!The biggest issue here is that the official support line cannot give us a solution to the problem. They have actually referred us to this site, which is ridiculous!Mr. Ferguson, since you are an Opentext employee, can you PLEASE forward this to anyone responsible? Isn't this a major issue for your firm? Don't you have a big client base that uses Oracle databases?I thank you in advance.

PVA_Livelink_Developer_(pva_pva_-_(deleted))

Message from Joe Lesh via eLinkIt's too bad that Open Text has to refer you here for developmentsupport. For what it's worth, I'll try to give you my perspective onthis issue (I used to work as a programmer there).I'm pretty sure that Open Text isn't using plSQL as its database driver.AFAIK, they are using Oracle-provided database driver libraries andthose are likely causing the limit. The quote below is from JDBCdocumentation and mentions that the OCI driver has a limit in 9.2 (ofOracle) of 64k. I'd take that as evidence that Oracle is deliberatelymaking libraries that limit LONGs to 32k or 64k to improve performanceand move people to using CLOBs. You should be able to see in the connectlogs where this error is originating from.As for changing the ExtData field from LONG to CLOB, I think I'd bepretty circumspect about modifying that datatype if I was Open Text. Canyou imagine what that database upgrade would be like for a customer withmillions of rows in DTREE? If it took a long time, many customers wouldcomplain. Also, customers who have existing code that relies on thatcolumn being of the LONG type would be unhappy. Much better is to getnew development projects to create their own schema that doesn't usethose columns.In addition, developers have been shying away from using the ExtDatafield. Standard practice is to create a table for your own schema. Thisreally opens things up for searching and reporting and gives you controlover your own schema going forward.Joe Leshjlesh@pvaglobal.comhttp://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.htm---------------------------------------------------What is the longest value I can bind?Method Column Type Maximum lengthsetBytes LONG 4k bytessetBytes LONG RAW 2G bytessetString LONG 32k chars (SetBigStringTryClob="false")64k chars (SetBigStringTryClob="true")setString CLOB 2G charsIn 9.2, setString() on a LONG can insert up to 64k characters with theOCI driver, and 4k characters with the Thin driver. In 10.1.0 we changedthe limit for both drivers to 32k characters. We understand thatreducing the limit for OCI from 64k to 32k may be a problem to somecustomers. However, considering the substantial performance improvementthat this change made possible, and that Oracle is strongly recommendingour customers to migrate from LONG to CLOB, we decided that thearchitectural change is necessary.We recommend customers who need setString() to work over 32k charactersto migrate from LONG to CLOB.--------------------------------------------------------Original Message-----From: eLink Discussion: Development Discussion[mailto:development@elinkkc.opentext.com] Sent: Tuesday, December 13, 2005 10:51 AMTo: eLink RecipientSubject: This is a very serious issue! (please read)This is a very serious issue! (please read)Posted by FAMELIARIS, ATHANASSIOS on 12/13/2005 10:50 AM(First of all, thank you for your reply!)I have come to the conclusion that this is a bug in Livelink's driverfor Oracle. This is not a problem on Oracle's side, as we have managedto insert very large amounts of data on LONG fields using java.The upper limit is actually 64k, and it's consistent. This is not arandom number. Actually, I think it is the limit plSQL imposes on thedata inserted into a LONG field. Now, this is a guestimate, but I thinkOpentext are using plSQL to implement the interface to Oracle, which, ofcourse, is a VERY BAD APPROACH. This, along with the fact that LONG should not be used anymore, sinceOracle has CLOB now, demonstrates bad development practices onopentext's side and exposes them to their clients!The biggest issue here is that the official support line cannot give usa solution to the problem. They have actually referred us to this site,which is ridiculous!Mr. Ferguson, since you are an Opentext employee, can you PLEASE forwardthis to anyone responsible? Isn't this a major issue for your firm?Don't you have a big client base that uses Oracle databases?I thank you in advance.[To reply to this thread, use your normal E-mail reply function.]============================================================Topic: Urgent! Setting very long extendedData with Oracle 10https://knowledge.opentext.com/knowledge/livelink.exe/open/4343609Discussion: Development Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe/open/786303Livelink Server:https://knowledge.opentext.com/knowledge/livelink.exeTo Unsubscribe from this Discussion, send an e-mail tounsubscribe.development@elinkkc.opentext.com.

ATHANASSIOS_FAMELIARIS

First of all, I really appreciate your response. But could you please post your thoughts on the following as well? Thank you!1) The JDBC setString has a limit ot 64K, but one can always use setASCIIStream, which we have used to store 1MB of data on a LONG field. This is certainly NOT an issue wiht Oracle's driver!2) Oracle certenly discourages the use of LONG. They have provided the CLOB since verison 9. Now, I realize that replacing LONG with CLOB is not an easy thing to do. I agree it should be avoided with old installations. But there should be an option for new customers to use a database with CLOB. CLOB has been arround for 4 years now!(Currently, Livelink imposes the same limitation for CLOB, by the way!)3) Even if one avoids using the extendedData field and constructs new tables, one can never get round the problem that large ammounts of data cannot be stored in a single field! Where do we put a lage text? (again, CLOB has the same limit when using CAPI)4) Using extendedData is allot simpler than constructing tables and Opentext actually recomends its use instead of extra tables in the Builder tutorial!5) There are core livelink objects that use the extendedData allready, like news items. This limitation means that the news text must be less than 64k characters, which is very restrictive!6)Why isn't anyone from Opentext replying here?Yiannos

PVA_Livelink_Developer_(pva_pva_-_(deleted))

Message from Joe Lesh via eLinkOne option for storing very long text is to store the information as aversion on your node. Look at how categories work with attributes for anexample.The AddressBook module guide may have parts of it that are not perfect.I can tell you that I wouldn't use ExtendedData anymore for the reasonsI mentioned before. Anyhow, the AddressBook is more like a 'Hello world'program that teaches you how to compile and make things display. Itdefinitely is no substitute for training through the Open Text Developerclasses or through an advisor relationship like the kind that my company(PVA) provides. As K. Nair said in some recent post, to be a goodLivelink developer you have got to look to modules recently written forgood examples of how to do things.Have you considered using JavaModules? The documentation and dev guidesfor that are really good and it seems like you might be more comfortableusing Java ...Joe Leshjlesh@pvaglobal.com-----Original Message-----From: eLink Discussion: Development Discussion[mailto:development@elinkkc.opentext.com] Sent: Wednesday, December 14, 2005 5:41 AMTo: eLink RecipientSubject: It is impossible not to use the extendedData field!It is impossible not to use the extendedData field!Posted by FAMELIARIS, ATHANASSIOS on 12/14/2005 05:38 AMFirst of all, I really appreciate your response. But could you pleasepost your thoughts on the following as well? Thank you!1) The JDBC setString has a limit ot 64K, but one can always usesetASCIIStream, which we have used to store 1MB of data on a LONG field.This is certainly NOT an issue wiht Oracle's driver!2) Oracle certenly discourages the use of LONG. They have provided theCLOB since verison 9. Now, I realize that replacing LONG with CLOB isnot an easy thing to do. I agree it should be avoided with oldinstallations. But there should be an option for new customers to use adatabase with CLOB. CLOB has been arround for 4 years now!(Currently, Livelink imposes the same limitation for CLOB, by the way!)3) Even if one avoids using the extendedData field and constructs newtables, one can never get round the problem that large ammounts of datacannot be stored in a single field! Where do we put a lage text? (again, CLOB has the same limit when using CAPI)4) Using extendedData is allot simpler than constructing tables andOpentext actually recomends its use instead of extra tables in theBuilder tutorial!5) There are core livelink objects that use the extendedData allready,like news items. This limitation means that the news text must be lessthan 64k characters, which is very restrictive!6)Why isn't anyone from Opentext replying here?Yiannos[To reply to this thread, use your normal E-mail reply function.]============================================================Topic: Urgent! Setting very long extendedData with Oracle 10https://knowledge.opentext.com/knowledge/livelink.exe/open/4343609Discussion: Development Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe/open/786303Livelink Server:https://knowledge.opentext.com/knowledge/livelink.exeTo Unsubscribe from this Discussion, send an e-mail tounsubscribe.development@elinkkc.opentext.com.

ATHANASSIOS_FAMELIARIS

Thanks again for the reply.A question about Javamodules:Have yo had any experience with the latest version? Because the earlier one I tried was pretty much in alpha stage! It was impossible to complete the tutorial actually, because the not even examples would work! Documentation was also inexistant. Has this changed in 9.5.1?About Advisor relationship:My company is actually similar to yours from what I understand. We are the sole representators of Opentext in Greece.We sell their product and make any customisations required. Believe me we have made many customizations. Lukily, many of our clients run Livelink on windows with MS-SQL so we have had no DB problems. But now we are required to use Solaris and Oracle.Question about Advisor relationship:We have been doing this for 2 years now and Opentext has provided us with the worst kind of support. How would you describe your experience?About Opentext:By Builder tutorial I meant the text that Opentext gave me when I did their training program in London. "Livelink Builder Fundamentals". I'm telling you, Opentext does NOT recoment changing the schema (at least my instructor didn't). They are right in my opinion, as for simple storage extendedData is allot simpler to use that a custom table. Custom tables would only give you more detailed reporting IMO.Also, table columns are fixed, whereas extData is flexible to take any kind of data this allows for extensive customization of nodes.Finaly storing text as a version (like category versions) is a solution I suppose, but this means that it is stored as binary data, not in a table field. This doesn't give me much flexibility.Keep in mind that individual attributes have the same problems when it comes to using LONG. The difference here is that opentext ( realizing their problem) uses more than one table row to stote the category, hence avoiding the problem. This is no real solution of course, as any reporting is impossible really.And let's not forget Opentext's node! News items store their text in extendedData, which means that these nodes have problems too.Yiannos

Janusz_Frydecki

I know this is an old thread but I'll post here anyway in hope someone could reply:How can we use CLOBs using CAPI? Last time I tried, I got an unsupported data type error and OT confirmed me that they don't support CLOB.