Board index » delphi » New Delphi (ex-Paradox) user DB question

New Delphi (ex-Paradox) user DB question

I am having problems getting my head around the way Delphi handles database
connections, as opposed to the way paradox does. Does anyone know how I may
achieve the following in Delphi 4? The code below is ObjectPAL code from
Paradox 8:

var
    sqlvar    sql
    sqlstr    string
    db        database
endvar

sqlstr="insert into :priv:tmatters select m.* from :mainalias:matters"
sqlvar.readfromstring(sqlstr)

db.open(":work")
sqlvar.executesql(db)
db.close()

I also can't seem to find any reference to the PRIV alias in Delphi, which I
used to use extensively. The reason I want to do this is that I don't want
to keep a constant connection to the database, and I treat all data views as
local, refreshing on a time basis. :mainalias: is an alias to SQL Server,
and we have found that keeping connections open is detrimental to
performance and the UI (scrollbars become disproportional etc).

Can anyone help me here, I'm getting a bit baffled.

Cheers in advance

Simon Keefe

 

Re:New Delphi (ex-Paradox) user DB question


I think this is what you want. It is from D3 help:

Specifying a temporary files location

If no value is specified for the PrivateDir property, the BDE automatically
uses the current directory at the time it is initialized. If your
application runs directly from a network file server, you can improve
application performance at run time by setting PrivateDir to a user's local
hard drive before opening the database.

Note: Do not set PrivateDir at design time if you intend to open the
database. Doing so generates a "Directory busy" error.

The following code changes the setting of the default session's PrivateDir
property to a user's C:\TEMP directory:

Session.PrivateDir := 'C:\TEMP';

Important: Do not set PrivateDir to a root directory on a drive. Alway
specify a subdirectory.

In D3 help, it was under Privatedirectory

Quote
Simon Keefe <newsgr...@keefey.freeserve.co.uk> wrote in message

news:7mvat1$fvj$1@news5.svr.pol.co.uk...
Quote
> I am having problems getting my head around the way Delphi handles
database
> connections, as opposed to the way paradox does. Does anyone know how I
may
> achieve the following in Delphi 4? The code below is ObjectPAL code from
> Paradox 8:

> var
>     sqlvar    sql
>     sqlstr    string
>     db        database
> endvar

> sqlstr="insert into :priv:tmatters select m.* from :mainalias:matters"
> sqlvar.readfromstring(sqlstr)

> db.open(":work")
> sqlvar.executesql(db)
> db.close()

> I also can't seem to find any reference to the PRIV alias in Delphi, which
I
> used to use extensively. The reason I want to do this is that I don't want
> to keep a constant connection to the database, and I treat all data views
as
> local, refreshing on a time basis. :mainalias: is an alias to SQL Server,
> and we have found that keeping connections open is detrimental to
> performance and the UI (scrollbars become disproportional etc).

> Can anyone help me here, I'm getting a bit baffled.

> Cheers in advance

> Simon Keefe

Re:New Delphi (ex-Paradox) user DB question


Simon:

Greetings! It's been a while since I've worked with ObjectPAL, but if I
understand your code, the following should do the same thing, assuming a TQuery
object named quExample. I haven't had a chance to run this code, but to the best
of my knowledge it should be close. BTW, :FirstTable and :SecondTable are
parameters passed to the query by the TQuery.ParamByName(TParam) assignment.

procedure {procedure name}
    begin
        with quExample do
            begin
                Close;
                Database := {BDE Database Alias};
                SQL := 'insert into :FirstTable select * from :SecondTable';
                ParamByName(FirstTable) := Database.Session.PrivateDIR +
'TblName1';
                // You'll need to define a string variable somewhere to hold the
mainalias
                ParamByName(SecondTable) := MainAlias + 'TblName2';
               ExecSQL;
            end;
    end;

Again, this was off the top of my head, so to speak. I hope that it helps.

Kevin

--------------------------------

Quote
Simon Keefe wrote:
> I am having problems getting my head around the way Delphi handles database
> connections, as opposed to the way paradox does. Does anyone know how I may
> achieve the following in Delphi 4? The code below is ObjectPAL code from
> Paradox 8:

> var
>     sqlvar    sql
>     sqlstr    string
>     db        database
> endvar

> sqlstr="insert into :priv:tmatters select m.* from :mainalias:matters"
> sqlvar.readfromstring(sqlstr)

