1 2 Previous Next 18 Replies Latest reply on Aug 3, 2016 3:48 AM by beverly

    SQL Import script issues

    JohnM_2

      I have an SQL DB with  300 Tables and I created an FM table containing those table names - I was hoping an IMPORT using the following script would cycle through each table and import it into FM. However, this only brings across tables with their field names and it also just names the TABLES:     JJSQL  to JJSQL 299

      METHOD 1 sadly does not bring the data across   whereas Method 2 brings the data as well as the Field names.

      Why is there a difference or am I doing something wrong?

      Is there a way to RENAME the tables with the same name as $tableName? either during the script or batch renaming them in the tables DATABASE dialog?

       

      After all these years it seems a real pity that Filemaker hasn't improved on the IMPORT facility.

       

      I have tried FM Migrator - but it just would not work for me in Windows nor OSX (using the ACTUAL connector). Their tech support could not help either.

       

       

      METHOD #1
      Set Error Capture
      [ On ]

      Go to Layout [ “Tables” (Tables) ] Go to Record/Request/Page

      [ First ]

      Loop

      Set Variable [ $tableName; Value:Tables::Name ]

      Import Records [ ODBC Data Source: JJSQL; Calculated SQL Text: "SELECT * FROM " & $tableName; Target: “{New table}”; Method: Add; Character Set: “Mac Roman” ]
      [ No dialog; Data contains column names ]

      Go to Layout [ “Tables” (Tables) ] Go to Record/Request/Page

      [ Next; Exit after last ]

      End Loop
      Go to Layout
      [ “Import_Tbl” () ]
      Show Custom Dialog [ Title: "Import tables"; Message: "Finished import tables."; Default Button: “OK”, Commit: “Yes” ]

       

      METHOD #2

      Import Records [ ODBC Data Source: JJSQL; SQL Text: SELECT * FROM Addresses"; Method: Add; Character Set: “Mac Roman” ]

       

      [ No dialog; Data contains column names ]

       

      Thanks,

      J

        • 1. Re: SQL Import script issues
          David Moyer

          The problem must be in the use of the dynamic table name, or that the target is a "New Table".  Those are the two differences between the two import statements.

          Place a Pause Script step after the import step (in the loop) to see what it looks like after the first table is supposed to be imported.  Did it create a new table?  What layout is it in?

          • 2. Re: SQL Import script issues
            user19752

            Table created on SQL import is named usin DSN name, as you saw. It is reasonable since the SQL can have any number of tables in FROM clause.

             

            I don't have any idea why method 1 don't import records. You got no error so $tableName should be valid without quoted...

            • 3. Re: SQL Import script issues
              Mike_Mitchell

              When you import, I've found you have to either be in the context of the target table (i.e., sitting on a layout based on it), or explicitly include it in the Import Records script step. Suggest you do this instead:

               

              1) Set up a layout for each table with a name exactly the same as the table.

               

              2) Insert a Go to Layout script step into your loop, like this:

               

                   Go to Layout [ $tableName ]

                   Import Records [ ]

               

              This will probably work better for you.

               

              Also, your script above has the Import step creating a new table. Or at least it's attempting to. Note that you said in your description that you already had tables for these imports. Do you mean to create a new table each time? If not, remember that new table creation is not supported on Go or when importing server-side. (Don't know if those apply, but something to consider.) If a table of that name already exists, then that may be the problem.

               

              As another recommendation, parameterize your SQL statement. Use:

               

                   SELECT * FROM ?

               

              and then pass the name of the table as a parameter. Not only is this more secure, it will also allow FileMaker to parse out any spaces or other unruly characters in your table names.

               

              HTH

               

              Mike

              • 4. Re: SQL Import script issues
                JohnM_2

                Thanks for the replies:

                Putting a pause did not help.

                I tried setting up layouts ahead of the IMPORT _ that did not work either.

                I don't plan on using this on GO and I am not worried about security because it really is only done once in a while.

                 

                I am curious about the parameter option on the SQL statement - .  Could you please send me an example?

                 

                 

                Secondly -: no suggestions about batch renaming the tables  from JJSQL 2....x   to the individual names from the TABLEname table?

                • 5. Re: SQL Import script issues
                  Mike_Mitchell

                  What exactly are you trying to accomplish here? Describe your desired result.

                  • 6. Re: SQL Import script issues
                    JohnM_2

                    I have an SQL DB with 300 tables  that is the backend of a CRM .  I am trying to recreate the CRM in Filemaker , import all the data from the SQL.

                     

                    I know I can individually import all the tables and I may never need all 300 tables.

                     

                    I was just hoping there would be a nice automated way to do this now.

                     

                    If everything was in Filemaker at least I would not have to keep referring to the SQL DB.

                     

                    I also tried importing all the data into ACCESS --- successful.  but again when you try to import the data from ACCESS to Filemaker the import only brings over the empty fields  - is this a bug?

                     

                    The Lopp method is the best - if only we could also batch rename the tables.

                     

                    What would be great is you could create a FILEMAKER schema with tables names and fields from a single CSV file.

                    Maybe someone has written that already out there?

                    • 7. Re: SQL Import script issues
                      user19752

                      Your SQL in method#1 has $variable in it, so the SQL is not valid if the $variable is not defined, so while you defining script it never be valid and matching import fields are not stored in script step.

                      How about

                      "SELECT * FROM " & Case ( $tableName = "" ; "Addresses" ; $tableName )

                       

                      not tested. This may fail since columns in tables are different.

                      • 8. Re: SQL Import script issues
                        JohnM_2

                        Interesting idea  USER19752  but how will that work for the 300 tables I need

                        • 9. Re: SQL Import script issues
                          David Moyer

                          Hi,

                          Sorry - I wasn't very clear.  The Pause step can be used as a debugging tool.  When the script paused the first time, you should have seen your first complete import table imported.  If not, you can at least see what layout you landed in.  Cancel and debug, rinse, repeat.

                          • 10. Re: SQL Import script issues
                            user19752

                            Sorry my last comment was for "why method#1 didn't import record data". Created table name thing is not resolved with it, so not help your main issue.

                             

                            If you use Windows, DSN can be editted through registry.

                            • 11. Re: SQL Import script issues
                              David Moyer

                              This might be worth looking at:

                              I don't know what version it starts with (I have 14), but, if you haven't already looked into it, there's ESS (external sql sources).

                              This would be useful if you could take an Oompa-Loompa off of the chocolate-bar line for a day to do the clicking for you select multiple tables at once in the following steps (maybe you can) ...

                              Once your DSN is created, go to the relationships graph and click + to add an occurrence of one (hopefully more) of your 300 tables via the ODBC/DSN connection.

                              FM is supposed to build a "shadow table" with a "shadow schema" and populate the table with data.  I haven't actually tried this myself.

                              Interestingly, the shadow schema inherits much of the field validation rules from the SQL source.

                              Also, note that you can corrupt the original sql data by manipulating it in the shadow table, I think.  (So work with a copy.)

                               

                              Fellow contributors - is this correct?

                              • 12. Re: SQL Import script issues
                                user19752

                                Re: If you use Windows, DSN can be editted through registry.

                                 

                                Sorry this is not helpful for your issue...

                                I used this for one DSN accessing to many files of FM database, changing filename in DSN.

                                Changed DSN name is not usable, since DSN name in script step can't be changed...

                                • 13. Re: SQL Import script issues
                                  JohnM_2

                                  I guess in the end I will look for a developer to help me write this in Java or C.  The fact that there are apps like FM Migrator suggest that it can't be done easily from within FM.

                                  • 14. Re: SQL Import script issues
                                    Mike_Mitchell

                                    Yes, but it really doesn't help him much. He'd have to add the 300 shadow tables individually. He's trying too avoid doing the job one at a time.

                                    1 2 Previous Next