Using DBXL with Existing Databases - Patrick Halstead
in

InfoPath Dev

Patrick Halstead

Using DBXL with Existing Databases

What DBXL does well

DBXL helps you quickly move the XML data in your InfoPath forms to a SQL database. It also enables web-based solutions and greater fault-tolerance. More detail:

1. Quickly move your XML file data into a SQL database to enable:

a. richer reporting – extend SharePoint views with Excel and SQL Reporting

b. better performance – pull and push data faster with SQL (bypasses ShP)

c. fine-grained permissions – control read and write at document level based on assignment

2. Enable Web-based forms – a secondary benefit of DBXL is to act as a “proxy” layer between the InfoPath client and your SQL server enabling internet-based forms:

a. No VPN? No problem! DBXL is your proxy. Users submitting forms across the Internet may not be VPN’ed into the corporate domain. In that case, DBXL acts as an authentication proxy.

b. Browser database hops a problem? DBXL is your bridge. Browser-based forms cannot directly submit to a database on another server. DBXL enables this architecture by providing an authentication proxy between SharePoint and SQL.

c. SQL security holes? DBXL is your gatekeeper.
Inside the corporate domain, InfoPath forms submitting directly to SQL will either need Windows Authentication (which opens the SQL db up to anyone) or SQL Authentication, which requires storing connection string info in the InfoPath XSN. Both are security risks. DBXL provides a solution here by providing a proxy layer in between your InfoPath form and SQL.

3. Build robust, fault-tolerant forms – DBXL is a middle-tier web service providing a layer between your InfoPath form and your SQL database.

a. Abstraction layer provides a buffer allowing changes to take place in the form or in the database without impacting the form submission process.

b. Mapping between the XML and the SQL data is done in the Web service which stores the XML and logs mapping errors.

c. Admin can check mapping log after hours to correct issues and re-map XML data to update data in reporting.

d. This fault tolerant approach is a major cost-saving advantage of DBXL. If your form is down, you lose money. DBXL increases your form’s uptime.

What DBXL does not do well

DBXL was not designed to enable InfoPath forms to work with existing SQL databases. Why is that? There are several reasons why existing SQL databases are not fully supported:

1. To protect data integrity DBXL needs to control data submitted to the database. If someone opens a form from the database, DBXL locks the form to prevent overwrites. However, the lock is stored in DBXL not in the database. While forms are being edited, if a separate process changes the data in the database, that data will be overwritten when the form is submitted. Vice versa, if the separate process updates the data, the XML form will not be updated. These two scenarios lead to data integrity problems. DBXL cannot be used with databases that are also being written by separate processes or applications. Of course, if the separate process or application uses DBXL web methods, there is no issue.

2. SQL data in previous database cannot be updated. SQL data not in XML format – to preserve data type fidelity, in addition to mapping data from your XML form into your database, DBXL also stores all form data as XML in the database. This XML is the primary store and that’s good for your InfoPath forms because it means you won’t have data type mismatch problems. However, for pre-existing databases the data exists only in SQL, not in XML. DBXL does not support converting existing SQL data into XML. You can pull SQL data into your InfoPath form (using DBXL’s QueryDB web method) and submit as a new XML form, but you can’t update existing data in a SQL database because DBXL does not have correspondence information – it doesn’t know which rows to update if there is no pre-existing XML data. 

3. Support for constraints in the existing database – existing SQL databases have foreign and primary key constraints that DBXL may not support. For example, DBXL does not support preserving primary key ids for child and grandchildren tables. For new databases, where all data is submitted from your InfoPath form, this is not an issue, but for existing databases that require these constraints, it is the main issue preventing adoption of DBXL.

If you have an existing database and you would like to create an InfoPath form for it, your best option is to use the InfoPath’s built-in support for databases. If you need Web-based forms, you will have to create a Web service. You can still use DBXL for this scenario, but you will have to create separate tables that DBXL writes to, or make changes to your existing tables and prevent other processes from accessing the existing database.

Why DBXL doesn’t do UPDATE – Philosophical Reasons

The simple answer is that supporting SQL UPDATE is not an important scenario. The complete answer is slightly more complex:

  • DBXL is for office workers who love InfoPath not IT departments. Because DBXL doesn’t require programming it empowers office workers to streamline their business data processes without having to go through their IT department. That saves time and money. Supporting existing databases just isn’t the primary goal since existing databases are most often the domain of the IT department. DBXL lets office workers use a database without having to do complex database programming.
  • DBXL enables robust form solutions. You can change your form or your database and the InfoPath form’s submit process continues to work. This is a major cost savings for you since it ensures greater uptime (see last bullet above under the DBXL benefits section). To support SQL UPDATE would require keeping track of mapping information in the InfoPath form’s XML but since this mapping could change between submits it would create complex update scenarios. This isn’t an issue with simple Delete-then-Reshred because we don’t have to figure out what to update. We just delete everything and resubmit.
Published Jul 09 2008, 04:14 PM by ErnestoM
Filed under:

Comments

 

Patrick Halstead said:

Update: Hilary Stoupa wrote a blog with examples of how to use DBXL on existing SQL databases: www.infopathdev.com/.../use-dbxl-submit-with-existing-sql-data.aspx.

While we update top-level tables in place, we choose to do cascading deletes for child and grandchild (etc.) tables because an update would require a tight coupling and the whole point of the DBXL Web Service is to provide a loose coupling. Since InfoPath does the data type and structure validation for you, there’s no need to tightly couple at the submit interface anymore so long as you have InfoPath. Loose coupling enables greater up-time because the mapping can fail but since DBXL always stores the XML, the admin can go in after the fact and re-shred the data. Loose coupling also enables data source changes and many-to-one mapping where separate XMLs are mapped to the same table set (a boon for versioning). We always want the submit to succeed. Reads can sometimes fail, which is ok, but if submits always succeed then it provides a more robust and scalable system for business process automation.

April 28, 2010 2:57 PM
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.