Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How do I perform an SQL query in Delphi

Posted on 2002-03-25
7
Medium Priority
?
149 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 300 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

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…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
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

704 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