sql query syntax problem TQuery 1

[safra]

Hi,

I have 2 tables and would like to return the name of an agent from table 2 by its ID in table 1. In php/MySql I would do something like this:

SELECT table2.MyAgent FROM table1,table2 where table1.MyAgent = table2.ID

However in Delphi using a TQuery I have problems with the syntax as the table names are identified by their filename.

I tried:

SELECT table2.db.MyAgent FROM table1.db,table2.db where table1.db.MyAgent = table2.db.ID

But no results.

Is this possible anyway or should I use an alias (which I am trying to avoid)?

Thanks!
Raoul

 

[VintageWine]

I suspect that you don't need the ".db" in the table names.

If there is a problem with the table names I would expect the query to error, rather than return no results.

In the IDE connect a TTable to your TDatabase and click the object inspector drop down for the table property. This will give a list of the tables as that the BDE has found, with names in the format you need to use in your queries.

 

[svanels]

I suspect you have a conflict of types, you try to compare a name with an ID number.
You could use a look-up field (see the FAQ area), or use 2 queries if you want to make the queries updatable


Would be something like:

Query1

select MyAgentID, MyAgentAdress from table1.db

//table1 must be the name of the physical table


Query2

select MyagentName from table2.db where MyAgentId =: MyagentID


:MyAgentID is a parameter (value) that must be passed to the second query

This we do by connecting the Datasource propertie of Query2 to the dataset of Query1.

Every time Query1 scrolls or is updated the second Query is executed thus retrieving the name.

Regards




Steven van Els
SAvanEls@cq-link.sr

 

[towerbase]

Why are you trying to avoid using the alias feature of the BDE?

However, I think you can achieve what you want by doing something along the lines of the following. I am creating the TQuery at run time to show how little is required but you could create it at design table.

const
 DataPath = 'c:\data\folder';  // or whatever
var
 Query: TQuery;
begin
 Query := TQuery.Create(nil);
 try
  Query.DatabaseName := DataPath;
  Query.SQL.Add ('SELECT t1.MyAgent');
  Query.SQL.Add (' FROM table1 t1, table2 t2');
  Query.SQL.Add (' WHERE t1.MyAgent=t2.Id');
  Query.Open;
// Your code to process the result set
 finally
  Query.Free;
 end;

Note the space before FROM and WHERE. I've also split the SQL command across three lines but that is a matter of taste.

If the TQuery was dropped on your form at design time (with the Name property set to Query and the DatabaseName property set to the value of DataPath) the code would be reduced to something like:

 Query.Close;
 Query.SQL.Clear;
 Query.SQL.Add ('SELECT t1.MyAgent');
 Query.SQL.Add (' FROM table1 t1, table2 t2');
 Query.SQL.Add (' WHERE t1.MyAgent=t2.Id');
 Query.Open;
// Your code to process the result set

Andrew

 

[safra]

Thanks All!

I didn't realise it is not necessary to add .db to the table name, it is working now.

Why are you trying to avoid using the alias feature of the BDE?

I am a beginner and are not sure yet how to deal with aliases!

For this little thing I aonly have a TQuery and dataset on the form and it is working fine.

regards,
Raoul

 

[svanels]

You are only working with 1 table/query. In database applications that is very rear. Probably you would have at least 3 physical tables to deal with.

If you don't work with an alias (which identify the location of the database or tables), for every query and table you have to hardcode the physical location (path, computer etc..)in your tquery or ttable.

Imagine the work when your application has 50+ queries

Steven van Els
SAvanEls@cq-link.sr

 

[safra]

Yes, I realise using aliases is the next step. This is only a test project to see how Delphi handles sql queries.

Probably it is not that difficult to use aliases but at this point I am not sure where to start with this and still haven't really looked at it.

 

[towerbase]

Aliases are worthwhile and easy to use.

The idea of an Alias is to give a name to a directory that will contain your database tables (indexes and so on). You could consider the Alias to be the name of your database. This is particularly useful for DBMS such as dBASE and Paradox where each table and index is contained in its own file.

If you use an Alias, your program only has to refer to the Alias name and not the physical location of the files.

Life being what it is, you will find there will be occasions when you need to change the location of your database. This maybe because you want to run a test version of the data or because the hard drive containing the database has got full.

As Steven has already pointed out, changing the locations of 50+ tables is a lot of work. It is also error prone. Wouldn't it be so much easier to change only one path? This is what an Alias gives you. You simply change the directory path associated with the Alias.

How is this done? Start up BDEADMIN.EXE. This is the Borland Database Administration program.

Click on the Databases tab.

Ctrl+N

Click the OK button

Type in the Alias Name. For example, Movies

Click on the PATH field in the right hand window

Enter the path of the directory containing your Movies database. For example, C:\Data\Movies

Click on Object | Exit

Click on Yes to save the updated file.

Restart Delphi (to get the latest version of the Alias file) and in your Delphi application you simply select each TQuery and TTable that access the Movies database and set the DatabaseName property to Movies. You will find that the list of available aliases is available in the property inspector.

That's all there is to it. If you ever need to get your application to refer to the database in a different location you simply run BDEADMIN.EXE and amend the path associated with Movies.

It really is worthwhile doing and it's not difficult.

Andrew

 

[safra]

Thanks a lot for that explanation Andrew!

My first thought about using databases in Delphi was: what if you develop an app that is used by third parties? As you say, you need to use the BDEAdmin to create aliases and change them. Don't you want to avoid that others have to create aliases etc. first before they can run the program? Or can you compile a project with all of this included?

Raoul

 

[svanels]

Yes with installshield express that comes with Delphi enterprise, other install programs also do this Job

Steven van Els
SAvanEls@cq-link.sr

 

[DaZZleD]

when you first run your program you can make the user go through a one step at a time config. therefore you can make him browse to the db's you need and create aliases depending on his choice. take a look at TSession (and especially at AddAlias, AddStandardAlias, DeleteAlias and IsAlias) to see more about how to create aliases at run time.

 

[safra]

I think I would prefer to have the installer taking care of that? I have never seen a program where you as the user have to setup database locations first. is this used a lot? I will look at it anyway to find out how this works. Thank a lot both of you.