12 Replies Latest reply on Dec 21, 2011 12:06 AM by Bibhu Bikash Nayak

    How to connect to MySQL through workbench

    Bibhu Bikash Nayak Level 4

      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.

        • 1. Re: How to connect to MySQL through workbench
          Han Dao Level 1

          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

          • 2. Re: How to connect to MySQL through workbench
            Bibhu Bikash Nayak Level 4

            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.

            • 3. Re: How to connect to MySQL through workbench
              $Nith$ Level 4

              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.

              • 4. Re: How to connect to MySQL through workbench
                Bibhu Bikash Nayak Level 4

                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.

                • 5. Re: How to connect to MySQL through workbench
                  $Nith$ Level 4

                  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.

                  • 6. Re: How to connect to MySQL through workbench
                    Bibhu Bikash Nayak Level 4

                    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.

                    • 7. Re: How to connect to MySQL through workbench
                      $Nith$ Level 4

                      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*

                       

                      • 8. Re: How to connect to MySQL through workbench
                        Bibhu Bikash Nayak Level 4

                        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.MysqlValidConnectio nChecker</valid-connection-checker-class-name>

                                <exception-sorter-class-name>com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorte r</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.MysqlValidConnectio nChecker</valid-connection-checker-class-name>

                                <exception-sorter-class-name>com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorte r</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.MysqlValidConnectio nChecker</valid-connection-checker-class-name>

                                <exception-sorter-class-name>com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorte r</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.

                         

                        DSN.JPG

                         

                        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.MysqlValidConnectio nChecker</valid-connection-checker-class-name>

                            <exception-sorter-class-name>com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorte r</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.

                        • 9. Re: How to connect to MySQL through workbench
                          $Nith$ Level 4

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

                           

                          Can you try that see the response?

                           

                          Nith

                          • 10. Re: How to connect to MySQL through workbench
                            Bibhu Bikash Nayak Level 4

                            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.

                            • 11. Re: How to connect to MySQL through workbench
                              Bibhu Bikash Nayak Level 4

                              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.

                              • 12. Re: How to connect to MySQL through workbench
                                Bibhu Bikash Nayak Level 4

                                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_Manage r $}');

                                 

                                It gave the following error.

                                 

                                Db.JPG

                                Any help on this?

                                 

                                Thanks,

                                Bibhu.