Array of Strings and SQL Execute

Hello Vasco,

I encountered a similuar situation when I wanted to compare values from my database with values found online. Here is how I solved the problem :

First I create a TStringList :

LocalList := TStringList.Create;

Then I populate the list with the result of a query :

SQLQuery('SELECT asset_sku FROM asset WHERE id_vendor = 1 AND asset_is_reserved = 0',Results);

                        //-----> Now let's fill the localList with the results of the query
                            while not Results.Eof do
                                begin
                                    LocalList.add(Results.FieldByName('asset_sku').AsString);
                                    Results.Next;
                                end;
                            Results.Free;

(in your case this would be id's and not asset_sku)

Now the list is full of local references, I use the same technique to create a second one but with online data.

OnlineList := TStringList.Create;

and I fill it with another procedure.

Comparing both list was just a matter of checking the existence of an item from one list into another like this :

for i := 0 to OnlineList.Count - 1 do
     begin
          ...
          if localList.IndexOf(onlineList.Strings(i)) = -1 then
          ...
     end;

meaning : "if index of element i from OnlineList into LocalList is not found (-1)  then..."
If another value is returned, it means that the element is present in the list and the value is it's index.

Now, regarding your array question. You usually create an Array by splinting a TStingList.

Remember the

LocalList.add(Results.FieldByName('asset_sku').AsString);

command ?

We did not mention any separator for the list, the default one being a comma. So to get an array of strings out of the list, it's just a matter of splitting into after every comma like this :

LocalArray := SplitString(LocalList,',');

(declare your Array of String or Integer into the variable section, but you do not need ti initialize (create) it before using)

SIDE NOTE 1
If you already have a string with value separated by commas, you can load it directly into a TstringList like this :

OnlineList := TStringList.Create;
OnlineList.CommaText := sku_string;

SIDE NOTE 2
So, TstringList or Array ? That's up to you. I personally find it easier with TstingList to handle data in memory, to add or remove values and so on. All those things you can certainly do with an Array as well. But Array, especially multi-dimensional ones can get tricky, so I tend to stick to the following principle : KISS (keep it simple stupid).

Hope this helped a little

Cheers

Mathias

mathamandu, is results a string or an array?

Hello,

Sorry I missed you message.

The content of a TStringList is a string, with all the values separated by a comma

,

If you want an array, you need to split the TStringList with the correct command:

LocalArray := SplitString(LocalList,',');

Sending you an example is not really easy as I have no project in mind that could help you right now.

But if you want to transfer a table's content to another, why do you plan on using TStringLists or Arrays ? Why not direct SQL or SQLTransactions ?

Give me a little more detail on what you want to do and I'll try to find an answer for you

Cheers

Mathias

hey Mathias. just came here to thank you.
I figure it out how to do this , it was easier than i tought. the script is working fine now, i'm almost finished with my database! (Finally)