FMPA 11 import error with ODBC table.
I have an existing FMPA db with 295 fields. On a monthly basis, I export two subsets as files in .fp7 format. I then import into two different ODBC sandboxes on my company Oracle db. This process has worked for over a year.
This week, the Oracle architect had to duplicate and rename one of my tables (generic_facility) using the field names and character types from specific_facility. The new Oracle table has select, update, insert, and delete privileges. However, when I import my subset file into generic_table.fp7 (an ODBC mirror table of the Oracle source), I get 6 records added, 6 records skipped due to errors, 6 fields skipped due to errors. None show up.
If I import into the old copy prior to the rename, 6 records are added with 5 fields skipped.
What could be wrong with the import? Is there a tool in FMPA to debug, or is my import error obvious?
This week, the Oracle architect had to duplicate and rename one of my tables (generic_facility) using the field names and character types from specific_facility. The new Oracle table has select, update, insert, and delete privileges. However, when I import my subset file into generic_table.fp7 (an ODBC mirror table of the Oracle source), I get 6 records added, 6 records skipped due to errors, 6 fields skipped due to errors. None show up.
If I import into the old copy prior to the rename, 6 records are added with 5 fields skipped.
What could be wrong with the import? Is there a tool in FMPA to debug, or is my import error obvious?
ASKER
The existing situation is this. Oracle has a sandbox feature that allows a user to create an Oracle SQL table. Filemaker allows me to create a file with an ODBC source and sync the ODBC source with the fp7 file (much like a linked table in MS Access).
I have existing FM files that I currently populate in Oracle by uploading data into them in fp7 format. For example, in Existing.fp7, I export some fields from Master.fp7 (huge) to Intermediate.fp7 and upload Intermediate.fp7 to Existing.fp7
I have done it with csv format before, but fp7 does not require me to reformat and verify data fields.
The problem I now face is that I cannot upload the same Intermediate.fp7 into DuplicateOfExisting.fp7. DuplicateOfExisting.fp7 is FM but it represents the Oracle SQL db that I have added as an ODBC source table in FM.
Does that help or complicate?
I have existing FM files that I currently populate in Oracle by uploading data into them in fp7 format. For example, in Existing.fp7, I export some fields from Master.fp7 (huge) to Intermediate.fp7 and upload Intermediate.fp7 to Existing.fp7
I have done it with csv format before, but fp7 does not require me to reformat and verify data fields.
The problem I now face is that I cannot upload the same Intermediate.fp7 into DuplicateOfExisting.fp7. DuplicateOfExisting.fp7 is FM but it represents the Oracle SQL db that I have added as an ODBC source table in FM.
Does that help or complicate?
<squeeky stretching sounds as brain attempts to wrap around what you are describing...>
So, if I understand correctly, you have:
Is that correct? (I'm trying to state the problem as clearly as possible because that generally makes finding a solution much easier.)
To return to my previous note, I still think you should be able to add Master.fp7 as an external FileMaker Data Source to Existing.fp7. Then, go to a layout based the table you want in Master.fp7, do a Find to locate the found set that you have been exporting as Intermediate.fp7, open a new window to a layout based on the ODBC table and perform and import from Master to Existing. It should import just the found set (Intermediate), although sometimes importing a Found set can be complicated by how many windows are open and to what tables. Make sure if you try this to import the Master table from it's reference in Existing, not from the actual Master file.
An even simpler way to do this would be to simply add your ODBC file reference to Master and do the above two window import all within one file.
So, if I understand correctly, you have:
a FileMaker file, Master.fp7 with local data stored in the file.
another FileMaker file, Existing.fp7 which has no local data or tables but is simply a front end to the Oracle db, which is connected to it as an External ODBC data source.
You are wanting but unable to import a subset of Master.fp7 into the Oracle table that is attached to Existing.fp7, something that has worked in the past but is not longer working with a newly created Oracle table.
Is that correct? (I'm trying to state the problem as clearly as possible because that generally makes finding a solution much easier.)
To return to my previous note, I still think you should be able to add Master.fp7 as an external FileMaker Data Source to Existing.fp7. Then, go to a layout based the table you want in Master.fp7, do a Find to locate the found set that you have been exporting as Intermediate.fp7, open a new window to a layout based on the ODBC table and perform and import from Master to Existing. It should import just the found set (Intermediate), although sometimes importing a Found set can be complicated by how many windows are open and to what tables. Make sure if you try this to import the Master table from it's reference in Existing, not from the actual Master file.
An even simpler way to do this would be to simply add your ODBC file reference to Master and do the above two window import all within one file.
ASKER
Correct you are on your bullet point summary. If you don't mind the extra work, I will have to ask more questions about the new process you describe. It addresses the symptom without diagnosing the problem, but I can flex if a new process is better in the long run.
Do I enable ODBC sharing or Filemaker Network sharing to add Master.fp7 to Existing.fp7?
Do I add Master.fp7 to Existing.fp7 in the Relationship field of Manage Database?
What baby steps get the Found Records in Master.fp7 into Existing.fp7? (This is the step where I usually export as FMPro then import into Existing.fp7.)
What other steps are missing?
I am perhaps unduly cautious at this point since loss of data integrity via duplication or orphans is much more likely than with my subset export.
Thanks.
Do I enable ODBC sharing or Filemaker Network sharing to add Master.fp7 to Existing.fp7?
Do I add Master.fp7 to Existing.fp7 in the Relationship field of Manage Database?
What baby steps get the Found Records in Master.fp7 into Existing.fp7? (This is the step where I usually export as FMPro then import into Existing.fp7.)
What other steps are missing?
I am perhaps unduly cautious at this point since loss of data integrity via duplication or orphans is much more likely than with my subset export.
Thanks.
Ok, to go back to your original issue for a moment, have you checked the import.log to see what issues are listed? Can you paste the contents of the import.log file here?
I'm assuming your import consists of just the six records you mentioned. The import.log file may give a clue as to what is going on. However, one other possibility is that your Oracle shadow table in FM it not synced with the Oracle db. If changes are made to the Oracle file - adding/editing/deleting fields - and you don't click the "sync" button in the Table tab of Manage database, the shadow table will not be in sync with what's actually in Oracle. Note that "sync" means to synchronize the structures, not the data.
I'm assuming your import consists of just the six records you mentioned. The import.log file may give a clue as to what is going on. However, one other possibility is that your Oracle shadow table in FM it not synced with the Oracle db. If changes are made to the Oracle file - adding/editing/deleting fields - and you don't click the "sync" button in the Table tab of Manage database, the shadow table will not be in sync with what's actually in Oracle. Note that "sync" means to synchronize the structures, not the data.
ASKER
How do I find/access/generate a current import log? i see several import.log files on my system but none with current error data and the most recent being 10/28/2011?
I used ManageDatabase...Sync to get the fields into FM from the Oracle table in the first place. I know from past mistakes that data doesn't sync:) Each time I have added/deleted an Oracle field, I have synced and verified the new number of fields which match. When I begin the import, I use matching names and see a 1:1 correspondence between source and target fields.
I used ManageDatabase...Sync to get the fields into FM from the Oracle table in the first place. I know from past mistakes that data doesn't sync:) Each time I have added/deleted an Oracle field, I have synced and verified the new number of fields which match. When I begin the import, I use matching names and see a 1:1 correspondence between source and target fields.
Assuming the file you are importing into is local and not hosted, the import.log file is created in the same directory as the FM file. IF the file is hosted, import.log gets created in either "My Documents" (Win) or "Documents" (Mac). Delete all existing import.log files and then try your import again.
As an additional troubleshooting measure, try the import again, but instead of importing into the Oracle table, use the "New Table..." option available in the right-side pop-up menu of the Import Field Mapping dialog, just to see if it will import correctly into FM without errors (and to eliminate any question about the problem being with the import file or data). That should definitely create an import.log file. Always delete or rename import.log files if you wish to easily locate the most recently generated one.
As an additional troubleshooting measure, try the import again, but instead of importing into the Oracle table, use the "New Table..." option available in the right-side pop-up menu of the Import Field Mapping dialog, just to see if it will import correctly into FM without errors (and to eliminate any question about the problem being with the import file or data). That should definitely create an import.log file. Always delete or rename import.log files if you wish to easily locate the most recently generated one.
ASKER
They are hosted locally. Here is a successful log file when I imported to New Table and the failed log from importing to the shadow table.
Import-SuccessNewFile.log
Import.log
Import-SuccessNewFile.log
Import.log
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the assistance to this point. I seem to be stuck with enterprise support on my end not knowing why permissions are different but unable to get to the root of the problem. I will close the ticket.
In your second paragraph you say:
Did you mean to say "generic_table.fp7"? It's unclear what you are saying...you were talking about importing into Oracle but here it seems like you are describing importing into a FileMaker file.
Based on what you've described as your goal, I would think the best approach would be to start with your FileMaker file, add the Oracle database as an external ODBC source IN the FileMaker file, and then do your import directly within FileMaker, importing from the table in FileMaker to Oracle table. I don't see any need to export an intermediary file.