• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 156
  • Last Modified:

How do I perform an SQL query in Delphi

I have never used the TQuery component in Delphi, and I need to perform a simple SQL query on a database.

The filename is tableFile.


How do I run an SQL query  on the data in that file?


I know this will be really simple, but I have never done it, and consequently have no idea about how to go about doing it.

chmod101
0
chmod101
Asked:
chmod101
  • 4
  • 2
1 Solution
 
GrahamWhiteCommented:
Set the DatabaseName to the ALIAS or folder where the database is located. In the SQL property set the SQL statement. This can be very simple like 'SELECT * FROM tableFile.db' (get all records from all fields in the database). However, it can become more complex than that depending on what you want to do. The TQuery component can be used with parameterised queries (where the basic SQL is the same but the criteria may vary e.g first pass is salaries > 5000 and the second pass is salaries > 6000).

Once you have set the properties do something like

with TQuery do
begin
  Close;
  try
    Open;
  except
    // handle error here
  end;
  Close;
end;

If you want to set the SQL at run time

with TQuery do
begin
  Close;
  SQL.Clear;
  SQL.Add(sMySQLStatement)
  try
    Open;
  except
    // handle error here
  end;
  Close;
end;

There is a lot more you can do (see the Prepare, Unprepare and ExecSQL in the help file ) but this should get you going
0
 
shyampaliyathCommented:
u have to create an alias for the database in the BDE Engine. this alias should be assigned to the database property of the query component.
if suppose u have to execute the query as given below.
'SELECT * FROM TAB' can be done by 2 ways
1. write the query in sql property of the query component.
then on the button click u can write 'query1.open'
2. write the following code in the button click eevent
with query1 do
begin
   close
   sql.clear;
   sql.add('SELECT * FROM TAB');
   open;
end;

**note if suppose ur going to write the insert statement or
update statement use 'execsql' instead of 'open'.

Accessing the data inside the querycomp
=======================================
  showmessage(query1.fieldbyname('TNAME').asstring);

u can pass even parameters
==========================
with query1 do
begin
   close
   sql.clear;
   sql.add('SELECT * FROM EMP WHERE EMPNO=:XEMPNO');
   parambyname('XEMPNO').asstring:=trim(edit1.text);
   open;
end;

0
 
chmod101Author Commented:
alright, one other question...

After running the sql statement...where are the results stored?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
chmod101Author Commented:
One other thing...

In a table in my database, I have a field that has the name "Main Picture".  How do I make an SQL statement like the following work:

 Select * From table1.db where "Main Picture" = 'x'

How do I structure the name of the field after the 'Where' so that it recognizes it as a field name?
0
 
chmod101Author Commented:
I am rejecting this answer to make sure that others can post to this question.  Graham white, just post a comment again, and I will give you the points for the answer after I find an answer to my other comments.

I am not sure if the lock on a question still exists after an answer has been suggested, but I am doing this in case it is.
0
 
GrahamWhiteCommented:
The results from the database are stored in the dataset. I'm not at the computer at present so trying to remember of the top of my head.

You can use bracketing as follows:

SELECT * FROM TABLE1 WHERE (Main Picture = "X")
AND (Field = "y") AND (.....

the use of the double quotes (") is deliberate and also makes creating the statements much easier e.g.

const
  sSQL = "SELECT * FROM TABLE1 WHERE (Main Picture = "%s");
begin
  sSQL = Format(sSQL, ['Picture Criteria']);

My preference is not to use spaces within field names as it can cause problems, you could try using _ instead as it still makes the names quite readable e.g. Main_Picture.

OK, how to read fields.

Assign the data from the query to a variable (appropriately typed of course).

var
  iCount : integer;
begin
with qryPicture do
begin
  First;
  while not EOF do
  begin
    iCount := FieldByName('Main_Picture').AsInteger;
    Next;
  end; // of loop round SQL result set
  Close;
end; // of with statement

Hope this helps (and it roughly correct - like I say, no access to Delphi at present)

Graham
0
 
chmod101Author Commented:
GrahamWhite,

Thank you for your patience and your help.

chmod
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now