Change SQL Query dynamically - InfoPath Dev
in

InfoPath Dev

Use our Google Custom Search for best site search results.

Change SQL Query dynamically

Last post 11-11-2010 12:54 AM by joanfuture. 6 replies.
Page 1 of 1 (7 items)
Sort Posts: Previous Next
  • 10-15-2010 04:22 AM

    Change SQL Query dynamically

    As you know, when I create a SQL Query Data Connection in InfoPath, I can put ONE fixed Query, that I can sort and filter and so on. At runtime I usually need to query information for lets say a certain product. The data connection returns ALL entries that I defined in the query before and then I can put rules to work to really get the information I want.  But I only need the information for one tiny little product :-). Now, is there a way to modify the query with qrules dynamically, so I can increase performance (we are not talking about seconds, but minutes and maybe hours, when you are dealing with large tables).

    I did this with code once, but since I know qrules, I don't want to do this anymore. Did I mention yet, that i love qrules :-)

    thx
    Michael

  • 10-15-2010 05:12 AM In reply to

    Re: Change SQL Query dynamically

    Hello Michael,

     We at Qdabra do not advocate using SQL data connections from InfoPath because it creates a tight coupling between your form and your database.  And modifying data connections' SQL to incorporate form values carries with it all the dangers and unlpleasantness of escaping values and protecting againt SQL injection. The alternative we developed is the QueryDB web service, which allows you to perform dynamic database queries with filtering and sorting using only rules.

    Here is information on the DBXL Data Query package:

    http://www.qdabra.com/en/products/WSSP.aspx

    And here is a how-to video where you can see how the QueryDB web service can be used to dynamically query your data:

    http://www.screencast.com/users/qdabra/folders/Default/media/ae6728ea-3273-4202-bc9b-173ff701d8a9

    Jimmy Rishe / Software Developer / Microsoft MVP
    Qdabra Software
  • 10-15-2010 11:41 AM In reply to

    Re: Change SQL Query dynamically

    Thank you for your quick reply. I have studied a lot of documents regarding your DBXL products. I get somehow the ideas and I think I begin to like it, however could you clarify the following questions for me, please?

    1. Do I need need DBXL Data Query or is it part of the DBXL Services, which I might want as well, but which I don't yet realize?
    2. Where do I want to install all this stuff? On the SQL Server? on the Moss Server? or the AD-Server or a new Server?
    3. What do you define as a DB Connection (Standard version may have 5 of them)? Can I upgrade to Enterprise Version, once I have the need for it?
    4. It says here: http://www.qdabra.com/en/products/DBXL_compare.aspx
      that, the Integrated DBXL Services can have 1 xsn-Template. Do I just misunderstand something or can I just use ONE form template to write into a SQL-Table? I have a lot more XSNs that I might want to use this DBXL in. Can you please verify or correct me?
    5. Is the Data Query fairly new? or have you changed its name? I somehow couldn't find an installation manual, I always end up finding the "bigger" DBXL Manual
    6. Can I write to a table of my choice or do I still need to do this with InfoPath Database Template?

    thx

  • 10-15-2010 08:32 PM In reply to

    Re: Change SQL Query dynamically

    Hello again,

    1. All of the features of DBXL Data Query are included in the main DBXL package.  The minimum DBXL Data Query license allows querying data from up to 5 databases, while the main DBXL package will allow you to query from 1 database or more, depending on the license.
    2. For customers that have SharePoint, it is typical to install DBXL on the SharePoint (MOSS) server, as part of the SharePoint site.
    3. A DB connection is the ablilty to query data from a single database in SQL Server. Five connections means you can query data from up to five different databases, which may or may not be on different SQL Server instances.  There is no limit on the number of tables, amount of data, or number of different InfoPath templates involved in the data access.  Upgrading a license is a simple process and you should certainly be able to upgrade after the fact, but Ted Fitch from our sales department can give you a more specific answer about that than I can.
    4. The minimum DBXL license (Beginner) does only allow storing XMLs and writing data to SQL from one XSN.  Larger licenses allow a larger number of XSNs, as that page you referenced shows.  Incidentally, you can write data from a single XSN to any number of tables and we have customers with database mappings involving hundreds of tables.
    5. DBXL Data Query was previously named WSSP, and you can find documentation for WSSP 2.3 here: http://www.qdabra.com/en/products/docs/WSSP23.aspx
    6. Writing SQL data from your InfoPath forms using DBXL requires DBXL Integrated or Standalone.  DBXL Data Query does not provide this feature.

    Thank you for your interest in DBXL!

    Jimmy Rishe / Software Developer / Microsoft MVP
    Qdabra Software
  • 10-19-2010 07:04 AM In reply to

    Re: Change SQL Query dynamically

    Thank you, Jimmy,

    I have downloaded the trial version and will try to have it installed by the end of the month by our partner as it is somewhat more complex to just "inject qrules" into my form. However, I am quite sure I want this Data Query quite badly and my compliments to you guys that you came with this solution. I think, I just have to go through this once and then have this Meta-WebService in place, instead of coding n WebServices for each of my projects.
    The Partner asks me, wether one SHOULD install this software on the SharePoint Server or rather on an independent Windows Server. They would like to know how sensitve SharePoint (and Qdabra) react, when we install SharePoint-Updates and/or Qdabra Updates. So lets say, we are cost independant, would I want to install it on a separate Server? Will it still run as defined?

    Thanks Michael

  • 10-19-2010 05:31 PM In reply to

    Re: Change SQL Query dynamically

    Hi Michael,

    Thank you for being a qRules customer and for your positive comments! :)

    There are essentially four deployment scenarios for DBXL:

    1. Integrated with SharePoint. DBXL Web Service is closely integrated with SharePoint. Forms submit to the DBXL Web service which ("gatekeeper") which maps to SharePoint and your SQL database. This is great for conditional mapping, item-level permissioning, and performance (submit time faster to DBXL than to ShP) but can require extra config for forms that use a lot of SharePoint workflow. Note: this was the only scenario supported through DBXL v2.3, so the majority of our customers have integrated.
      Two main sub scenarios (note SQL DB can be on same server or separate in same domain):
      • SharePoint server: requires the least amount of configuration, but requires installing on your SharePoint box, wihch you may not have access to. For load balanced environments, install on each WFE.
      • Domain Web server: requires some config to allow DBXL to talk to SharePoint and vice versa.
    2. Side-by-side, separate from SharePoint. Forms get submitted to both SharePoint and DBXL Web service. XML Template Parts for browser and filler forms to easily update form to submit to DBXL. New feature in DBXL v2.4 meant for customers who want to reduce config cost further.
      • SharePoint server: no SharePoint config required but you have to be able to install on your SharePoint box, etc.
      • Domain Web server: requires config if you are using IPFS (browser forms). Otherwise, just requires UDCX files for your data connection (sample comes with package).
    3. Reporting Dashboard, separate from SharePoint. No change to forms and no change to SharePoint. The dashboard pulls forms from SharePoint and maps asynchronously. Great for reporting where you don't need *immediate* updates. Document types (form templates) can be configured to synch on the hour or more frequently.
      • Domain Web server: config tool specifies SharePoint form library and frequency of synch. Synch runs under default account. If that account doesn't have access to the ShP library, you can post-config task with special credentials.
      • Standalone desktop: obviously requires that you are connected. Uses your credentials to access libraries.
    4. Standalone Dashboard, combines with Side-by-side DBXL instance to allow synching and editing forms on a desktop/laptop that is often offline. SharePoint Workspaces don't synch form libraries but DBXL's standalone dashboard does. Provides form filtering and advanced synch options with conflict detection. Obviously, need to be online to synch. Both of the following are required:
      • Server: you need to have an Integrated or Side-by-side instance of DBXL to support editing.
      • Desktop/laptop: install dashboard on your laptop or desktop.

    The trial download of DBXL available from our Qdabra dot com web site supports all of these scenarios. We are working on detailed documentation for the above scenarios. Thanks for your patience.

    Patrick Halstead
    Project Manager at Qdabra
  • 11-11-2010 12:54 AM In reply to

    Re: Change SQL Query dynamically

    Thank you Patrick for the detailed information.

    We are now upgrading to SharePoint 2010, so it makes no sense to inall in MOSS2007, but I will keep this product in mind to maybe install next year. I solved my immediate query and performance issues by code, which works pretty fine.

    best regards
    Michael

Page 1 of 1 (7 items)
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.