Solved

How do I perform an SQL query in Delphi

Posted on 2002-03-25
7
147 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses
Course of the Month4 days, 9 hours left to enroll

635 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