C# Array as part of MySQL Select Statement?

Question

Ok heres my scenario - i want to query a local db and store the results of the GUID select query into an array.

i would then like to query the remote server to return a result set which show basically any new records

This is the query but obviously this only works on the same server.

SELECT  *
FROM    remotetable remote
WHERE   NOT EXISTS
        (
        SELECT  1
        FROM    localtable local
        WHERE   local.guid = remote.guid
        )

and insert the results of this query into my local db to consolidate it.

so it would be something like

ArrayList myArrayList = new ArrayList();
OdbcConnection local = new OdbcConnection("DSN=local");
local.Open();
OdbcCommand guidSelect = new OdbcCommand("SELECT GUID from localtable",local);
OdbcDataReader DbReader = guidSelect.ExecuteReader();
while (DbReader.Read())
{
String guid = (string)DbReader[0];
myArrayList.Add(guid);
}
local.Close();
OdbcConnection local = new OdbcConnection("DSN=remote");
"SELECT * FROM remotetable remote WHERE NOT EXISTS (SELECT  1 FROM"+ myArrayList() +" local       WHERE   local.guid = remote.guid)

obviously this will not work as is but it should give a general overview what id like to accomplish

thanks in advance :)

Try building the query programmatically by adding odbc parameters. Syntax example: stackoverflow.com/questions/5759845/… — Hans Z
– Hans Z, Commented Jun 11, 2012 at 13:44

Malcolm O'Hare · Accepted Answer · 2012-06-11 13:57:46Z

2

The only way I know how to do this is to actually pass in a table of Guids like this:

string guids = "''" + string.Join("'',''", myArrayList) + "''"; 
string query = string.format("SELECT * FROM remotetable WHERE NOT id IN ({0})", guids);

In my opinion if you are trying to track for new records, you should just add a sequential column and then just grab everything higher than the MAX of that column on the local side.

answered Jun 11, 2012 at 13:57

Malcolm O'Hare

5,0393 gold badges36 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JazziJeff Over a year ago

ok thanks for the help - it is actually cleaner to add the sequential column, dont know why i didnt think of this instead. Problem im now seeing is that for some reason duplicates are being inserted into the local table even though they dont exist on they cloud DB

Malcolm O'Hare Over a year ago

without inspecting all your code that would probably be difficult to debug

JazziJeff Over a year ago

ah it was unrelated, basically i had a timer set up that was restarting and executing the code again before the other pass had completed - hence the duplicates, now resolved thanks again

Collectives™ on Stack Overflow

C# Array as part of MySQL Select Statement?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related