Sqlite problem with purebasic

[loulou2522]

Who can help me get around this SQL instruction problem in Purebasic?
The purpose of this instruction is to compare two files in a “ZRUM” area and display the records from file one that do not have a match in the common RUM area of file two.
Here is the instruction

Code: Select all

   DatabaseQuery(openingDatabase::opening, ‘Select ZRUM, ZIBAN, ZNOM FROM MANDAT Where  ZRUM  Not IN (Select DISTINCT(RUMDBT) from CLIENT)  and ZIBAN  in (SELECT DISTINCT IBANDBT From client)  and ZNOM  in (SELECT DISTINCT NOMDBT From client)’)

This instruction returns the correct result using an SQLite editor but returns an empty record with PureBasic.

ISorry but i can't transmit file because it's confidential files

[miso]

I can't say I know the answer, but maybe it's just a ";" if its sqlite.

Can you try this?

Code: Select all

DatabaseQuery(openingDatabase::opening, "SELECT zrum, ziban, znom FROM mandat WHERE zrum NOT IN (SELECT DISTINCT(RUMDBT) FROM client) AND ziban IN (SELECT DISTINCT IBANDBT FROM client) AND znom  IN (SELECT DISTINCT NOMDBT FROM client);")

[loulou2522]

thanks miso ,
problem is resolved , it's work perfectly

[miso]

No problem, your welcome.

[ebs]

I'm glad that miso's solution worked, but I don't think it's because of the semicolon at the end of the SQL statement.
I use SQLite extensively without the trailing semicolon and it works fine.

I think the reason miso's code worked is that he used double quotes (") around the SQL statement, which is necessary for a string in PureBasic. Your code used single quotes (') around the string, which I think should produce an error.

What happens if you just change the quote characters?

[Mindphazer]

@ebs, you're probably right !

[loulou2522]

I have another problem because even if the instruction works fine, I'm stuck because I also need to retrieve the value of rumdbt in the client file in the same instruction, and that's where I'm lost.

[skywalk]

Use a db browser to verify your SQL syntax.
I use db browser for sqlite.
You can attach the 2nd db file in a separate query and then refer to its tables.

[loulou2522]

HI skywalk
Thanks for your answers? I am not familiar with attachdabase. Can you give me anexample
Thanks in advance