DATA Data1...and other topics
Richard Katz
Apparantly, Visual Foxpro passed a major hurtle yesterday; my manager
decided to accept using Visual Foxpro 5 in our data warehousing/Intranet
product!
I have been using VFP in the prototype system, but only yesterday was it
approved for production. This is the first time in a long time I've
been able to have the program officially sanctioned in a client/server
environment.
It's been highly successful in this environment. It's generally fast.
It's easy to develop extract code with, and it's efficient at moving
many megabytes of data around, parking the data on other network drives
and so on. You couldn't do these kinds of things with MS Access because
the procedures will cause MDB bloat.
However, winning this battle is not the end of a war, but the beginning
of other things. The question I now face is: How do I train the
department programmers to be able to use VFP in a client-server
environment?
I'll be writing some of this to Microsoft.public.vfp.dbc and so on, when
the news server wakes up. At the moment, I think, the anti-spammers
might be cleaning newsgroups, I guess, because I'm calling from the
normal Earthlink number which I think is on UUNET and no newsgroups are
getting downloaded from msnews at the moment.
Here's one of my problems. Our application requires querying SQL
Server, Oracle, and importing from some other database systems. Because
these have long field names, I want to train people in how to
consistantly replicate table structures properly when they do a query or
copy.
The only way I know how to do this is to append "DATA Data1" to the end
of every command.
It's like when you have everyone read a fortune cookie and append "In
bed" to the end of it. Try it some time. It's fun.
Back to DBC, let's say I've got an SQL Server remote view called assets.
To query the table properly you need to do:
SELECT * FROM assets ... INTO TABLE pulled DATABASE data1.
Or
USE assets
COPY TO pulled DATABASE data1
Without telling VFP to load the result into a table in the DBC, it will
truncate fieldnames, producing an FX2 compatible file.
Is there a SET default that can be set so that you don't need to do this
- like SET DBC ON so that it defaults to writing long fieldnames?
Second problem. I need a way of having REMOVE TABLE tablename DELETE to
be well behaved within a program. In other words, if the table doesn't
exist in the DBC, it should just ignore the REMOVE part of the statement
and possibly delete the table anyway. If the table exists in the DBC
but not on disk, it should just remove it from the DBC.
I imagine I can get around this somewhat by using ON ERROR. But, it's
still a training issue. Right now, the only way to tell ahead of time
if the table is in the DBC is to run SQLTABLES(). However, you can't
run SQLTABLES() on an openned database -- (maybe this is only if it's
exclusively used, I'm not sure). And if you REMOVE a table that doesn't
exist you get an error. In some cases, the only way to get the table
out of the DBC is through the GUI command MODIFY DATA, find the table,
press the DELETE key, and then answer some nasty little pop-up screen
that asks if you want to locate the table or just remove it regardless.
What I'd like is a REGARDLESS clause on the REMOVE TABLE command.
Third problem. Unrelated, but similar. I need a way to query a class
library so that it will tell me the names of subclasses under a
particular class. Right now, I have to close down the class library and
query the VCX using a somewhat complex database command. But, the
behavior is eratic. Sometimes, I can re-open the class library (SET
CLASSLIB TO) afterwards, and sometimes I'm going to get a FILE IN USE
error. Is there any way to query an already openned class library?
The VCX is *almost* a PSE (object oriented database - persistant storage
environment) and it appears that with some minor coaxing, it could be
made to function that way.
Thanks for any help,
Richard Katz
rich...@earthlink.com
Richard Katz
Garrett Fitzgerald wrote:
> As far as I know, the DBC is implicit, unless you specifg otherwise. In
> other words, you really shouldn't have to specify the database unless
> there's a name conflict.
>
> > But SET CLASSLIB seems to cause a lot of
> > problems for "SELECT FROM cccc.VCX WHERE" kinds of queries.
>
> I haven't checked this out, but can you instantiate the Class Browser and
> then use its methods to do your queries?
> --
> Garrett Fitzgerald
> Software R&D
> MicroKnowledge, Inc.
> Bangor, Maine
Garret,
Thanks.
1) That sounds interesting about instantiating the class browser.Can you
instantiate the class browser within an EXE? If so, I'll try it. How
do you do that?
2) I don't get an implicit DBC reference. Is there a way to turn it
on?
I've just retested these this AM on VFP 5.0. The commands:
OPEN DATA data1
SELECT * FROM <table> INTO TABLE <table2>
Results in truncated field names in table2. While the commands:
SELECT * FROM <table> INTO TABLE <table2> DATABASE data1
SELECT * FROM <table> INTO CURSOR <cursor2>
do not truncate field names. If you know a way you can make DBC
references implicit, let me know.
Richard Katz
Garrett Fitzgerald
In article <33EFB0...@juts.ccc.amdahl.com>, Richard
Katz <rd...@juts.ccc.amdahl.com> proclaimed...
> Quite often, however, you
> don't want a cursor as an intermediate table because you need to index
> it, or you need to save it for a step you are going to run tomorrow.
FWIW, you can index cursors, subject to some limitations....
> Absolutely. I use a DBC. I even open the DBC before starting. But, as
> far as I know, the DBC is ignored unless you use the DATABASE clause on
> every command.
Garrett Fitzgerald
Garrett Fitzgerald
In article <33F0CA...@juts.ccc.amdahl.com>, Richard Katz
<rd...@juts.ccc.amdahl.com> proclaimed...
> 1) That sounds interesting about instantiating the class browser.Can you
> instantiate the class browser within an EXE? If so, I'll try it. How
> do you do that?
"This proof is left as an exercise for the reader." :-) Seriously,
though, I'm not sure you can build BROWSER.APP into an EXE: it might be
something you can only do with the development version. Check the online
docs under Find for "class browser".
> I've just retested these this AM on VFP 5.0. The commands:
>
> OPEN DATA data1
> SELECT * FROM <table> INTO TABLE <table2>
>
> Results in truncated field names in table2. While the commands:
> SELECT * FROM <table> INTO TABLE <table2> DATABASE data1
> SELECT * FROM <table> INTO CURSOR <cursor2>
>
> do not truncate field names.
Oh, right, ok, I see what you mean. What that select command is doing is
adding table2 to data1: that's why the long field names are maintained.
As you found, SELECT...INTO CURSOR also maintains long field name,
without adding anything to the DBC. This would be my preferred solution:
is there a reason you need that table to stick around long-term?
Richard Katz
Garrett Fitzgerald wrote:
>
> In article <33F0CA...@juts.ccc.amdahl.com>, Richard Katz
> <rd...@juts.ccc.amdahl.com> proclaimed...
> > 1) That sounds interesting about instantiating the class browser.Can you
> > instantiate the class browser within an EXE? If so, I'll try it. How
> > do you do that?
>
> "This proof is left as an exercise for the reader." :-) Seriously,
> though, I'm not sure you can build BROWSER.APP into an EXE: it might be
> something you can only do with the development version. Check the online
> docs under Find for "class browser".
>
> Software R&D
> MicroKnowledge, Inc.
> Bangor, Maine
I'll try that and ask MS - if it's not in documentation for the
distribution kit. This will give you a picture of what I'm doing,
within the EXE or DLL:
1. User presses Button that loads form.
2. In INIT:
SELECT <classnames> FROM dynamic.VCX ;
WHERE <class belongs to a specified baseclass, usually
Container>
USE in dynamic
Present user with a list box of <classnames>
3. User selects a class from the list box and presses "Load" button.
4. SET CLASSLIB TO static, dynamic
v=CREATEOBJECT(<classname>)
5. User adds objects to v, deletes objects from v, modifies v
properties.
6. User pushes Save As button and enters <newclassname>.
7. z=CREATEOBJ(<superclass of classname>)
* Copy v to z
FOR ALL controls in v
copy them into z
ENDFOR
FOR ALL properties in v (that are read/write)
Copy them to z
ENDFOR
8. z.SaveAsClass(<newclassname>,dynamic)
z.release
9. Form is terminated - releasing v.
What's gained by this technique?
First, you let VFP compose and decompose the class and it's associated
objects with CREATEOBJ and SAVEASCLASS - you don't have to build your
own load and save routines just to build objects. Second, you don't
have to build and maintain additional tables. Third, you get multiple
levels of hierarchy if you want or need them without having to build
multiple levels of tables that have to be related to each other.
Essentially, you are using the class library as a dynamic ODBMS. The
classes you store can represent reports, complex query sequences,
tab-separated file imports, forms, almost any data transform or
production rules you can think of.
But, I run into a file sharing problem after this sequence. To make the
new class appears in the list box the next time, I do this:
SET CLASSLIB TO && Closes all class libraries (allegedly)
SET CLASSLIB TO static && dynamic.VCX is no longer in use
(allegedly)
This should make it possible to re-run the SELECT from dynamic.VCX.
But, it doesn't. For some reason, a file handle for dynamic.VCX
apparantly remains active.
Later,
Richard
Richard Katz
Garrett Fitzgerald wrote:
>
> > I've just retested these this AM on VFP 5.0. The commands:
> >
> > OPEN DATA data1
> > SELECT * FROM <table> INTO TABLE <table2>
> >
> > Results in truncated field names in table2. While the commands:
> > SELECT * FROM <table> INTO TABLE <table2> DATABASE data1
> > SELECT * FROM <table> INTO CURSOR <cursor2>
> >
> > do not truncate field names.
>
> Oh, right, ok, I see what you mean. What that select command is doing is
> adding table2 to data1: that's why the long field names are maintained.
> As you found, SELECT...INTO CURSOR also maintains long field name,
> without adding anything to the DBC. This would be my preferred solution:
> is there a reason you need that table to stick around long-term?
> --
> Software R&D
> MicroKnowledge, Inc.
> Bangor, Maine
Garrett,
Most definitely.
1) The files get to be rather large and are used in numerous queries
after downloading. You don't want them openned while you run other
operations.
2) This is a data warehousing application. The data coming in needs to
be cleaned and synchronized with data from other systems of record.
After cleaning it, we don't just throw it away. We publish a trial
version, the clients look at the results, and we may clean it some more,
and then publish it in Excel or HTML. This process may take several
days.
3) It becomes a training issue. And things would be better for new
programmers if it wasn't one. Tables can exist in 3 states: 1) bound to
the DBC, 2) unbound FX2, 3) unbound VFP (by remomving them without
deleting).
Rather than have to deal with FX2 format, why not just have a default
setting such that when the DBC is open, the table is saved in state 2 or
state 3 unless you specifically ask for FX2 format? Tables in state 1
are no longer compatible with 99% of the other databases you encounter
in a C/S environment. I was just hoping I'd missed something and there
was a SET command someone knew that would fix this.
Thanks,
Richard
Garrett Fitzgerald
In article <33F1CD...@earthlink.com>, Richard Katz
<rich...@earthlink.com> proclaimed...
> Essentially, you are using the class library as a dynamic ODBMS. The
> classes you store can represent reports, complex query sequences,
> tab-separated file imports, forms, almost any data transform or
> production rules you can think of.
I'm still confused on what the benefit of this is. Don't bother trying to
explain it, though: just get the code working and ignore me. :-)
Richard Katz
Garrett,
No, we can't use updatable views. In data warehousing you never update
the original system anyway (which is what an updatable cursor is
generally used for). It's strictly a one way ticket only.
I guess I could explain the data warehousing type of application a
little more clearly. Data warehousing is an extract and summary
process for the purpose of creating strategic reports and drill-down
user interfaces. Data warehousing generally requires:
1) Synchronizing of multiple data sources.
2) Incremental extraction
To synchronize incremental data, you need to keep a kind of "repository"
which is somewhat permanant where the rest of the data can wait and be
matched up with newly arriving data.
For example, you download a set of service calls from one source, and
time recorded against service calls from another. A service call may
activate on August 1st, while different actions occur on August 4th,
August 5th, and so on. So, the service order hits the repository on
August 2nd. But the service order record has to wait around unti August
6th and 7th for measurement data.
So, it waits in the DBC table.
It is common practice in decision support (DSS) and data warehouse
applications to process the data "off-line" so that you do not impact
the performance of on-line systems. So, all the off-line data winds up
in the DBC and stays there.
Therefore, solutions where the data only winds up in cursors, updatable
or not are of little interest.
Richard Katz
Garrett Fitzgerald wrote:
>
> In article <33F1C6...@earthlink.com>, Richard Katz
> <rich...@earthlink.com> proclaimed...
> > 1) The files get to be rather large and are used in numerous queries
> > after downloading. You don't want them openned while you run other
> > operations.
>
> I don't think this is as much of a problem as you do: in my experience,
> disk handles are cheap. :-)
>
> > After cleaning it, we don't just throw it away. We publish a trial
> > version, the clients look at the results, and we may clean it some more,
> > and then publish it in Excel or HTML. This process may take several
> > days.
>
> That, OTOH seems like a good reason to me. :-) Just for the sake of
> argument, though, have you looked at using updateable views? This might
> give you what you need. I don't know why, but adding tables to the DBC at
> runtime really grates on my nerves....