How to connect to MySQL through workbench

[Bibhu_Bikash_Na]

Hello All,

I have installed LiveCycle ES -2 through turnkey installation in a Windows -7(64 bit) system. I am trying to create a DSN in the system. But I could not get the MySQL ODBC connector. I have installed the same, still I am unable to see the option in the connector list. Only SQL server connector is present. (Which I guess by default). I am trying to connect the MySQL through workbench by picking an activity and modifying it for SQL statement. I am unable to see any DSN except few default ones. So now I am confused on the same. Can anybody help me in doing this?

Thanks,

Bibhu.

[Former Community Member]

I am assuming that you are trying to use JDBC Service in Workbench, if so then see the below:

First , you need to know which type of your web server for the workbench connection? e.g. WebSsphere, JBoss, or Web Logic.

Second, If it is IBM WebSphere then you need to create a new data source (DSN) for the MySQL thing in the WebSphere Admin Console. Hint note the new DNS that you created to use in the next step.

Finally, you can start working in Workbench to connect that DSN.

Hope it help.

HD

[Bibhu_Bikash_Na]

Hi,

Yes, you are right. I am using a JDBC service. As I have mentioned I have installed LS through TurnKey method, it automatically installed MySQL workbench. But I can not see any option for MySQL JDBC driver. So I installed that. But I don't know why while creating a DSN I am unable to see the MySQL driver option in the list. Only SQL Server option is there (I cant understand why). Now In workbench while modifying an activity to accept SQL statement. It shows error. Only two default DSNs are present. I cant understand how to extablish the connection. But while working in BAM I am able to fetch some data by select statement from few tables. That's why I guess there might be teh connection. But again I am unable to perform data manipulation statement. It says data manipulation statements can not be executed.

I have not installed Data service yet. Is it required?

Thanks,

Bibhu.

[_Nith_]

If you have installed LC though turn-key option, you should have got the

default JBoss and MySQL installed.

This will create a datasource for MySQL by default in Adobe-ds.XML and

mysql.xml configuration files.

You need not create a new DSN to connect to the default MySQL instance

installed by the turn-key installer.

In case, if you need to connect a different DB Server instance, then you

should following the DataSource creation step as mentioned in the

Installation guide.

[Bibhu_Bikash_Na]

Hi Nith,

Yes, I had MySQL, default JBOSS installed. But I have one doubt while installing MySQL by turnkey does it install MySQL JDBC connector with it? Because I did not find it. While installing JDBC connector by myself I did not get the option in the JDBC administrative tool. There is only one option available. (SQL Server- which is by default I guess??). Now the point is how to create a DSN? As my repitive installments failed. One more question also. Where do I need to create a table for my application I mean which schema? Is it in the adobe or adobe_geo or anything else. Though I have installation of LiveCycle data service still I have not installed it. How will it help me in this problem?

Thanks,

Bibhu.

[_Nith_]

Not sure if it will install the jdbc driver. You can easily make sure that

by locating the mysql-jdbc.jar file from the lib directory of jboss. If

not, you can install it manually.

Do not create any table on adobe or any other builtin schema. Create your

own database and use it.

[Bibhu_Bikash_Na]

Hi,

No, it does not install MySQL JDBC driver. I don't know why my repeated instances of installing MySQL driver is failing. I created a schemadifferently. But it's not accessing it.

Do you have any examples/resources/videos which will help me?

Thanks,

Bibhu.

[_Nith_]

Not sure what's the problem with JDBC driver installation. Any error found

in the Event Logger?

And, if you create a new schema, you should add a new Data Source to the

Adobe-ds.xml file and shoudl restart JBoss to effect the changes.

• Nith*

[Bibhu_Bikash_Na]

Hi Nith,

I have managed to create a DSN. I just opened the adobe.ds xml file and edited it. Below is my adobe.ds xml file.

=============================================================

<?xml version="1.0" encoding="UTF-8"?>

