| Author |
 Topic  |
|
|
Fez
Starting Member
USA
25 Posts |
 Posted - 17 November 2000 : 00:16:29
|
I took about 20 minutes to come up with this.
I created a program file called vfpsnitz.prg that takes a Snitz forum .MDB file, and creates a VFP container, with all the indexes.
Steps:
1. Create an ODBC DSN called "Snitz" that points to your Access file.
2. Create an empty directory, place in it the VFPSNITZ.PRG (at the end of this post).
3. Boot Visual FoxPro, and run VFPSNITZ.PRG.
4. After it's done, you will have all the data in VFP, along with all the indexes, and a few new ones for future use. New ones can be created later for additional speed.
5. To generate a CRUDE autonumber field modify the structure of each table, and place RECCOUNT() + 1 in each "Default Value" for the key fields (first fields) in each table. DANGER: By using this simple method, you CAN NEVER DELETE then PACK records. I will create a simple trigger later that will go to the last record, and add one to the highest number (whatever it may be) later... it's just late.
I HAVE NOT TESTED THIS USING THE FORUM CODE: It's just a start, to convert the data, and create the database container.
close all
set exclusive on
create DATABASE snitz
do vfp_add with "forum_category", "cat_id"
do vfp_add with "forum_config", "config_id"
do vfp_add with "forum_forum", "cat_id", "forum_id"
do vfp_add with "forum_members", "member_id", "m_name", "m_password"
do vfp_add with "forum_moderator", "mod_id", "forum_id", "member_id"
do vfp_add with "forum_reply", "cat_id", "forum_id", "topic_id", "reply_id", "r_author"
do vfp_add with "forum_topics", "cat_id", "forum_id", "topic_id", "t_author", "t_last_post"
do vfp_add with "forum_totals", "count_id"
drop view tmp
**
**
procedure vfp_add
parameter snitz_table, index1, index2, index3, index4, index5
CREATE SQL VIEW tmp ;
CONNECTION snitz ;
AS SELECT * ;
FROM &snitz_table
use tmp
copy to (snitz_table) production database snitz
do vfp_index with snitz_table, index1
do vfp_index with snitz_table, index2
do vfp_index with snitz_table, index3
do vfp_index with snitz_table, index4
do vfp_index with snitz_table, index5
return
procedure vfp_index
parameter tbl, idx
if !empty(idx)
use (tbl) exclusive
* uses left(10) becuause of tag size limits
index on &idx tag (trim(left(idx,10)))
endif
return
Fez
Developer of CouponPages.Com< |
|
|
Fez
Starting Member
USA
25 Posts |
Posted - 17 November 2000 : 11:11:31
|
Here's the code for the AUTONUMBER function that you will need to fill the key fields with.
It's pretty simple, just OPEN DATABASE SNITZ EXCLUSIVE (or whatever you called yours), then type MODIFY PROCEDURE, and paste this into the stored procedure window:
** place this stored procedure in the database container
** by using modify procedure, then paste this in
** to use it place: autonumber("key_field") in the default value of any
** field that needs to be unique. For it to work, the key field must be indexed
procedure autonumber
parameter fname
store alias() to db_pre_move
use (db_pre_move) again alias tmp_alias in 0
select tmp_alias
set order to (fname)
go bottom
store evaluate(fname)+1 to nextid
use
select (db_pre_move)
return nextid
After you do that, modify the structure of each table and change the default value of each key field (normally the first field) to say this:
autonumber("fieldname") where fieldname is the name of the field you want to autonumber. NOTE: The quotes are required, and you may be tempted to use field(1) (no quotes) as a parameter, which says use the name of the first field as the key, but my version is universal, so you can use it anywhere you want an automatic ID.
Example:
For the FORUM_MEMBERS table, the "default value" for the field "MEMBER_ID" would become: autonumber("member_id").
That takes care of the autonumber thing. It's pretty lame that VFP doesn't have such a simple feature in this version. Anyhow, this works fine in VFP. I still have not tried running the Snitz code with this new container, but it's a start, and I've been pretty busy over here lately.
Fez
Developer of CouponPages.Com< |
 |
