Using the SQL QPAC

[Former Community Member]

I am trying to use the SQL QPAC to insert some records into MS SQL at the end of a workflow process.



I've installed MS JDBC Driver for SQL 2000.

I tried to configure the Jindi Datasource several times but it did not work for me.

Any help on how to configure that?

[HowardTreisman]

Hi Anas



This is described in a fair amount of detail in the SDK in "standard_based_qpacs.pdf", in "Deploying the SQL QPAC / Creating a JNDI datasource".



You also need to place the SQL Server jar files in: C:\Adobe\LiveCycle\jboss\server\all\lib

and restart JBoss.



Alternately, you can try our SQLPlus QPAC at: http://www.avoka.com/avoka/qpac_library.shtml

This allows you to do some testing of your connection parameters and SQL statement within the QPAC's dialog.



Howard

[Former Community Member]

Hi Howard,



Thanks for the reply.

Where can I find this SDK?



Regards,

Anas

[HowardTreisman]

HI Anas



Have a look here: http://blogs.adobe.com/mikepotter/2005/12/new_version_of.html



However, the link seems to be incorrect. I think the correct link is:

https://partners.adobe.com/developer/en/livecycle/workflow/LiveCycleWorkflow702_SDK.zip



Regards,

Howard

www.avoka.com

[Former Community Member]

Here's a more general link that doesn't depend on the file name: http://partners.adobe.com/public/developer/livecycle/workflow/sdk/index.html



However you need to be a member of the Enterprise Developer Program to download it. You can get a free basic membership if you've purchased Workflow in order to download the SDK.



However, if you already have the SQL qpac, then you already have the SDK.



Chris

Adobe Enterprise Developer Support

[Former Community Member]

