🚀 go-pugleaf

"Simon Elliott" <simon@nospam.demon.co.uk> wrote in message
news:rUI$wAAUogz$IwA9@courtlands.demon.co.uk...
> Ted Byers <r.ted.byers@sympatico.ca> writes
> [snip]
Thanks for the code where you're creating the parmeterized queries.

> >In my case, it MUST be done in code because the user has the
> >liberty to create as many database files as he likes, and I have to
create
> >the correct structure for each database he creates.  (My application
comes
> >with one database that is read only, and can create two kinds of database
> >for user data, one of which is unique and the other can be represented in
as
> >many files as the user wishes, which will depend on how the user
conceives
> >of relationships in his data - imagine a folder full of such files as
> >representing a bookshelf full of recipe books).
>
> How do you create your new databases? Are you doing
>
> Catalog=Variant::CreateObject("ADOX.Catalog");
> Table=Variant::CreateObject("ADOX.Table");
>
Yes, but it is a bit more than that (and I'm not using ADOX.Table).  In the
event handler for the "New" menu item (used to create a new recipe
database), I begin with:

void __fastcall TCookbookMainForm::New1Click(TObject *Sender)
{
  if(OpenDialog->Execute()) {
    if(FileExists(OpenDialog->FileName)) {
      ShowMessage("File already exists and will be recreated");
      DeleteFile(OpenDialog->FileName);
    }
    ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
    ConnectionString += OpenDialog->FileName;
    RecipeCatalog=Variant::CreateObject("ADOX.Catalog");
    RecipeCatalog.Exec(Function("Create") << ConnectionString);
    Connection=RecipeCatalog.Exec(PropertyGet("ActiveConnection"));
    AnsiString SQLstatement1("CREATE TABLE RecipeNames (RecipeID COUNTER(1,
1) NOT NULL CONSTRAINT PRIMKEY PRIMARY KEY, RecipeNAME VARCHAR(100) WITH
COMPRESSION NOT NULL UNIQUE)");
// a bunch more SQL statements to create all the tables the application
needs
    Connection.Exec(Function("Execute") << SQLstatement1);

ConnectionString, RecipeCatalog, and Connection are all data members of the
form class, with ConnectionString being an AnsiString and the others being
Variants.

The corresponding code for merely opening an existing recipe database is:

  if(OpenDialog->Execute()) {
    if(!FileExists(OpenDialog->FileName)) {
      ShowMessage("File does not exist");
      return;
    }
    //Catalog.Exec(PropertySet("ActiveConnection") <<
                //("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+));
    Variant RecipeCatalogTMP=Variant::CreateObject("ADOX.Catalog");
    AnsiString tmpCS("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=");
    tmpCS += OpenDialog->FileName;
    RecipeCatalogTMP.Exec(PropertySet("ActiveConnection") << tmpCS);

But the key here is the statement "RecipeCatalog.Exec(Function("Create") <<
ConnectionString);" when creating a new database while using the statement
"RecipeCatalogTMP.Exec(PropertySet("ActiveConnection") << tmpCS);" when
merely opening an existing one.  And, of course, when creating a new
database, I must (or prefer to) use a series of SQL statements to create the
entire structure of the database, from tables and indices to stored
procedures (when I find a need for these - I am beginnng to wonder if I may
find a performance improvement for one of my functions that has to do a
large numer of lookups from one of my tables if I create a stored procedure
or parameterized query for it).

It seems to me that if I can do everything with the database using a
catalog, a connection, or a record set, submitting the tasks to the database
as SQL statements through one of these three kinds of objects, I should
become less vulnerable to changes made in ADO/ADOX, and it should become
easier to adapt my code to other means of connecting to databases (such as
via SQLAPI++).  Plus I feel a need to learn much more about SQL.  I try to
make my code as independant of proprietary stuff as possible (witness the
controversy that has raged in the CBX and cppbuilder nontechnical newsgroups
 for the past few months regarding FUD about the fate of VCL).  I figure
that my code will have a longer usable life if I use the proprietary stuff
only when absolutely necessary (e.g. to quickly develop a given application)
and try to use languages for which there is an ANSI/ISO standard whenever
possible.  In this context, for example, I don't see a significant advantage
in using "Table=Variant::CreateObject("ADOX.Table");" when I can use a
simple SQL statement instead.  Similarly, I don't see an advantage to using
"TADOQuery".  I am not sure I believe that using it will reduce the amount
of code I will have to write.  In contrast, using the VCL forms and edit
boxes and comboboxes, &c., does provide a significant advantage since I can
get a functional form in a matter of hours where, if I had to develop the
same thing using the WinAPI or even MFC, it would take me days, weeks or
even months (depending on the complexity of what I was doing).  If I am not
mistaken, I can execute any SQL statement, that doesn't return a record set,
using a connection object, and a recordset object otherwise.  And this
requires only to write the SQL statements and to create the catalog and
connection objects (and record set object when needed).

Cheers,

Ted