At 10:38 PM 20-05-01 -0700, Chuck Belanger wrote:

>Hi, again:
>
>I found out some things on my own. Thanks, Jason for the source code to
>IBWISQL!
>
>Followed the trail through the Browse and found that there are system tables,
>specifically RDB$Relations which list the tables in a database.
>
>So, at least I can check if a table exists before adding it, thus avoid
>dealing with error messages. Right now I see that adding a temporary table to
>the database, fill it with the CheckBoxList information (selected patients)
>and doing a select against that to pull out all the records from the
>variously related tables to move to archive or delete should work.
>
>But, I'm still thinking there has got to be an easier way! This seems so
>convoluted. Is adding temporary tables to a GDB a normal thing to do?

No: and it's not a good idea, either. This practice belongs to SQLServer, not to a proper client/server database! <g>

If you *must* put the results of your selection into a table (why????) then create a table for it that has a session identifier of some sort. Get the session ID from a generator and bring it across to your application; then store the rows you want (with a unique primary key, also generated) for the duration of your user's session. Have the application delete the rows when it has finished with them.

It is very much against the client/server grain to poke temp rows into a table (or create temp tables) to store selection criteria. Creating a temp table is especially horrible: how would one user's application be able to tell whether another's was attempting to create the table?

If you must go this route, insert rows into a pre-created table then make sure you isolate the rows involved in both the temp storage location AND the original dataset inside a single transaction.

>Anybody
>with a better way to use CheckBoxList information to use in a Select?

I'm assuming you are feeding a description column value from the selection dataset into the Items[] property of the listbox.

In your FormCreate, create a TStringlist. When you load the items for the CheckListBox, simultaneously load the Strings[] of this stringlist with the primary key of the selection table, so that your Strings[] index matches that of the listbox ItemIndex one-for-one. Then, when you come to process the checked items, in a loop, you can access the stringlist's Strings[n] with the ItemIndex and get the primary key value(s) you want.

I don't know what you want to do with the rows once they are selected.

Define some DML in a TIB_DSQL for the deletion you want to do and parameterise it, e.g. 'DELETE FROM MyTable where PK = :PK' or you might want to call a stored procedure to do your archiving, that accepts your primary key as input, e.g. 'EXECUTE PROCEDURE ArchivePatient(:PK)'.

Here's an idea of how you might go about it:

interface
...
slSelections: TStringlist;
...

In your FormCreate:
...
slSelections := TStringlist.create;
....


In your loading procedure:
...
begin
...
slSelections.Clear;
CheckListBox1.Clear;
with ib_Query1, CheckListBox1, slSelections do
begin
if not BOF then First;
while not EOF do
begin
Items.Add(FieldByName('Description').AsString);
Strings.Add(IntToStr(FieldByName('PK').AsInteger));
end;
end;
...
end;

When you want to process the user's selections:

var
ii: Integer;
...
begin
...
with ib_DSQL1, slSelections do
for ii := 0 to CheckListBox.Items.Count - 1 do
if CheckListBox.Items.Checked then
begin
ParamByName('PK').AsInteger := StrToInt(Strings[CheckListBox.Items[ii]]);
Execute;
end;
...
end;

You can take explicit control of the transaction and make all of this archiving occur inside a single transaction. After the Commit, call Refresh on the IB_Query if you need to show the results of the work.

There are 101 ways to kill this cat, though.
All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________