Solved

Delphi SQL query from a string list?

Posted on 2014-09-19
5
760 Views
Last Modified: 2014-10-30
I have a query that needs to pull a list of parts IF those part numbers are found in another list/table. The problem is that the query pulling the parts list uses a direct ODBC connection for access and the other query is linked to a SQL table that shows the parts and whether or not they should be shown.

Here's my example:  FIRST I pull the blue parts into a string list...
  //PULL BLUE BOOK PIECES INTO LIST
  with qryBlue do begin
    Close;
    SQL.Clear;
    SQL.Add('SELECT PieceType FROM PieceType WHERE Book = ''BLUE'' ');
    Open;
    while not EOF do begin
      slBlue.Add(qryBlue.FieldByName('PieceType').AsString);
      Next;
      end;

Open in new window


Now here's where I'm having trouble... how can I query this list?  Here's the 2nd part of my search (search doesn't work of course, here's where I need help):
  if sFrameless <> '' then begin
    with qryParser do begin
      Close;
      SQL.Clear;
      SQL.Add('SELECT * FROM Piece WHERE PieceType IN ('''+slBlue+''' ');
      Open;
      end;
    end
  else begin
    with qryParser do begin
      Close;
      SQL.Clear;
      SQL.Add('SELECT * FROM Piece WHERE PieceType IN (('''+slBlue+''') AND (PieceType <> ''45'') AND (PieceType <> ''46'')) ');
      Open;
      end;
    end;

Open in new window


Thank you.
0
Comment
Question by:Bianca
5 Comments
 
LVL 11

Expert Comment

by:David Kroll
ID: 40333704
Try using slBlue.CommaText
0
 
LVL 26

Expert Comment

by:Sinisa Vuk
ID: 40333782
why not extend second and third sql to use first query - and you don't need first one at all:

...
Close;
      SQL.Clear;
      SQL.Add('SELECT * FROM Piece WHERE PieceType IN (SELECT PieceType FROM PieceType WHERE Book = ''BLUE'') ');
      Open;
      end;
...
Close;
      SQL.Clear;
      SQL.Add('SELECT * FROM Piece WHERE PieceType IN (SELECT PieceType FROM PieceType WHERE Book = ''BLUE'' AND (PieceType <> ''45'') AND (PieceType <> ''46'')) ');
      Open;
      end;
...

Open in new window


...and this ''BLUE'' can be parameter too.
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 40336341
use bind variables ! no ? why not ?
don't care about performance ?

SQL.Add('SELECT PieceType FROM PieceType WHERE Book = :BOOKCOLOR');
SQL.ParamByName('BOOKCOLOR').AsString := 'BLUE';

is this for making a user selection ? > user_selection_table
insert the selected items into a table and then match the selection from that table

in pseudo:
delete from user_sel where user_session_id = :user_session_id
:user_session_id = number of connection id in database

sql.text := 'INSERT INTO user_sel (user_session_id, value) values(:user_session_id, :value)';
paramByName('USER_SESSION_ID').AsInteger := ___NumberOfSession;
for i := 0 to items.count - 1 do 
begin
  paramByName('VALUE').AsString := items[I];
  ExecSQL;
end;

sql.text := 
  'SELECT pt.* from PIECE p, user_sel u '+
  'where u.user_session_id = :user_session_id '+
  '  and p.piecetype = u.value';
ParamByName('USER_SESSION_ID').AsInteger := __NumberOfSession

Open in new window

0
 

Author Comment

by:Bianca
ID: 40336716
Sinisa - Thank you for your response. I tried that at first but here's my problem...

Piece table is using qryParser (connected to MDB)
PieceType table is using qryPiece (connected to SQL)

I need to pull records from table.Piece IF the field.PieceType is in table.PieceTable.  What is the best way to do this?
0
 
LVL 26

Accepted Solution

by:
Sinisa Vuk earned 500 total points
ID: 40337587
Is it possible to import this table Piece (from mdb) into Sql server database? It is possible, but I want to know if you willing to..? If you do - then is my first solution a good way.
This is how to ... http://www.fmsinc.com/MicrosoftAccess/SQLServerUpsizing/importing/Import_Access_DB.asp

Other option is to link table in access side:
http://office.microsoft.com/en-001/access-help/link-to-sql-server-data-HA102809758.aspx

.. or in sql server side (which might be the best):
http://www.mssqltips.com/sqlservertip/1480/configure-microsoft-access-linked-tables-with-a-sql-server-database/

... and this way you can have only one sql statement too.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

867 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now