In the SQL world everything is done from a "script". All data is retrieved using "queries", and there is an instruction for everything, from creating a database to creating a table, an trigger, or dropping (deleting) a table/database/trigger. If you'd have done everything "by the book" (IB users guide), you would have known this because all database and all tables are created using "isql" (command line SQL interface to the database). Since you haven't used "isql" (and note: I don't recommend it, there are simpler ways to do things those days) you missed that part. The language used to manipulate database structures (tables and all) is called "Data Definition Language" - DDL for short. The structure of the database is called (or I'm calling it?) the "metadata", so DDL is SQL language for manipulating the "metadata".
Fire up your IbExpert, double-click on a table. Did you notice the DDL tab before? IbExpert is kind enough to show us the DDL for re-creating the given table, so we can work with IbExpert's GUI to easily create and modify tables, but if required, one can copy-paste the DDL somewhere and use it "as is". Next: Look in the "Tools" menu, there's a "Extract Metadata" command. Click on it, then check the "Extract All" option and hit the green "play" button to start the extract process. IbExpert will extract an script that may be used to re-create the whole database. This includes everything in the metadata (database itself, tables, triggers, stored procs - you can see the list in IbExpert, I don't need to copy it here). The extracted script may be run on a customer's computer to re-create the database, using an number of alternative methods: using iSql (command line), using IbExpert, using IbConsole. The component usites I've used with Delphi so far all included a way to directly run such an script. I never had the need to do this with .Net so I don't know how it's done, but chances are there's a way to do it. Just look for something that talks about scripts (RunScript, ExecuteScript, etc). Please note the DDL extract option is not specific to IbExpert, you'll find a similar command in any application designed to work with any SQL database. As a matter of fact, there's a *very* good chance the command line SQL interpreter for any real-life SQL server is called "isql", just like in Firebird's / Interbase's case. For simple databases, the DDL script extracted using IbExpert might work on other databases "as-is" (because this data-manipulation language is standardized). If it doesn't work, chances are it can be easily changed so it will work. Back to your question: You've got a number of options for allowing the end-user to create a new database. (a) Keep a copy of the DDL-script to re-create the database with your application. You'll need to ask the user for the database name (not folder, Firebird databases are stored in a single file), change a few lines in the script (the line that says the name of the database, the line that gives the user name/password), run the script and that's it, you've got a brand new database. This is options is probably the most portable, as it allows you to work with different versions of Interbase/Firebird and it allows easy changing of the Database itself (maybe you want to move to MsSQL - I don't). (b) Keep a "backed-up" copy of your database with the application. When the user wants to create a new database, you simply restore the database at the requested location and that's it, you're done. This is a bit more flexible then (a) as it allows you to include both "metadata" and "data" in the script. Well... method (a) also allows you to put data in the script but the script will instantly become huge (as the data will be included as "INSERT" statements). (c) Keep a compressed copy of the full database with the application. When the user wants a new database, you uncompress a single file. This is the least flexible solution as it locks you into a single Firebird version, but hey, it's soooo easy! And if most of your users will be using the "Embeded" version of Firebird, they're locked into a single version of Firebird anyway. This is the method I'm using! As you get more used to this, you might start looking into other things, like on-the-fly table creation. Just look at the DDL for a single table (as you can see it in IbExpert) and remember you can copy-paste that script into Delphi and run it: (FbCommand.ExecuteNonQuery). You can also create different tables for different needs, simply by building up the script and then running it. -- Cosmin Prund Robert Meek wrote: > I had been using Nexus for the last three years, doing all direct > table access, no SQL, for commercial or pro-bono work, and had started > trying to learn SQL with it after picking up a few recommended books on the > subject. Unfortunately there were simply too many bugs and other > difficulties with the system that I was not able to work out. And when > you're trying to learn something new knowing whether a problem you are > encountering is caused by your own errors or that of the system itself is > important to know! > I've already created a few test tables using IBExperts and have > written my code to setup, create/open, close a firebird database in a > net-based application, but so far I haven't used any SQL to manipulate it. > Let me ask a question here that may sound a little dumb. But as I > never had to worry about such things when using direct table access methods > this is quite new to me and I haven't found a definitive answer anywhere. > Am I correct in assuming that if I want my application to allow a > user to create a new database directory and then a particular set of tables > I can create a schema of my design database and use it as the model for the > new creation of such tables in the working application? Using table > components I would simply store the definitions and then create each table > if it didn't already exist. But with Firebird there are no table components > to speak of, so if I create my tables during design, then save out a schema, > is that basically the same thing or is there more to it? > It's odd but unlike when learning Delphi and not having any > programming experience before hand, basic questions like these were quite > easy to find out about, but in database documentation and tutorials they all > seem to skip over this kind of stuff as if you're already supposed to know > it! You end up wasting a lot of time on simple concepts that shouldn't be a > problem but are for the lack of one simple sentence telling you exactly what > a schema is and what it is used for! > _______________________________________________ Delphi-DB mailing list Delphi-DB@elists.org http://www.elists.org/mailman/listinfo/delphi-db