Thanks all,<br /><br />I am trying to use the SQL QPAC to insert records in an MS SQL DB.<br /><br />I've configured the jndi source name of the QPAC to be IDP2_DS .<br /><br />This is my DS configuration file:<br /> <br /><?xml version="1.0" encoding="UTF-8"?><br /><br /><datasources><br /><br /> <local-tx-datasource><br /><br /> <jndi-name>IDP2_DS</jndi-name><br /><br /> <connection-url>jdbc:microsoft:sqlserver://workflow-srv:1433</connection-url><br /><br /> <driver-class>com.microsoft.jdbc.sqlserver.SQLServerDriver</driver-class><br /><br /> <user-name>sa</user-name><br /><br /> <password>123456</password><br /><br /> <min-pool-size>1</min-pool-size><br /><br /> <max-pool-size>100</max-pool-size><br /><br /> <blocking-timeout-millis>20000</blocking-timeout-millis><br /><br /> <idle-timeout-minutes>10</idle-timeout-minutes><br /><br /> <prepared-statement-cache-size>50</prepared-statement-cache-size><br /><br /> <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation><br /><br /> </local-tx-datasource><br /><br /></datasources><br /><br />And, This is the error log for the stalled workitem:<br /><br /> <br /><br />javax.naming.NameNotFoundException: IDP2_DS not bound<br /><br /> at org.jnp.server.NamingServer.getBinding(NamingServer.java:495)<br /><br /> at org.jnp.server.NamingServer.getBinding(NamingServer.java:503)<br /><br /> at org.jnp.server.NamingServer.getObject(NamingServer.java:509)<br /><br /> at org.jnp.server.NamingServer.lookup(NamingServer.java:282)<br /><br /> at org.jnp.interfaces.NamingContext.lookup(NamingContext.java:530)<br /><br /> at org.jnp.interfaces.NamingContext.lookup(NamingContext.java:509)<br /><br /> at javax.naming.InitialContext.lookup(InitialContext.java:347)<br /><br /> at com.adobe.workflow.qpac.jdbc.JDBCService.execute(JDBCService.java:85)<br /><br /> at com.adobe.workflow.engine.PEUtil.executeAction(PEUtil.java:184)<br /><br /> at com.adobe.workflow.engine.ProcessEngineBMTBean.continueBranchAtAction(ProcessEngineBMTBean.java:2320)<br /><br /> at com.adobe.workflow.engine.ProcessEngineBMTBean.asyncInvokeProcessCommand(ProcessEngineBMTBean.java:512)<br /><br /> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)<br /><br /> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)<br /><br /> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)<br /><br /> at java.lang.reflect.Method.invoke(Method.java:324)<br /><br /> at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:683)<br /><br /> at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:185)<br /><br /> at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:84)<br /><br /> at org.jboss.ejb.plugins.AbstractTxInterceptorBMT.invokeNext(AbstractTxInterceptorBMT.java:144)<br /><br /> at org.jboss.ejb.plugins.TxInterceptorBMT.invoke(TxInterceptorBMT.java:62)<br /><br /> at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterceptor.java:72)<br /><br /> at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:120)<br /><br /> at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:191)<br /><br /> at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:122)<br /><br /> at org.jboss.ejb.StatelessSessionContainer.internalInvoke(StatelessSessionContainer.java:331)<br /><br /> at org.jboss.ejb.Container.invoke(Container.java:723)<br /><br /> at org.jboss.ejb.plugins.local.BaseLocalProxyFactory.invoke(BaseLocalProxyFactory.java:359)<br /><br /> at org.jboss.ejb.plugins.local.StatelessSessionProxy.invoke(StatelessSessionProxy.java:83)<br /><br /> at $Proxy303.asyncInvokeProcessCommand(Unknown Source)<br /><br /> at com.adobe.workflow.engine.ProcessCommandControllerBean.onMessage(ProcessCommandControllerBean.java:127)<br /><br /> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)<br /><br /> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)<br /><br /> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)<br /><br /> at java.lang.reflect.Method.invoke(Method.java:324)<br /><br /> at org.jboss.ejb.MessageDrivenContainer$ContainerInterceptor.invoke(MessageDrivenContainer.java:458)<br /><br /> at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:185)<br /><br /> at org.jboss.ejb.plugins.MessageDrivenInstanceInterceptor.invoke(MessageDrivenInstanceInterceptor.java:62)<br /><br /> at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:84)<br /><br /> at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:315)<br /><br /> at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:148)<br /><br /> at org.jboss.ejb.plugins.RunAsSecurityInterceptor.invoke(RunAsSecurityInterceptor.java:90)<br /><br /> at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:191)<br /><br /> at org.jboss.ejb.MessageDrivenContainer.internalInvoke(MessageDrivenContainer.java:372)<br /><br /> at org.jboss.ejb.Container.invoke(Container.java:723)<br /><br /> at org.jboss.ejb.plugins.jms.JMSContainerInvoker.invoke(JMSContainerInvoker.java:914)<br /><br /> at org.jboss.ejb.plugins.jms.JMSContainerInvoker$MessageListenerImpl.onMessage(JMSContainerInvoker.java:1208)<br /><br /> at org.jboss.jms.asf.StdServerSession.onMessage(StdServerSession.java:276)<br /><br /> at org.jboss.mq.SpyMessageConsumer.sessionConsumerProcessMessage(SpyMessageConsumer.java:871)<br /><br /> at org.jboss.mq.SpyMessageConsumer.addMessage(SpyMessageConsumer.java:159)<br /><br /> at org.jboss.mq.SpySession.run(SpySession.java:347)<br /><br /> at org.jboss.jms.asf.StdServerSession.run0(StdServerSession.java:200)<br /><br /> at org.jboss.jms.asf.StdServerSession.run(StdServerSession.java:180)<br /><br /> at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:748)<br /><br /> at java.lang.Thread.run(Thread.java:534)<br /><br /> <br /><br />Any Help?<br /><br />Also, what are the limitations of the SQL transactions that can be done through the SQL QPAC that comes in the LC WF SDK?

[Former Community Member]

