Qdabra Database Accelerator

Re: unable to delete document(s) from DocumentType

S.Oka — Fri, 27 Nov 2009 02:45:01 GMT

Hello

I understood the design of DBXL, and I support for "Philosophical Reason". As you mentioned, we don't need the databse constraint as long as we only use InfoPath to store data in SQL.

Thank you for taking your time.

Re: unable to delete document(s) from DocumentType

Patrick Halstead — Fri, 27 Nov 2009 01:25:13 GMT

Hi Oka-san,

Looks like a contraint issue. Because of DBXL’s design of updating child tables by deleting and reinserting, primary key constraints in a child table can cause problems. There’s a philosophical reason why we do this (“loose coupling”), but easiest check is to first try to remove your constraint or your primary key from the child table (note: this is not an issue for parent tables).

If that fixes the problem, and you are only using InfoPath data to submit, you probably don’t need the database constraint. However, if you have other “tightly coupled” applications hanging off the DB that require it, there are a couple workarounds: Here is a blog post from Hilary that you may find useful:http://www.infopathdev.com/blogs/hilary/archive/2009/10/13/use-dbxl-submit-with-existing-sql-data.aspx I also recommend reading the link in Hilary’s blog to my original blog from summer of 2008 which describes the reasoning. Best regards,

Re: unable to delete document(s) from DocumentType

S.Oka — Thu, 26 Nov 2009 20:46:21 GMT

Hello

After I have deleted foreign-Primary key relations in a table mapped from InvoicForm Document Type, the above two issues are resolved.

I am wondering why this relation affects these issues, especially the issue (2) which doen't keep showing data in a repeating table in the form.

Thanks

Re: unable to delete document(s) from DocumentType

S.Oka — Thu, 26 Nov 2009 19:16:19 GMT

1) I can duplicate the issues which are no updating one document type.

I have two document type, one for "OrderRecordForm" and the other for "InvoiceForm".
The InvoiceForm gets data stored by OrderRecordForm. Once I create InvoiceForm, I can't update OrderRecordFom, though I did not get any error or any message like "this form can be opened as read-only form".
The InvoiceForm has SQL mapping to a table including "OrderRecord ID (台帳ID)" which is the same number as the file name of the document of OrderRecordForm Type. I am setting Foreign-key to the column, Primary-key is the DocId of OrderRecordForm Type.

But why this relation lock the document of OrderRecordForm?

2) Another thing what I have noticed in OrderRecordForm is that the OrderRecordForm has a table to show created invoices based on this OrderRecordForm, and the repeating table getting data and copying them from secondary data source by CopyTable command of qRules 2.0 does not keep show data in the table when I reopen the OrdeRrecordForm. When I reclick "get data" command in the form, I can get data in the table, though. Is this related to the above issue?

Thanks

Re: unable to delete document(s) from DocumentType

S.Oka — Thu, 26 Nov 2009 17:30:00 GMT

Hello

I could delete all document (document Type, OrderRecord) after I have deleted related documents (document type, InvoiceForm).

The remaing issue is if I can update information of the document (document type, OrderRecord).

I will update, thanks

Re: unable to delete document(s) from DocumentType

S.Oka — Thu, 26 Nov 2009 17:12:08 GMT

Hello

Thank you for the reply. I have copied one of error events in the below.

--------------------------------------------------

Error deleting document from DBXL (DocType = 'OrderRecordForm', DocId = 2163) encountered a critical error and needs attention:

System.Exception: The DELETE statement conflicted with the REFERENCE constraint "FK_InvoiceHeaderList_OrderHeaderList". The conflict occurred in database "JAM_Data", table "dbo.InvoiceHeaderList", column '台帳ID'.
The statement has been terminated.

Caught exception populating items from XRefItems table. Query String: DELETE FROM [OrderHeaderList] WHERE DocID IN ( @p0 )
at Qdabra.Dbxl.Domain.DocumentType.DeleteTargetDBItems(SqlConnection conn, SqlTransaction sqlTran, Int32 docID, String tableName, String lookupListXml)
at Qdabra.Dbxl.Domain.DocumentType.DeleteDocumentFromTables(Document doc)

The stack trace was: at Qdabra.Dbxl.Domain.DocumentType.DeleteTargetDBItems(SqlConnection conn, SqlTransaction sqlTran, Int32 docID, String tableName, String lookupListXml)
at Qdabra.Dbxl.Domain.DocumentType.DeleteDocumentFromTables(Document doc).

----------------------------------------------------------------

Since I have setup SharePoint Listenner, we usually delete documents from SharePoint Site, it is working for other other document type. In regard with this document type, I have redesigned the form which query/show document file name (such as 2163 in the above error message, which is refered as "台帳ID"). We create invoice form (other document type) which has dynamic query based on file name of OrderRecordForm in the above error. Then, when we go back to OrderRecord Form, we can get table of invoices based on this OrderRecord Form (I have used qRules v2.0 here) because one OrderRecord Form relates to several Invoices (Invoice Form).

When I have noticed that the OrderRecord Form is not updating information, I decided to delete all of documents (there were about 10 documents in document library in WSS). I have deleted from WSS, and I have checked DAT. And I have noticed there was one document remained. Probably, the document was what I have tested to show file name and table of invoices after I have redesigned. That is the brief story of this document type. If you need more information please let me know.

Thanks

Re: unable to delete document(s) from DocumentType

ErnestoM — Thu, 26 Nov 2009 12:03:49 GMT

Could you check the event viewer for any errors or messages related to Qdabra? Hopefully, a message in event viewer will provide us with clues and information to solve the issue.

Thank you.

Re: unable to delete document(s) from DocumentType

Patrick Halstead — Thu, 26 Nov 2009 05:19:13 GMT

Hi Oka-san,

I'd be happy to look at your problem. I'm not sure what could be causing it. Deleting documents from SharePoint requires a special Delete "listener" add-in that deletes documents from DBXL, but you are deleting directly from DAT so it should work fine. The only thing I can think of is that the Doc Migration tool locked them and the lock has not expired. Please send me e-mail and I can look at your problem tonight or tomorrow after 2pm PST.

Thanks!

unable to delete document(s) from DocumentType

S.Oka — Thu, 26 Nov 2009 02:34:26 GMT

Hello

I have a problem with deleting documents from Document Type. I get message, "documents deleted successfuly" in DAT. Also, I have also tried Migration tool, and I got no error. Also, I have tried to use DbxlDocumentService.asmx. Again I got no error message. But there are still documents in the DBXL. Before I have decided to delete all (there are several documents, though) documents, I have experienced that documents are not updated whenever I have edited them. It seemed that the documents are read-only permission. Therefore, I have decided to delete all documents and start from the beginning. But I have noticed that I can't delete documents from DBXL, it looks like they are locked. Please advise me what to do.