Solved

How do I perform an SQL query in Delphi

Posted on 2002-03-25
7
142 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

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

Suggested Solutions

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

839 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