anas... Coincidentally, I just spent the last few hours getting this to work via trail and error. Here's some things to make sure you do....<br /><br />1. Download and install Microsoft's SQL Server 2k JDBC driver from <a target="_blank" href="http://www.microsoft.com/downloads/details.aspx?FamilyId=07287B11-0502-461A-B138-2AA54BFDC03A&displaylang=en">here</a>.<br /><br />2. Once installed, copy the ms*.jar files from the lib folder to the JBoss's server/all/lib folder.<br /><br />3. As you mentioned above, setup <i>databasename</i>-ds.xml in JBoss's server/all/deploy folder. Here's an example....<br /><pre><br /><?xml version="1.0" encoding="UTF-8"?><br /><datasources><br /> <local-tx-datasource><br /> <jndi-name>MY_DS</jndi-name><br /> <connection-url>jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=dbname</connection-url><br /> <driver-class>com.microsoft.jdbc.sqlserver.SQLServerDriver</driver-class><br /> <user-name>myusername</user-name><br /> <password>mypassword</password><br /> </local-tx-datasource><br /></datasources><br /></pre><br /><br />4. For your QPAC's deployment settings, enter <b><pre>java:MY_DS</pre></b> as the JNDI datasource. Make sure you save the component. You may want to re-save any deployed workflows that use it as well (dunno if this is necessary or not).

[HowardTreisman]

Hi



You're missing the name of the database on the end of the connection url. This should cause an error when trying to establish the datasource, which you should be able to see in the JBoss log file.



Our SQLPlus QPAC does allow you to verify things like your connection strings by connecting interactively to the database. You can also view the tables/columns, preview your results, use prepared statements, handle multiple rows, etc.

http://www.avoka.com/avoka/qpac_library.shtml



Howard

[Former Community Member]

If the problem is because the named DS is not found, check your server.log file (or possibly boot.log) and search for the name of your datasource. I found that my jndi-name "MySqlDS" was bound to "java:/MySqlDS". Once I entered that full descripter into the deployment settings of the standard SQL QPAC, it worked fine.

[Former Community Member]

Hi,<br /><br />I have similar problem with Avoka's SQL+Import QPAC. I can't make it work but standard SQL QPAC is working correctly. Here is my up-ds.xml:<br /><br /><?xml version="1.0" encoding="UTF-8"?><br /><datasources><br /><local-tx-datasource><br /><jndi-name>UP</jndi-name><br /><connection-url>jdbc:mysql://10.0.0.10:3306/up</connection-url><br /><driver-class>com.mysql.jdbc.Driver</driver-class><br /><user-name>up</user-name><br /><password>password</password><br /><min-pool-size>1</min-pool-size><br /><max-pool-size>100</max-pool-size><br /><blocking-timeout-millis>20000</blocking-timeout-millis><br /><idle-timeout-minutes>10</idle-timeout-minutes><br /><prepared-statement-cache-size>50</prepared-statement-cache-size><br /><transaction-isolation>TRANSACTION_READ_COMMITTED<br /></transaction-isolation><br /></local-tx-datasource><br /></datasources><br /><br />In standard SQL QPAC deployment settings jndi-datasource is set to: java:/UP , but when I use these setting in Avoka's SQL+Import and press Connect button I get error "Exception occured: javax.naming.NameNotFoundException: UP not bound".<br />Also java:UP and UP setting are not working (of course connection type is JNDI).<br /><br />Maybe someone could help ?<br /><br />Thanks in advance ;)<br /><br />£ukasz

[HowardTreisman]

Hi £ukasz

You can't connect to a database from a client application like Workflow Designer using the JNDI approach. You have to be running on the server. The connect button should actually be disabled when you select JNDI - I've logged this as a bug on your behalf.



However, the QPAC should run correctly when you execute it on the server side as part of a workflow.



You can use the connect button when you're using the Direct method. You will need to add the MySQL driver to the Designer's classpath in order to do that (instructions are in the SQLPlus QPAC documentation set). Once you've connected, you will be able to use other features on the QPAC dialog, such as the table/column tree, and the ability to test your sql, populate result-set metadata, etc. The direct connect method will also work when you run the workflow on the server, but:

- it's slower

- cannot participate in transactional branches.



We often use the direct connect method when we are prototyping the workflow, and then switch to JNDI in production.



I hope this is helpful.



Regards,

Howard Treisman

http://www.avoka.com

[Former Community Member]

Hi Howard,



thank You VERY much, i just downloaded documentation for SQL+Import QPAC and it's all working just like You wrote. By the way, it's great documentation, everything's explained.



Thank You once again,



regards,

£ukasz