Solved

Delphi SQL query from a string list?

Posted on 2014-09-19
5
811 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
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…

777 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