12 Replies Latest reply on Nov 22, 2015 3:49 PM by whardy7

    Can I script a table name change?

    whardy7

      I need to change a table name once it has been created. I think there are other ways around it, but I'm looking for ideas. Here's my situation...

       

      I'm querying several online book databases that each return a JSON file. A handy MonkeyBread plugin creates tables/fields from the JSON. The plugin does what it's supposed to basically. But, one of the JSONs (World Catalog system) constructs their JSON differently than all the others, which results in a table name of that book's ISBN number. The table name ends up being something like ISBN:3876098761. Each query obviously results in a different table name. Once I create the tables, I consolidate them into my own Library database, then delete the record. The next query it creates another table with a whole different name. There are several other tables that rely upon this table (it becomes the parent/child to about 12 different relationships).

       

      Any ideas? I can clarify if it doesn't make sense.

       

      Wayne

        • 1. Re: Can I script a table name change?
          coherentkris

          to answer the title question..

          You might be able to set up an odbc connection to your database and use the Execute SQL script step and the UPDATE clause to change table names.

          I have no idea if this will in fact work but it might

          • 2. Re: Can I script a table name change?
            bigtom

            I do believe there is access to table names via calculations. I have not done it though.

            • 3. Re: Can I script a table name change?
              okramis

              whardy7 schrieb:

               

              I need to change a table name once it has been created. I think there are other ways around it, but I'm looking for ideas. Here's my situation...

               

              I'm querying several online book databases that each return a JSON file. A handy MonkeyBread plugin creates tables/fields from the JSON.

               

              Why don't you modify the JSON before sending it to the MBS-function to create the table?

              I don't think, FMs SQL-standard allows for altering table names and I haven't found a script step to do so.

              • 4. Re: Can I script a table name change?
                coherentkris

                We have access to table/to/fieldnames via ExecuteSQL Function (not script step) and other Design Functions but not manipulation of them.

                • 5. Re: Can I script a table name change?
                  coherentkris

                  No script steps will do it. The ExecuteSQL function will not do it. MBS / Script Master / Base Elements plug ins wont do it.

                  • 6. Re: Can I script a table name change?
                    alecgregory

                    coherentkris wrote:

                     

                    to answer the title question..

                    You might be able to set up an odbc connection to your database and use the Execute SQL script step and the UPDATE clause to change table names.

                    I have no idea if this will in fact work but it might

                    This does work as far as I recall. A FileMaker database can add itself as a ODBC source and run the full range of DML and DDL statements that are in the FileMaker SQL reference guide using the Execute SQL script step.

                     

                    I tried this on PC with FileMaker 12. An annoyance was that the FileMaker ODBC driver needed to be installed on every client machine that was running the SQL commands, you couldn't use the driver on the server. Perhaps with Perform Script on Server this limitation is gone.

                     

                    Anyway, this is a way that you can update the schema of a deployed solution. It's limited as you can't add relationships or perhaps not even table occurrences but will be useful in some circumstances.

                    • 7. Re: Can I script a table name change?
                      okramis

                      alecgregory schrieb:

                       

                      This does work as far as I recall. A FileMaker database can add itself as a ODBC source and run the full range of DML and DDL statements that are in the FileMaker SQL reference guide using the Execute SQL script step.

                      Yes, this works - unfortunately there's no command to alter a table name as sp_rename on MSSQL or rename on MySQL.

                      You could query the scheme of the table, create a new one with the same scheme and copy the records over, drop the original

                      I tried this on PC with FileMaker 12. An annoyance was that the FileMaker ODBC driver needed to be installed on every client machine that was running the SQL commands, you couldn't use the driver on the server. Perhaps with Perform Script on Server this limitation is gone.

                       

                      Yes, now with >=13 and PSOS this works great.

                       

                      Anyway, this is a way that you can update the schema of a deployed solution. It's limited as you can't add relationships or perhaps not even table occurrences but will be useful in some circumstances.

                      as whardy7 uses the MBS-Plugin anyway, there's no need for the ODBC-way. as he can use MBS's FilemalerSQL command for this purpose.

                      • 8. Re: Can I script a table name change?
                        wimdecorte

                        whardy7 wrote:

                         

                        I need to change a table name once it has been created. I think there are other ways around it, but I'm looking for ideas. Here's my situation...

                         

                        I'm querying several online book databases that each return a JSON file. A handy MonkeyBread plugin creates tables/fields from the JSON.

                         

                        Something about this sounds wrong to me.  A table is for a new entity type (books, cars, people,...).    I'm assuming that each of those JSON files describes the same type of entity so I would not go create a new table for say every book that comes in.  Those should be records in a book table, not tables by themselves.

                         

                        Especially when you say:

                         

                        whardy7 wrote:

                        The table name ends up being something like ISBN:3876098761.

                         

                        That does not sound like it needs to be a table, but a record.  Whatever is described by that ISBN number is not a separate entity type but an entity instance.

                        • 9. Re: Can I script a table name change?
                          whardy7

                          You are correct. That is why I said I am consolidating it into my own Book table. I don't have any control over their data. But, given the structure of their JSON and the way the plugin works (which is correct), I need to be able to have a consistent table name.

                           

                          After realizing from the replies that this wouldn't work this way, I started thinking about another approach. If corrected it by actually manipulating the JSON before the plugin gets it. This way it gives me a consistent table name to work with.

                           

                          Thanks for all the feedback!

                          • 10. Re: Can I script a table name change?
                            wimdecorte

                            Not wanting to sound too harsh but I think you took a fundamental wrong turn in using the JSON and the plugin.  In no way should a simple import process from external data create its own tables.  The import should either:

                            - go straight to the target table

                            - or go to an existing scratch table if you want to post-process the data prior to pushing it to the ultimate target table.

                            • 11. Re: Can I script a table name change?
                              bigtom

                              What is the reason you cannot take the JSON directly to a field?

                              • 12. Re: Can I script a table name change?
                                whardy7

                                That's the way I wrote it originally, but Google messed up their Book API and now I have to query 4 different databases to get all the data I need. So, I'm working with at least 4 JSONs. It is a whole lot easier (at least at my skill level) to have them all in tables so I can deal with them. Working with a JSON involving 70+ fields is challenging.

                                 

                                Oh, and I forgot...I have to take different data from each JSON-turned-table because no one database has it all.