|
|
Fez
Starting Member
USA
25 Posts |
Posted - 18 November 2000 : 20:12:59
|
Well... well... well..
I created the container, and wrote a short script to see if the autonumber would work outside of FoxPro... NOPE!
It comes through as a .NULL.
I will have to make the assumption that the VFP ODBC drivers that come with the system do not execute VFP code inside of the "default value" property of a field. I put a hard coded number there to see if it outright ignores the option, and it did put that number in, so I know it uses that field. Now the question is how do I get it to execute the code...Sheesh! It's only about 8 lines of code, but it ignores it... hmmm.
I'll go back to the drawing board on this one.
Fez
Developer of CouponPages.Com< |
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 18 November 2000 : 21:03:36
|
You can't run access VBA code via ODBC and ADO (at least I don't know how). It's probably a similar issue with VFP code. There is a discussion in the Oracle catagory about some code to replace autonumbers.
======
Doug G
======< |
|
|
|
Fez
Starting Member
USA
25 Posts |
Posted - 19 November 2000 : 13:09:07
|
Thanks, I'll visit that forum. It's starting to look like we'll need conditional clauses for ALL inserts.
For systems with an autonumber feature, inserting data as normal. For systems without, it looks like we're going to have to look ahead, get a new number, and insert in by hand. It's not the worst thing in the world, just a pain in the neck.
The nice side effect will be that the code could work with or without an autonumber feature.
Fez
Developer of CouponPages.Com< |
|
|
|
gor
Retired Admin
Netherlands
5511 Posts |
Posted - 19 November 2000 : 13:24:00
|
Fez,
You can find the discussion Doug was referring to here: http://forum.snitz.com/forum/link.asp?TOPIC_ID=2146
As a sidenote: though it might seem that none of us care because there hardly are replies, that is not the case.
Me for one have just one big problem: I've never worked with VFP before and don't have one available to me to test what you do on. That makes it hard to think along with you.
But I really appreciate your efforts so far !! 
Pierre Gorissen
Even if you're on the right track,
you'll get run over if you just sit there.
Will Rogers< |
|
|
|
Fez
Starting Member
USA
25 Posts |
Posted - 19 November 2000 : 13:56:15
|
Thanks for the link, I've already joined that talk. It seems the issue of autonumbering is something we all have to face, not just VFP, but Oracle, and who knows what other platforms.
I've proposed a solution, and I am willing to code it. By using the autonumber system inside of Access we've closed the door to quite a few platforms. I think a universal numbering system would be the best thing for us. We gain control of a critical component in the system, and open the door to all the platforms out today, and in the future.
My solution will reside in the Global.ASA, and in the INC_FUNCIONS.ASP files.
This conversation should continue in the Oracle forum.
Fez
Developer of CouponPages.Com< |
|
|
|
gor
Retired Admin
Netherlands
5511 Posts |
Posted - 19 November 2000 : 15:44:20
|
quote:
As a sidenote: though it might seem that none of us care because there hardly are replies, that is not the case.
Me for one have just one big problem: I've never worked with VFP before and don't have one available to me to test what you do on. That makes it hard to think along with you.
But I really appreciate your efforts so far !!
Ok, it looks like I do have VFP because it is part of Visual Studio 6.0 Enterprise Edition.
So now I've got 10 minutes of experience working with VFP
Fez,
Couldn't we just create tables in VFP using SQL and ODBC ? (haven't installed the help-files yet)
Pierre Gorissen
Even if you're on the right track,
you'll get run over if you just sit there.
Will Rogers< |
|
|
|
Fez
Starting Member
USA
25 Posts |
Posted - 19 November 2000 : 17:03:23
|
Yes, You can create the tables by hand, but if you just run the FoxPro code I posted, you'll have an exact duplicate not only of the Snitz Access table structure, but it will even import all your data.
The default IMPORT / EXPORT functions will not really work. Trust me, running the script is FAST! And you can test it on real data!
One thing, make sure your VFP has the Collate sequence set to "GENERAL". If you want to play it safe, add SET COLLATE TO "GENERAL" to the script above.
It will work both ways, but if your default collate sequence is not set to GENERAL, then the database will be case sensitive... people hate having to type their member name and password in a case sensitive world.
Again: SET COLLATE TO "GENERAL" in the above, or from a FoxPro Command box.
Fez
Developer of CouponPages.Com< |
|
|
|
Flex
Starting Member
Canada
9 Posts |
Posted - 18 June 2001 : 22:42:34
|
Fez,
Any futher development on thos front? I am very interested in how this has progressed.
As for the auto number issue, why not use SYS(2015) to generate a randon number.?
Regards,
Chris Lang< |
|
|
| |
Topic |
|
|
|
Forum Locked