> db.open(":work")
> sqlvar.executesql(db)
> db.close()

> I also can't seem to find any reference to the PRIV alias in Delphi, which I
> used to use extensively. The reason I want to do this is that I don't want
> to keep a constant connection to the database, and I treat all data views as
> local, refreshing on a time basis. :mainalias: is an alias to SQL Server,
> and we have found that keeping connections open is detrimental to
> performance and the UI (scrollbars become disproportional etc).

> Can anyone help me here, I'm getting a bit baffled.

> Cheers in advance

> Simon Keefe

Re:New Delphi (ex-Paradox) user DB question


This is to give you some ideas -- use at your own risk ;-) .  It is not the only
solution.  Furthermore, this example does not deal with runtime errors.

First lets deal with the :PRIV alias question:
One thing you can do is create an alias called PRIV in the idapi.cfg file.  Use
the BDE Administrator to add the alias.  By doing this, you can access the alias
directly via your components at design time.  However, you should set the
session's private directory in code before you open any tables at runtime.  Use
Session.PrivateDir, otherwise a default private directory will be used by the
application.  One place for this code is the Form's OnCreate event:

procedure TForm1.FormCreate(Sender: TObject);
begin
  Session.PrivateDir := 'C:\windows\temp';
end;

Next we deal with your first question:
From the component palette select and place each of the following components on
a Form:  1) a Query (Query1),  2) a BatchMove (BatchMove1), 3) a Table
(Table1),  4) a DataSource (DataSource1), 5) a DBGrid (DBGrid1), and lastly 6) a
Button (Button1).

Set the Query1 DatabaseName property to your "MainAlias" (I assume you have
created it in the idapi.cfg file), or you can type in the path to your data.

Next place your SQL statement in the Query1 SQL property, or create the
statements using the built-in SQL Builder (right-click on the SQL component and
select SQL Builder from the pop-up menu).

Now set the Table1 DatabaseName property to the :PRIV alias you created in the
idapi.cfg file, or just type in the private directory path.  Also, set Table1's
TableName property to "Result" (without the quotations).  This is what the new
answer table will be called.  You can name it something else if you like.

Connect DataSource1 to Table1: Set the DataSource1 DataSet property to Table1.
This will allow other components to access the contents of Table1.

Connect the DBGrid1 to DataSource1 so you can see the contents of Table1 at
runtime: Set the DBGrid1 DataSource property to DataSource1.

Set the following BatchMove1 properties:  Destination = Table1, Mode = batCopy,
and Source = Query1.

Lastly, place this code in your Button1's OnClick event:

procedure TForm1.Button1Click(Sender: TObject);
begin
  Query1.Open;
  BatchMove1.Execute;
  Table1.Open;
end;

Run the application and click on the button.

Note:  The reason I used a BatchMove component is so you can write the contents
of your Query to a physical table.  In this case, a table called "Result" will
be created in your :PRIV directory.  Otherwise the results of your query will
exist only in memory and will be released when the application terminates.

Erik.

Quote
Simon Keefe wrote:
> I am having problems getting my head around the way Delphi handles database
> connections, as opposed to the way paradox does. Does anyone know how I may
> achieve the following in Delphi 4? The code below is ObjectPAL code from
> Paradox 8:

> var
>     sqlvar    sql
>     sqlstr    string
>     db        database
> endvar

> sqlstr="insert into :priv:tmatters select m.* from :mainalias:matters"
> sqlvar.readfromstring(sqlstr)

> db.open(":work")
> sqlvar.executesql(db)
> db.close()

> I also can't seem to find any reference to the PRIV alias in Delphi, which I
> used to use extensively. The reason I want to do this is that I don't want
> to keep a constant connection to the database, and I treat all data views as
> local, refreshing on a time basis. :mainalias: is an alias to SQL Server,
> and we have found that keeping connections open is detrimental to
> performance and the UI (scrollbars become disproportional etc).

> Can anyone help me here, I'm getting a bit baffled.

> Cheers in advance

> Simon Keefe

Re:New Delphi (ex-Paradox) user DB question


Quote
>var
>    sqlvar    sql
>    sqlstr    string
>    db        database
>endvar

>sqlstr="insert into :priv:tmatters select m.* from :mainalias:matters"
>sqlvar.readfromstring(sqlstr)

