Filip Brnic
Programmer
Command button in the form that checks if the table is in the project, if the table doesnt exist create it, if it does then do nothing.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Create a table if it doesn't exist, if it does do nothing.
- Thread starter Filip Brnic
- Start date
- Status
Chris Miller
Programmer
Wrong idea - looking for a table in a project.
The project exists during development, when you finally build an exe what exists is an EXE from that PJX and the PJX file (and all futher source code files) stays where it is and is not distributed, you only distribute an EXE and the VFP runtime, and database files. And "distribute" may also just be for yourself on your PC, doesn't matter. Which means, you shouldn't look for a table in a project, that's a way to only do things in the development environment and not be usable, once you could build applications for selling them, the final user will have no project, but the EXE and database files.
So what you rather look for is, if a table exists in a directory/folder or in a DBC, depends on your choice of using free tables or a DBC with database tables.
There is only one use case where you would need an operation like this, if you want to create a database system, but if your tables are VFP tables, that's nothing new. Anyway, the commands to check for a table are simply on the level of whether file exists FILE() or ADIR(). On the level of a DBC, whether a table exists in it can be checked with INDBC('tablename','table'). ADBOBJECTS() could also be used to create a list (an array) of all objects (connections, tables, views) in a DBC, and if a table isn't found in there (could be done with ASCAN), then you can create it.
Chriss
The project exists during development, when you finally build an exe what exists is an EXE from that PJX and the PJX file (and all futher source code files) stays where it is and is not distributed, you only distribute an EXE and the VFP runtime, and database files. And "distribute" may also just be for yourself on your PC, doesn't matter. Which means, you shouldn't look for a table in a project, that's a way to only do things in the development environment and not be usable, once you could build applications for selling them, the final user will have no project, but the EXE and database files.
So what you rather look for is, if a table exists in a directory/folder or in a DBC, depends on your choice of using free tables or a DBC with database tables.
There is only one use case where you would need an operation like this, if you want to create a database system, but if your tables are VFP tables, that's nothing new. Anyway, the commands to check for a table are simply on the level of whether file exists FILE() or ADIR(). On the level of a DBC, whether a table exists in it can be checked with INDBC('tablename','table'). ADBOBJECTS() could also be used to create a list (an array) of all objects (connections, tables, views) in a DBC, and if a table isn't found in there (could be done with ASCAN), then you can create it.
Chriss
Code:
IF FILE("YourTableName.dbf")
MESSAGEBOX("The table exists.", 0, "Table Check")
ELSE
MESSAGEBOX("The table does not exist.", 0, "Table Check")
ENDIFIn between the IF ELSE and ELSE ENDIF, you can create your conditions for what you want it to do based on the existence of ANY file (table or not), in a specified directory.
No need to determine what the file type is or any other data, you can just check for any file (dbf or otherwise) exists, and then do whatever you want based on the result.
Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCAP, CDCP, CDCS, CDCE, CTDC, CTIA, ATS, ATD
"I try to be nice, but sometimes my mouth doesn't cooperate.
Chris Miller
Programmer
True, Scott, FILE is a universal solution, whether the DBF is in a DBC or not. And the only thing missing would be changing directory into the directory of tables of your data before the FILE() check.
You should organize your data files to be in one directory, everything else - i.e. sporadic DBF files in many directories - would be confusing, though one valid scenario for DBFs in multipe directories is generating directories for each month/quarter (partition by time) or for each customer. Anyway, details. The main point is a FILE() check only needs the tablename, that's right in Scotts example, provided you change into the data directory. You don't even need to specify .dbf, as FILE will search for DBFs if no file extension is specified.
What speaks for INDBC() is that it explicitly does not only check whether a DBF file exists, but whether the DBC already has a table with the same name belonging to it - anywhere. If that's in a subdirectory (for reasons of partitioning data by time or customer or other things), then checking for a file could tell you "no, file does not exist", but the DBC could have a table of that name in another directory. Crreating a table of the same name still would fail, not because of a name collision in the directory in file names, but of the requirement of the uniqueness of table names within a DBC.
In essence, when you use a DBC, rather check what you're allowed to use as table name or not by INDBC(). You also will need to check whether the file name has no collision, so you'd need both checks. Or you prevent directories to have name collisoins in tables within them, by chooseing a strategy where you always create tables of a subdirectory togther with the subdirectory, so you know it's empty to start with.
All this is still subject to be nonsense, because wither you distribute an executable with all database files and not need to check for a table exisitng, or you distribute your application with code generating a whole database, where you would let the user choose a root directory to create empty and then go from ground zero and wouldn't need to check for table or file existence at all, you can generate all the tables you need and know nothing exists at start of that database generation.
If you generate data for partioning purpioses, ie.e. the tables per month, per cusomter or per any other "dimension", then an easy way to do that is generate a directory first and know it's empty for all the months/customers/anything else you need tables for. In all these practices you don't need a tbale exists check.
Chriss
You should organize your data files to be in one directory, everything else - i.e. sporadic DBF files in many directories - would be confusing, though one valid scenario for DBFs in multipe directories is generating directories for each month/quarter (partition by time) or for each customer. Anyway, details. The main point is a FILE() check only needs the tablename, that's right in Scotts example, provided you change into the data directory. You don't even need to specify .dbf, as FILE will search for DBFs if no file extension is specified.
What speaks for INDBC() is that it explicitly does not only check whether a DBF file exists, but whether the DBC already has a table with the same name belonging to it - anywhere. If that's in a subdirectory (for reasons of partitioning data by time or customer or other things), then checking for a file could tell you "no, file does not exist", but the DBC could have a table of that name in another directory. Crreating a table of the same name still would fail, not because of a name collision in the directory in file names, but of the requirement of the uniqueness of table names within a DBC.
In essence, when you use a DBC, rather check what you're allowed to use as table name or not by INDBC(). You also will need to check whether the file name has no collision, so you'd need both checks. Or you prevent directories to have name collisoins in tables within them, by chooseing a strategy where you always create tables of a subdirectory togther with the subdirectory, so you know it's empty to start with.
All this is still subject to be nonsense, because wither you distribute an executable with all database files and not need to check for a table exisitng, or you distribute your application with code generating a whole database, where you would let the user choose a root directory to create empty and then go from ground zero and wouldn't need to check for table or file existence at all, you can generate all the tables you need and know nothing exists at start of that database generation.
If you generate data for partioning purpioses, ie.e. the tables per month, per cusomter or per any other "dimension", then an easy way to do that is generate a directory first and know it's empty for all the months/customers/anything else you need tables for. In all these practices you don't need a tbale exists check.
Chriss
Chris Miller
Programmer
Maybe I was too strong on the aspect you shouldn't need these checks for existence of tables, because my point of view was focussing on the rule of thumnb an application should know by itself where its data is. By it's setup, or by creating its database in the inital run. In all these scenarios I see it as an advantage you won't ever neeed this kind of existence checks.
To show one more aspect, if you think about an application where different users each have their own database - it's not necessary to separate data by separation of files, your application can put data of multiple users into one general database and filter the access to data belonging to each usser.
Another line of thinking may be you want to create databases like other applications create documents. A scenario where users usually decide where to store their documents within the directories. One thing that speaks against the user specifying where to store some database or table is, that when using this idea a user will usually want to be able to go to such a directory, double click a "document", which in your case is a dbf or dbc, and then your application should be associated with these file extensions, dbf or dbc. But that makes thee file extensions unavailable to any oother foxpro applicatios, so that's a bad idea. Even though it is technically possible to let a doubleclick on some dbf or dbc file start your application, what should other Foxpro applications do, that also want to associate their data with their application, and it's like having multiple image painting applications each wanting to associate image file extensions to themselves.
With a databse application you expicitly have the chance to hide this location of files from users. When you also take care of backups, then you make it easier for users, they don't need to know where their data goes. I don't want to narrow your ideas, though, do as you like, just think about whther it really is necessary or good or which strategy to go about data is better.
One more piece of knowledge here is that you can determine where some special folders are, like a windows users documents folder for their single user databases or directoreis for all users in case your application should handle data for mutiple users. Last not least applications that will have a shared database not only for multiple users/user accounts on one (home) PC, but for multiple users of a company, in something called a share, conceptually a server side directory all workstations can access.
So that's one aspect to think about when you start programming an application. Should data be separate per user? Is it a single PC application stopring data locally, again per user or for all users? Or is it an application needing a central databse. It could even be in different offices around the world and then need databses on the internet. Which would also be something you wouldn't let the user decide but prepare for him automatically. You would rather host a database server like MySQL and access that, DBFs on the internet are less of a usual scenario, even for FoxPro applications, though there is one sample scenario for that, too, if you program a website that runs on the basis of VFP code and has a local database (from the point of view of the application) and the accessibility ofd a website through the internet makes this local database a central worldwide databse for every user.
One thing is very common in all these scenarios: The user wil know what application to start, but won't need to know where the data is. That's usually considered an advantage of database applications in comparison to applications working on (binary) documents or other files, like images. And the inconvenience to need to know where files are also led to the introduction of a users documents folder in Windows, in the end, and the Downlaods, Videos, Muisc, Pictures special folders. So there already is a bse structuring of where which type of files or data goes.
Chriss
To show one more aspect, if you think about an application where different users each have their own database - it's not necessary to separate data by separation of files, your application can put data of multiple users into one general database and filter the access to data belonging to each usser.
Another line of thinking may be you want to create databases like other applications create documents. A scenario where users usually decide where to store their documents within the directories. One thing that speaks against the user specifying where to store some database or table is, that when using this idea a user will usually want to be able to go to such a directory, double click a "document", which in your case is a dbf or dbc, and then your application should be associated with these file extensions, dbf or dbc. But that makes thee file extensions unavailable to any oother foxpro applicatios, so that's a bad idea. Even though it is technically possible to let a doubleclick on some dbf or dbc file start your application, what should other Foxpro applications do, that also want to associate their data with their application, and it's like having multiple image painting applications each wanting to associate image file extensions to themselves.
With a databse application you expicitly have the chance to hide this location of files from users. When you also take care of backups, then you make it easier for users, they don't need to know where their data goes. I don't want to narrow your ideas, though, do as you like, just think about whther it really is necessary or good or which strategy to go about data is better.
One more piece of knowledge here is that you can determine where some special folders are, like a windows users documents folder for their single user databases or directoreis for all users in case your application should handle data for mutiple users. Last not least applications that will have a shared database not only for multiple users/user accounts on one (home) PC, but for multiple users of a company, in something called a share, conceptually a server side directory all workstations can access.
So that's one aspect to think about when you start programming an application. Should data be separate per user? Is it a single PC application stopring data locally, again per user or for all users? Or is it an application needing a central databse. It could even be in different offices around the world and then need databses on the internet. Which would also be something you wouldn't let the user decide but prepare for him automatically. You would rather host a database server like MySQL and access that, DBFs on the internet are less of a usual scenario, even for FoxPro applications, though there is one sample scenario for that, too, if you program a website that runs on the basis of VFP code and has a local database (from the point of view of the application) and the accessibility ofd a website through the internet makes this local database a central worldwide databse for every user.
One thing is very common in all these scenarios: The user wil know what application to start, but won't need to know where the data is. That's usually considered an advantage of database applications in comparison to applications working on (binary) documents or other files, like images. And the inconvenience to need to know where files are also led to the introduction of a users documents folder in Windows, in the end, and the Downlaods, Videos, Muisc, Pictures special folders. So there already is a bse structuring of where which type of files or data goes.
Chriss
FILE() does not always return the expected result. If the file is included in the built EXE (or a file has the same name), then FILE will always return True for the file in the project. An alternative is to use ADIR() function which always returns what is on the disk and does not consider any files contained in the built EXE.
Greg
Greg
This is a snippet of the code I use. The app has a version number and the database must match that number, if not
code like this is invoked.
m.EXISTING is the current DB version, MYFILE() is a FILE() like udf that avoids ggreen61's thought, along with false positives from the current path
(don't know if that problem still exists)
Regards
Griff
Keep [Smile]ing
code like this is invoked.
m.EXISTING is the current DB version, MYFILE() is a FILE() like udf that avoids ggreen61's thought, along with false positives from the current path
(don't know if that problem still exists)
Code:
IF m.EXISTING = "1.23"
SELECT 0
IF !MYFILE(CHANGE_DIR+'PROJINV.DBF')
CREATE TABLE 'PROJINV.DBF' NAME 'PROJINV' (PROJECTNO C(20) NOT NULL, ;
INVOICE_NO C(6) NOT NULL, ;
FLAG L NOT NULL)
***** Create each index for PROJINV *****
INDEX ON PROJECTNO+INVOICE_NO TAG PROJINV1
INDEX ON INVOICE_NO+PROJECTNO TAG PROJINV2
ENDIF
...Regards
Griff
Keep [Smile]ing
There are 10 kinds of people in the world, those who understand binary and those who don't.
I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.
There is no place like G28 X0 Y0 Z0
I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.
There is no place like G28 X0 Y0 Z0
Chris Miller
Programmer
Everything is you both say is correct.
Still, even if file() would always only check file existence on the harddrive, there could be a table with that name in a DBC anyway, so file /adir check alone does only suffice in the cases where you organize all DBF files of the tables in one directory, and then it can work no matter if you use a DBC or not. You can't always assume others work by the same rules or "gut feelings" you developed over years of experience, that's why I layed all this down and try to showcase why thinking along the lines of a construct like CREATE TABLE IF NOT EXISTS is not appropriate.
Even in context of other databases systems having this in their SQL language (T-SQL for example) what can it really solve? You avoid an error by trying to create a table that already exists. Okay, fine, but it will still not allow you to write SQL scripts that can be run without any concerns of getting a final incomplete database structure, i.e. repair a database with a missing table by running the complete database generation script. Because it does not act upon any further things like foreign key constraints, etc. This type of SQL is not helpful even where it is part of the SQL dialect, in my oppinion.
Essence of that: Care for a good situation regarding your data. And the simplest point in time is the setup. If you have any problems in a database, that's a maintanence task for you, not for an automatic repair script fixing any possible problem you experienced or even not ever experienced and surely not fixing a rogue user that simply deletes random files from your data directory. Against that you should think about proper file permisssions, fo example. Application users should be able to create and modify files, but never to delete them, for example. And for shared access guarding a share is an admin task.
The only stable situations are a) there is no databse at all and you create it completely, or b) the datbase or databases exists with all necessary tables. and you rely on it. Any other state is not solved with a click of a button anyway. You can only think that way if you have a naive idea of a database and don't know how related data works, what constraints are, etc. A faulty situation needs the developer or an admin to look at it.
Chriss
Still, even if file() would always only check file existence on the harddrive, there could be a table with that name in a DBC anyway, so file /adir check alone does only suffice in the cases where you organize all DBF files of the tables in one directory, and then it can work no matter if you use a DBC or not. You can't always assume others work by the same rules or "gut feelings" you developed over years of experience, that's why I layed all this down and try to showcase why thinking along the lines of a construct like CREATE TABLE IF NOT EXISTS is not appropriate.
Even in context of other databases systems having this in their SQL language (T-SQL for example) what can it really solve? You avoid an error by trying to create a table that already exists. Okay, fine, but it will still not allow you to write SQL scripts that can be run without any concerns of getting a final incomplete database structure, i.e. repair a database with a missing table by running the complete database generation script. Because it does not act upon any further things like foreign key constraints, etc. This type of SQL is not helpful even where it is part of the SQL dialect, in my oppinion.
Essence of that: Care for a good situation regarding your data. And the simplest point in time is the setup. If you have any problems in a database, that's a maintanence task for you, not for an automatic repair script fixing any possible problem you experienced or even not ever experienced and surely not fixing a rogue user that simply deletes random files from your data directory. Against that you should think about proper file permisssions, fo example. Application users should be able to create and modify files, but never to delete them, for example. And for shared access guarding a share is an admin task.
The only stable situations are a) there is no databse at all and you create it completely, or b) the datbase or databases exists with all necessary tables. and you rely on it. Any other state is not solved with a click of a button anyway. You can only think that way if you have a naive idea of a database and don't know how related data works, what constraints are, etc. A faulty situation needs the developer or an admin to look at it.
Chriss
- Status
Similar threads
- Question