Hi All, I am having an issue with an error when running a velocity macro in xwiki. Within the Oracle database I have a table containing a list of page names. I am trying to run a script where the system takes this list, cycles through all pages within a space and deletes any pages who's name appears on the list. Here's what I have so far... .... #set($sql = " where doc.space='AlarmIdentifiers' AND doc.name IN (SELECT data FROM lovtestdata)") #DeletePagesLocal($sql) .... #macro(DeletePagesLocal $sql) #foreach($item in $xwiki.searchDocuments($sql)) #set($itemdoc = $xwiki.getDocument($item)) $itemdoc.delete() #end #end The error I am getting is: Caused by: com.xpn.xwiki.XWikiException: Error number 3223 in 3: Exception while searching documents with SQL [select distinct doc.space, doc.name from XWikiDocument as doc where (doc.hidden <> true or doc.hidden is null) and doc.space='AlarmIdentifiers' AND doc.name IN (SELECT data FROM lovtestdata)] Wrapped Exception: lovtestdata is not mapped [select distinct doc.space, doc.name from com.xpn.xwiki.doc.XWikiDocument as doc where (doc.hidden <> true or doc.hidden is null) and doc.space='AlarmIdentifiers' AND doc.name IN (SELECT data FROM lovtestdata)] Essentially it is saying that the table is not mapped. If anyone could point me in the right direction it would be great. Many thanks Luke LEGAL NOTICE This email and any files transmitted with it are confidential, may be privileged and are intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient, please telephone or email the sender on +44 (0) 1785 827 000 and delete this message and any attachment from your system; you must not copy or disclose the contents of this message or any attachment to any other person. Any views or opinions presented are solely those of the sender and do not necessarily represent those of Capula Limited and we accept no liability for its accuracy or completeness. Copyright in this email and any attachment created by us belongs to Capula Limited. Please ensure you have adequate virus protection before you open or detach any documents from this transmission. Capula Limited does not accept any liability for any loss or damage arising in any way from the receipt or use thereof. Any e-mail reply to this address may be subject to monitoring for operational reasons or lawful business practices. Capula Limited Registered No 00953504 England Registered Office Orion House, Stone, Staffordshire, ST15 0LT. _______________________________________________ users mailing list users(a)xwiki.org http://lists.xwiki.org/mailman/listinfo/users

The problem is that you are using SQL to query against the database and Hibernate is expecting HQL. I assume you added a table called lovtestdata manually. unfortunately hibernate doesn't know that it exists and will not allow you to query against it. The easy solution is to use: {{groovy}} xc = xcontext.getContext() hib = xc.getWiki().getHibernateStore(); hib.beginTransaction(xc); try { conn = hib.getSession().openConnection();

// conn will be a http://download.oracle.com/javase/1.3/docs/api/java/sql/Connection.html // which will give you raw access to the database. // commit hib.endTransaction(xc, true); } catch (Exception e) { // or rollback hib.endTransaction(xc, false); throw e; } {{/groovy}} The "right" solution is to use XObjects and query for their properties using HQL or XWQL instead of raw database queries. Caleb On 01/06/2011 05:09 AM, Richardson Luke wrote: _______________________________________________ users mailing list users(a)xwiki.org http://lists.xwiki.org/mailman/listinfo/users