12 Replies Latest reply on May 25, 2016 8:01 AM by kamal1234

    DB2 Query Problem

    kamal1234

      Dear Community members,

       

      I am using ExecuteSQL to execute following query. It has all the correct syntax of normal insert query why still it not converting my french char to DB2 EBCDIC string. I am connecting to DB2 using obdc.

       

      "INSERT INTO CPD_Documents (DocID, ISBN_ISSNNumber, OrderNo, PubOptionNo, PubStatusID, ProductNumber, Title,  Subtitle, PublisherID, BindingID, TitleAuthor,IndexTitle,NumberOfSupplements ) VALUES " &

      "('"&Products::zkDocID&"'," &

      "'"&Products::ISBN_ISSNNumber&"'," &

      "'"&Products::OrderNo&"'," &

      "'"&Products::PubOptionNo&"'," &

      "'"&Products::PubStatusID&"'," &

      "'"&Products::ProductNumber&"'," &

      "EBCDIC_STR('"&Products::Title&"')," &

      "'"&Products::SubTitle&"'," &

      "'"&Products::PublisherID&"'," &

      "'"&Products::zkBindingID&"'," &

      "EBCDIC_STR('"&Products::TitleAuthor&"')," &

      "EBCDIC_STR('"&Products::IndexTitle&"')," &

      "'"&Products::NumberOfSupplements&"')"

       

       

      creates

       

      INSERT INTO CPD_Documents (DocID, ISBN_ISSNNumber, OrderNo, PubOptionNo, PubStatusID, ProductNumber, Title,  Subtitle, PublisherID, BindingID, TitleAuthor,IndexTitle,NumberOfSupplements ) VALUES ('26','0706-5388','A02200','2200','1','A02200-76',EBCDIC_STR('Tim's Practice Cases, 7th Series/Recueil de droit judiciaire de Tim, 7e série'),'','84','2',EBCDIC_STR('Tim's Practice Cases, 4th Series Recueil de droit judiciaire de Tim's, 4e serie'),EBCDIC_STR('Tim's Practice Cases, 7th Series/Recueil de droit judiciaire de Tims, 7e série'),'0')

       

      Please can someone tell me what wrong I am doing here. Can we execute server side functions from ExecuteSQL statement.

      Thank you

      Kamal

        • 1. Re: DB2 Query Problem
          LSNOVER

          To be clear, are you using the Execute SQL Script step,or the ExecuteSQL FUNCTION?

           

          The Execute SQL function can not do INSERTs, only SELECT statements.

           

          If you are using the ExecuteSQL Script Step, in theory you should be able to execute a stored procedure.  I'm not familiar with DB2, but in Oracle, I have to frame the call to a stored procedure with and "Begin" and "End" statements.

           

          Typically you can not execute direct data manipulation commands through this interface, but DB2 may have different capabilities than I'm used to.

          • 2. Re: DB2 Query Problem
            beverly

            Clarity:

            1. ExecuteSQL() - the function - is for SELECT only and places the results into a text string for a field or variable.

            2. Execute SQL - the script step is for INSERT, UPDATE & DELETE with SQL sources. This is not the same as ESS.

            3. What you have appears correct, but numbers are not single quoted.

             

            Questions:

            1. Do you have the permission to INSERT (some dbs only allow SELECT)?

            2. Are you trying to insert into any auto-enter (unique primary serial) columns?

            3. Do you have any dependancies that might be prevent the INSERT?

            4. What ODBC Driver are you using? Can you get a connection ok? Do you have any ODBC logs?

            5. Did you try Get ( LastODBCError )? - now called Get(LastExternalErrorDetail) in FM 15 (https://www.filemaker.com/help/15/fmp/en/#page/FMP_Help%2Fget-lastexternalerrordetail.html)

            6. Are you using the correct data types?

             

            These may help:

             

            beverly

            • 3. Re: DB2 Query Problem
              user19752

              Your values contain apostrophe so you need escape it with doubling.

              ... & Substitute ( yourFieldHavingApostrophe ; "'" ; "''" ) & ...

               

              Doubling it is standard SQL way but I'm not sure it works on DB2.

              • 4. Re: DB2 Query Problem
                kamal1234

                Thank you very much for looking into my problem.

                 

                I am using ExecuteSQL statement.  I am successfully executing insert and update commands on DB2 database.

                 

                My problem is my data has lot of french text. It throws an error on insert or update as soon as string as french accented char. I am using EBCDIC_STR function but it looks like it is not working.

                 

                Thank you,

                Kamal.

                • 5. Re: DB2 Query Problem
                  kamal1234

                  Thank you very much for your prompt reply.

                  The quotes are working fine as I can insert and update data if it has not french accented char.

                   

                  Thank you,

                  Kamal.

                  • 6. Re: DB2 Query Problem
                    kamal1234

                    Hello Beverly,

                    Once again thank you for looking into my problem.

                     

                    3. What you have appears correct, but numbers are not single quoted.

                        Even though the some of the DB2 fields are Decimal it works with quotes around no problem as my insert and update are successful.   

                     

                    Questions:

                    1. Do you have the permission to INSERT (some dbs only allow SELECT)?

                    - Yes

                    2. Are you trying to insert into any auto-enter (unique primary serial) columns?

                    - No

                    3. Do you have any dependencies that might be prevent the INSERT?

                    - No

                    4. What ODBC Driver are you using? Can you get a connection ok? Do you have any ODBC logs?

                    - I dont know exactly what ODBC driver it is. I have downloaded it from filemaker website.

                     

                    5. Did you try Get ( LastODBCError )? - now called Get(LastExternalErrorDetail) in FM 15

                     

                    - I have not tried this function. I will try it now.

                     

                    6. Are you using the correct data types?

                    - yes - Except for french accented char set.

                     

                    Thank you,

                    Kamal.

                    • 7. Re: DB2 Query Problem
                      beverly

                      You are using Execute SQL (note the space) script step to write an INSERT, UPDATE or DELETE sql statement. You are not using ExecuteSQL() - the function - which can only SELECT (within a table occurrence in FileMaker). I'm just trying to let others not be confused with these two "similarly named" functionality. They are not the same.

                       

                      beverly

                      • 8. Re: DB2 Query Problem
                        kamal1234

                        Hello Beverly,

                        Yes, you are correct. I am using the Execute SQL (With a space between Execute and SQL).

                        Thank you,

                        Kamal.

                        • 9. Re: DB2 Query Problem
                          monkeybreadsoftware

                          If you connect to DB2 via MBS Plugin and the SQL functions, we use the native DB2 driver (or ODBC).

                          Our plugin talks unicode to DB2 server, so you should not see any issued with text encodings.

                           

                          But this is an alternative way and not using ESS.

                          • 10. Re: DB2 Query Problem
                            beverly

                            4. What ODBC Driver are you using? Can you get a connection ok? Do you have any ODBC logs?

                             

                            - I dont know exactly what ODBC driver it is. I have downloaded it from filemaker website.

                             

                            The ODBC driver works with the ODBC source (in this case the DB2 database). It is often available when you install your database or it is downloadable from the provider. It is installed on the machine that will be calling the database. This is a great source of confusion when sharing with ODBC (any application!)

                             

                            • IF you share FileMaker (Server) with another ODBC application, the driver is downloadable from FileMaker, but installed on the machine where the application resides (often two different machines).

                            • IF you are reading/writing DB2 the driver is supplied by them but must use a special connector for ESS (only). If you are using the Execute SQL script step you are not using the ESS. However the driver is added to the machine that will be calling DB2 (FileMaker Pro or FileMaker Server).

                            • You cannot download an ODBC driver from the filemaker website to communicate with DB2, unless you have:

                                  

                            • shared FileMaker for ODBC

                                  

                            • installed the FM driver on the DB2 machine

                                  

                            • written SQL calls on the DB2 machine to communicate with FMServer.

                             

                            Can you post a screen shot of your ODBC set up for DB2?

                            beverly

                            • 11. Re: DB2 Query Problem
                              beverly

                              CS, is this 'driver' a part of the plugin and no additional setup needed? Does it work x-plat (FMPro Win & Mac, and FMServer)?

                               

                              Yes, this is NOT ESS, but would be similar to using ExecuteSQL and Import script steps. There would be no live connect to the DB2 database.

                              beverly

                              • 12. Re: DB2 Query Problem
                                kamal1234

                                Hello Christian,

                                 

                                Thank you for pointing me towards plugin. I am going to test it today.

                                 

                                Thank you,

                                Kamal.