Solved

How do I perform an SQL query in Delphi

Posted on 2002-03-25
7
138 Views
Last Modified: 2010-04-04
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
Comment
Question by:chmod101
  • 4
  • 2
7 Comments
 

Expert Comment

by:GrahamWhite
ID: 6894696
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
 
LVL 1

Expert Comment

by:shyampaliyath
ID: 6896110
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
 

Author Comment

by:chmod101
ID: 6896499
alright, one other question...

After running the sql statement...where are the results stored?
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:chmod101
ID: 6896846
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
 

Author Comment

by:chmod101
ID: 6897157
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
 

Accepted Solution

by:
GrahamWhite earned 75 total points
ID: 6897217
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
 

Author Comment

by:chmod101
ID: 6899358
GrahamWhite,

Thank you for your patience and your help.

chmod
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question