Solved

Delphi SQL query from a string list?

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

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 27

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

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!

Question has a verified solution.

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

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…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

679 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