I have searched Focal Point and Advanced Search (and just what is so 'Advanced' about this generally useless tool ?) long and hard to find pretty much nothing on the subject of converting Focus databases to SQL Server tables.
This was frustrating, bit I tried to figure it out on my own...

As an example question on this subject, suppose we have a simple 2 segment Focus file:

FILENAME=EXAMPLE , SUFFIX=FOC ,$
SEGNAME=SEG1 ,SEGTYPE=S1 ,$
FIELD=KEY_1 ,ALIAS=K1 ,FORMAT=A8 ,INDEX=I,$
FIELD=ATTRIBUTE1 ,ALIAS=ATT1 ,FORMAT=A10 ,$
...
SEGMENT=SEG2 ,PARENT=SEG1 ,SEGTYPE=S1 ,$
FIELD=KEY_2 ,ALIAS=K2 ,FORMAT=A8 ,INDEX=I,$
FIELD=ATTRIBUTE21 ,ALIAS=ATT21 ,FORMAT=A10 ,$
...

I created two tables in SQL Server:

Table SEG1 with one primary key KEY_1,
Table SEG2 with a primary key consisting of KEY_2 and KEY_1 (as a foreign key).

So far so good, but I then tried to test a Maintain program written for the Focus file, after adjusting the Path Spec statements,
on my new tables and synonyms, and it keeps failing with a 'stack contains multiple paths' error on one of the SEG2 table stacks.

I don't know quite what is going on here and would like to get some idea of just what are the best practices (or ordinary ones for that matter) for converting Focus databases to SQL tables.

Thanks,

I don't know if it is best practice, but I would use WebFocus to pull the data into a flat file and then use SQL insert to load the SQL database.

I think Dave is looking for a solution to a more complex problem - how to convert a Maintain program to use relational database tables instead of a hierarchical database table.

At first I thought creating a view that joins the two SQL Server tables would be enough, but I don't think that would do the job, probably because (I know nothing of Maintain) the program traverses the two segments of the hierarchical database table differently than it would for relational database tables.

The job entails more than just replacing FOCUS databases by relational tables.

quote:

The job entails more than just replacing FOCUS databases by relational tables

I haven't used Maintain for 5 or 6 years so I may be completely off base on this one, but I think Francis hit it right on. It seems like there was a big difference between loading from a stack into a multi-segment FOCUS table and loading into two relational database tables. I'll defer to the Maintain gurus for additional direction on exactly how it should be accomplished, but I believe, as Francis said, that there are a few more steps involved than just moving the tables.

This message has been edited. Last edited by: Darin Lee,

Dave
If you are getting the "Stack contains multiple paths" error, make sure that you are prefacing the field names with the Master name. You have to do this in NEXT statements, WHERE statements, Update, Include, Delete and Revise. Since the Masters have fields with the same names in them, you must do this.

So, while in FOCUS you may have had:
FOR ALL NEXT KEY_1 KEY_2 INTO STACK
Now you would have to have:
FOR ALL NEXT SEG1.KEY_1 INTO STACK1
and
FOR ALL NEXT SEG2.KEY_1 SEG2.KEY_2 INTO STACK2

You can use criteria from STACK1 to load up STACK2, but you can't load the data from both Masters into one stack.

Mark

Darin, "Tony"? "Francis" - a collision of wise-guys.

While others have adressed converting an APPLICTION let me address the first question posed which was about how "converting Focus databases to SQL Server tables" which can most easily done using our ETL (Extract-Transform-Load) tool called DataMigrator.

The think to keep in mind here is that FOCUS as a hierarchical database handles the relationships between the segments automatically, but for an RDBMS the relationships are *explicit* in the synonym.

You can do this easily with Data Management Console modeling view, but here's a MASTER file for your MS SQL Server tables.

FILENAME=EXAMPLE , SUFFIX=SQLMSS ,$
SEGNAME=SEG1 ,SEGTYPE=S0 ,$
FIELD=KEY_1 ,ALIAS=K1 ,FORMAT=A8 ,INDEX=I,$
FIELD=ATTRIBUTE1 ,ALIAS=ATT1 ,FORMAT=A10 ,$
...
SEGMENT=SEG2 ,PARENT=SEG1 ,SEGTYPE=S0 ,  CRJOINTYPE=LEFT_OUTER, $
FIELD=KEY_1S ,ALIAS=K1 ,FORMAT=A8 ,INDEX=I,$
FIELD=KEY_2 ,ALIAS=K2 ,FORMAT=A8 ,INDEX=I,$
FIELD=ATTRIBUTE21 ,ALIAS=ATT21 ,FORMAT=A10 ,$
  

And here's an ACCESS file:

SEGNAME=SEG1, TABLENAME=example_seg1, KEYS=1, WRITE=YES, $
SEGNAME=SEG2, TABLENAME=example_seg2, 
   KEYFLD=KEY_1, IXFLD=KEY_1S ,  KEYS=2,  WRITE=YES, $

Now from the Data Management Console you can select your synonym and "Recreate DBMS Table" and it will create TWO tables for you.

Then a simple data flow with your FOCUS file as the source and the synonym above as a target is all that's needed to copy the data from FOCUS to MS SQL Server.

Sorry Francis - I had just been reading a post by Tony and got my wise guys mixed up. Credit to you.

Mark,

Thanks for the advice. I fully qualified the WHERE fields and my .mnt finally ran ! You da man !

Note: my maintains always treat segments separately so they *should* work with tables easily.

Clif,

Thanks for the DataMigrator advice. I'd really like to work with it, but don't think the customer is going to pop for the extra expense for a one time thing...


All,

While the maintain conversion was my immediate problem, I'd really like some good practical advice on how to structure relational tables from Focus database. I know the general idea, but not real world stuff - strategies, rules-of thumb, gotchas, etc.

Thanks,

Hi. I know that this is months late, but the way that I did most of this was through MODIFY using CASE LOGIC and then write to file using TYPE ON Statement. This extracted the data. I then use SSIS to load the data into SQL Server.
Any one that would like a copy of the code that I use, let me know.
regards steve njm870t@hotmail.com