JohnM_2

SQL Import script issues

Discussion created by JohnM_2 on Jul 30, 2016
Latest reply on Aug 3, 2016 by beverly

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

Outcomes