<datasources>

    <local-tx-datasource>

        <jndi-name>IDP_DS</jndi-name>

        <connection-url>jdbc:mysql://localhost:3306/adobe</connection-url>

        <driver-class>com.mysql.jdbc.Driver</driver-class>

        <!-- modified by installer to encrypt password -->

        <security-domain>EncryptDBPassword_IDP_DS</security-domain>

        <min-pool-size>1</min-pool-size>

        <max-pool-size>30</max-pool-size>

        <valid-connection-checker-class-name>com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker</valid-connection-checker-class-name>

        <exception-sorter-class-name>com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter</exception-sorter-class-name> 

        <new-connection-sql>SELECT count(*) from DUAL</new-connection-sql>

        <check-valid-connection-sql>SELECT count(*) from DUAL</check-valid-connection-sql>

        <blocking-timeout-millis>20000</blocking-timeout-millis>

        <idle-timeout-minutes>2</idle-timeout-minutes>

        <prepared-statement-cache-size>20</prepared-statement-cache-size>

        <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>

    </local-tx-datasource>

    <local-tx-datasource>

        <jndi-name>EDC_DS</jndi-name>

        <connection-url>jdbc:mysql://localhost:3306/adobe</connection-url>

        <driver-class>com.mysql.jdbc.Driver</driver-class>

        <!-- modified by installer to encrypt password -->

        <security-domain>EncryptDBPassword_EDC_DS</security-domain>

        <min-pool-size>1</min-pool-size>

        <max-pool-size>20</max-pool-size>

        <valid-connection-checker-class-name>com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker</valid-connection-checker-class-name>

        <exception-sorter-class-name>com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter</exception-sorter-class-name> 

        <new-connection-sql>SELECT count(*) from DUAL</new-connection-sql>

        <check-valid-connection-sql>SELECT count(*) from DUAL</check-valid-connection-sql>

        <blocking-timeout-millis>20000</blocking-timeout-millis>

        <idle-timeout-minutes>2</idle-timeout-minutes>

        <prepared-statement-cache-size>20</prepared-statement-cache-size>

        <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>

    </local-tx-datasource>

    <local-tx-datasource>

        <jndi-name>TestDSN</jndi-name>

        <connection-url>jdbc:mysql://192.168.8.9:3306/adobe</connection-url>

        <driver-class>com.mysql.jdbc.Driver</driver-class>

        <!-- modified by installer to encrypt password -->

        <security-domain>EncryptDBPassword_TestDSN</security-domain>

        <min-pool-size>1</min-pool-size>

        <max-pool-size>20</max-pool-size>

        <valid-connection-checker-class-name>com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker</valid-connection-checker-class-name>

        <exception-sorter-class-name>com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter</exception-sorter-class-name> 

        <new-connection-sql>SELECT count(*) from DUAL</new-connection-sql>

        <check-valid-connection-sql>SELECT count(*) from DUAL</check-valid-connection-sql>

        <blocking-timeout-millis>20000</blocking-timeout-millis>

        <idle-timeout-minutes>2</idle-timeout-minutes>

        <prepared-statement-cache-size>20</prepared-statement-cache-size>

        <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>

    </local-tx-datasource>

</datasources>

==================

TestDSN is the new DSN that I had created. It's also showing in the workbench.

Below is my mysql.xml file.

===================================================

<?xml version="1.0" encoding="UTF-8"?>

<!--  -->

<!--  Datasource config for MySQL using 3.0.9 available from:

http://www.mysql.com/downloads/api-jdbc-stable.html

-->

<datasources>

  <local-tx-datasource>

    <jndi-name>DefaultDS</jndi-name>

    <connection-url>jdbc:mysql://localhost:3306/adobe</connection-url>

    <driver-class>com.mysql.jdbc.Driver</driver-class>

    <!-- encrypted password -->

    <security-domain>EncryptDBPassword</security-domain>

    <valid-connection-checker-class-name>com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker</valid-connection-checker-class-name>

    <exception-sorter-class-name>com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter</exception-sorter-class-name> 

    <new-connection-sql>SELECT count(*) from DUAL</new-connection-sql>

    <check-valid-connection-sql>SELECT count(*) from DUAL</check-valid-connection-sql>

    <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml -->

    <metadata>

       <type-mapping>mySQL</type-mapping>

    </metadata>

  </local-tx-datasource>

</datasources>

===========================

I had tested just editing some process to execute SQL statements. It worked when I created a table inside adobe schema. But It did not work when I used another table in another schema. What's wrong here?

Thanks,

Bibhu.

[_Nith_]

I think you should refer the table with complete name as* TestDB.TestTable*

Can you try that see the response?

Nith

[Bibhu_Bikash_Na]

Hi Nith,

I am sorry. I did not get your response correctly. Where to use the complete table ref name? Is it in the DSN? or else where? I got the following error when I tested it as per your instruction in the SQL statement of the action.

Exception: No matching credentials in Subject!; - nested throwable: (org.jboss.resource.JBossResourceException: No matching credentials in Subject!)

Thanks,

Bibhu.

[Bibhu_Bikash_Na]

One morw thing I would like to ask? Do we need Xml schema or sample xml data is a must for every form? Do we need LC DataService anyway?

Thanks,

Bibhu.

[Bibhu_Bikash_Na]

Hi,

I used the adobe schema for test. Made a simple form to update the DB. It worked well while giving hardcoded values in test. But it did not work while detching data from the form.

I used JDBC service by using the following statement :

INSERT INTO adobe.feedback_form VALUES ('{$ /process_data/formData/xdp/datasets/data/dataroot/ConfirmationFormFields/Employee_Id $}','{$ /process_data/formData/xdp/datasets/data/dataroot/ConfirmationFormFields/Date $},'{$ /process_data/formData/xdp/datasets/data/dataroot/ConfirmationFormFields/Designation $}','{$ /process_data/formData/xdp/datasets/data/dataroot/ConfirmationFormFields/Employee_Name $}','{$ /process_data/formData/xdp/datasets/data/dataroot/ConfirmationFormFields/Evaluator_Name $}','{$ /process_data/formData/xdp/datasets/data/dataroot/ConfirmationFormFields/Reporting_Manager $}');

It gave the following error.

Any help on this?

Thanks,

Bibhu.