SQL Database Integration

Re: Increase Timeout length when creating a SQL data connection

CCBSMF — Tue, 25 Oct 2016 00:41:02 GMT

Roger that. So far, so good. No issues to report and the new timeout feature is a blessing. Thank you for allowing us to beta for you.

Re: Increase Timeout length when creating a SQL data connection

Hilary Stoupa — Mon, 24 Oct 2016 23:35:36 GMT

Just updating to add that you have this for beta testing now, and this will be included in qRules 8.1

Re: Increase Timeout length when creating a SQL data connection

CCBSMF — Fri, 07 Oct 2016 17:58:21 GMT

That would be perfect! is there anyway that(timeout parameter) can be added to the beta version now and then I would gladly test for you. My only concern would be that I have the ability to roll back to a previous version or have you on hand(email phone call or otherwise) incase we run into some problems. This is currently in our clients production environment and the form cant be down for long.

Re: Increase Timeout length when creating a SQL data connection

Hilary Stoupa — Fri, 07 Oct 2016 17:41:49 GMT

Well, on review of the user guide & the code, it appears I may have been lying to you. :)

So, now you get to help me decide the best approach for this. We are trying to get qRules 8.1 out the door (email me via the forum if you want to beta test a copy for me), and this would be a great time to add this.

The simplest approach would just be to add a timeout parameter to the ModifySqlQuery command, then you can just add /timeout=120 or whatever to your existing command. I should add that to ExecuteSqlQuery too.

Re: Increase Timeout length when creating a SQL data connection

CCBSMF — Fri, 07 Oct 2016 17:21:53 GMT

Hey Hillary, remember when you said the "timeout can be adjusted via qrules"? How do I got about doing so along with my ModifySQL command?

Re: Increase Timeout length when creating a SQL data connection

Hilary Stoupa — Thu, 01 May 2014 18:12:49 GMT

Excellent! glad it is all sorted. :)

Re: Increase Timeout length when creating a SQL data connection

CCBSFranco — Thu, 01 May 2014 17:59:51 GMT

Well I could kiss you right now. Thanks so much Hilary. I think I can take it from here.

Re: Increase Timeout length when creating a SQL data connection

CCBSFranco — Thu, 01 May 2014 17:44:30 GMT

It does, Thank you. I will give this a try and see what I can do.

Thanks again.

Re: Increase Timeout length when creating a SQL data connection

Hilary Stoupa — Thu, 01 May 2014 17:41:15 GMT

I am suggesting that when you create the data connection in your InfoPath form template, you include the Where clause by clicking the Edit SQL button in the data connection dialog that generally shows the SQL data structure tree. Set the data connection to not run on load.

Then save your form as source files (from the Publish backstage).

Then open manifest.xsf in a text editor and look for your "Where" clause - remove that from the SQL in manifest.xsf. Save your changes. Right click manifest.xsf and select Design. This opens the form in design mode. Use File > Save As to pack your form back up as an XSN.

Now, you should be able to use the qRules ModifySQL command to add a new, dynamic Where clause to your query as needed.

Does that make sense?

To directly answer your question about the timeout - I do not know of a way to adjust the time out for a data connection while walking through the data connection wizard. Once the connection has been created, the timeout can be adjusted via qRules or code.

Re: Increase Timeout length when creating a SQL data connection

CCBSFranco — Thu, 01 May 2014 17:37:11 GMT

Let me see if I understand correctly. Keep my WHERE clause and make the Data connection. Then save the form and use it a xml source file for a data connection in a new form? When you say remove it from the manifest do you mean removing the WHERE clause from the xml source file?

Re: Increase Timeout length when creating a SQL data connection

Hilary Stoupa — Thu, 01 May 2014 17:27:04 GMT

By "IP" I meant InfoPath - the button itself shows the SQL InfoPath is using. What I'm trying to suggest is a way to get the form to let you set up the data connection - my understanding of your description was that the form is timing out when you try to create the data connection.

But it sounds like you are already directly modifying the SQL when you create the query to include your "With" statement.

So - why not just add the where clause when you create the connection, then save the form as source files, and remove the where clause from the manifest (which is where your original SQL command will be found) so you can use ModifySQL from qRules? A little work-around-y, but it might do the trick...

Re: Increase Timeout length when creating a SQL data connection

CCBSFranco — Thu, 01 May 2014 16:54:16 GMT

Still couldn't find a "Show IP" option and the TOP(x) isn't working either. I'm using a WITH statement to create multiple "temp tables" and with a WHERE clause it runs incredibly fast. Without it, it's querying the entire db. I just need to get the connection made then the ModifySql command will keep my performance great and smooth.

Re: Increase Timeout length when creating a SQL data connection

CCBSFranco — Thu, 01 May 2014 16:45:30 GMT

I don't recall the show ip option. I will recreate the connection and look for it closer. About the "TOP(x)" I can attempt it, but I don't believe it will do me any favors as the data sets can vary greatly and since I am not filtering with my where til in the form it's still going to cycle through a project list. Which in this case really is the problem causing the timeout. I will attempt both and let you know how it goes.

Thanks for the quick response.

Re: Increase Timeout length when creating a SQL data connection

Hilary Stoupa — Thu, 01 May 2014 16:33:17 GMT

When you initially create the data connection to SQL there should be a button in one of the dialogs that you can click to show the actual SQL IP is using. I'm wondering about adding a "top x" to the query - do you have an idea of the max number of rows your ModifySQLQuery command might return? You could probably limit the results enough to allow SQL to create the connection.

Re: Increase Timeout length when creating a SQL data connection

CCBSFranco — Thu, 01 May 2014 16:10:45 GMT

This is in IP 2010. sorry meant to put that in there. SQL 2008 r2 is the DB im pulling from.