>db.open(":work")
>sqlvar.executesql(db)
>db.close()

In Delphi you make user of components.  The Tquery component is used for running
SQL.
The Tquery has a databasename property that you set to the database where you
want the query to look for table that you don't specify a location for.

Paradox creates both Work and Priv aliases for you but Delphi doesn't do this.
In delphi you probably want to create an Alias or use a tdatabase to point to
your application directory.  A TdataBase is another component but you can think
of it as a temporary alias.
You can at runtime also set the databasename to a path specification.

In Delhi you would set the Tquey Database name to this Alias (or tDatabase)
instead of the way you set it to :work: in your Paradox code.
Delphi has a PrivateDir property that is the location where it creates temporary
tables.  This is a lot like Paradox's Private directory except Delphi sets it to
the directory your application starts in.  It is a good idea to set this to a
local directory in your code.
now assuming I had set the PrivateDir to a local directory I would write your
code like this

with Query1 do begin
      sql.add('insert into '+session.PrivateDir+'\tmatters ');
      sqlAdd(' select m.* from matters");
     ExecSQL//run the query
end;

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:New Delphi (ex-Paradox) user DB question


Thank you all very much for your detailed responses. There really does seem
to be many many ways of getting the same thing done with Delphi, which I
suppose is why it was so confusing!

Thanks

Simon Keefe

Re:New Delphi (ex-Paradox) user DB question


Simon:

Hello! After my post, I realized that I passed you some misinformation. The
TQuery.SQL property is not a string but list of strings. Don't know what I was
thinking at the time. Therefore, replace the line in the code in my previous post:

    SQL := 'insert .... etc.'

with the lines:

    SQL.Clear;       // to clear the list of strings
    SQL.Add('insert ...etc.);

Sorry for the misinformation.

Kevin

==================================

Quote
Kevin Meyers wrote:
> Simon:

> Greetings! It's been a while since I've worked with ObjectPAL, but if I
> understand your code, the following should do the same thing, assuming a TQuery
> object named quExample. I haven't had a chance to run this code, but to the best
> of my knowledge it should be close. BTW, :FirstTable and :SecondTable are
> parameters passed to the query by the TQuery.ParamByName(TParam) assignment.

> procedure {procedure name}
>     begin
>         with quExample do
>             begin
>                 Close;
>                 Database := {BDE Database Alias};
>                 SQL := 'insert into :FirstTable select * from :SecondTable';
>                 ParamByName(FirstTable) := Database.Session.PrivateDIR +
> 'TblName1';
>                 // You'll need to define a string variable somewhere to hold the
> mainalias
>                 ParamByName(SecondTable) := MainAlias + 'TblName2';
>                ExecSQL;
>             end;
>     end;

> Again, this was off the top of my head, so to speak. I hope that it helps.

> Kevin

> --------------------------------

> Simon Keefe wrote:

> > I am having problems getting my head around the way Delphi handles database
> > connections, as opposed to the way paradox does. Does anyone know how I may
> > achieve the following in Delphi 4? The code below is ObjectPAL code from
> > Paradox 8:

> > var
> >     sqlvar    sql
> >     sqlstr    string
> >     db        database
> > endvar

> > sqlstr="insert into :priv:tmatters select m.* from :mainalias:matters"
> > sqlvar.readfromstring(sqlstr)

> > db.open(":work")
> > sqlvar.executesql(db)
> > db.close()

> > I also can't seem to find any reference to the PRIV alias in Delphi, which I
> > used to use extensively. The reason I want to do this is that I don't want
> > to keep a constant connection to the database, and I treat all data views as
> > local, refreshing on a time basis. :mainalias: is an alias to SQL Server,
> > and we have found that keeping connections open is detrimental to
> > performance and the UI (scrollbars become disproportional etc).

> > Can anyone help me here, I'm getting a bit baffled.

> > Cheers in advance

> > Simon Keefe

Re:New Delphi (ex-Paradox) user DB question


That's okay, I worked it out from the endless stream of compilation errors!

Cheers

Simon Keefe

Quote
Kevin Meyers wrote in message <3794BD13.440CE...@epix.net>...
>Simon:

>Hello! After my post, I realized that I passed you some misinformation. The
>TQuery.SQL property is not a string but list of strings. Don't know what I
was
>thinking at the time. Therefore, replace the line in the code in my

previous post:
...

Other Threads