RealDatabase SQL: concat and NOT LIKE

Databases
1

Hi,
1.) In a RealSQLDatabase I want to add a string to a database string-field. I tried:
DB_Inst.SQLExecute("UPDATE tableA SET strFieldName = concat(strFieldName ,‘Hallo’) ")
DB_Inst.SQLExecute("UPDATE tableA SET strFieldName = strFieldName || ‘Hallo’ ")
What is the correct syntax for concatination in RealSQLDatabase?

2.) In the same RealSQLDatabase I tried to filter a RecordSet with NOT LIKE. I tried:
rs = DB_Inst.SQLSelect(“SELECT Name, Kurse FROM KursBelegung WHERE Name = '” + tmpName + “’ " + _
" WHERE Kurse NOT LIKE '” + tmpFormulierung + "’ ")
with tmpFormulierung = “%;” + tmpKursBez + “;%”
What is the correct syntax for filtering NOT LIKE in RealSQLDatabase?
Thanks, Christian Hahn.

2

Oh, sorry, in 2.) I use WHERE twice. Better:
rs = DB_Inst.SQLSelect(“SELECT Name, Kurse FROM KursBelegung WHERE Name = '” + tmpName + “’ " + _
" AND Kurse NOT LIKE '” + tmpFormulierung + "’ ")
with tmpFormulierung = “%;” + tmpKursBez + “;%”
And that works! Thank you.
Christian Hahn.

3

Visit http://www.sqlite.org/lang_expr.html to read about expressions in SQLite. They cover|| and LIKE with descriptions of how they can be used for concatenation and pattern matching.

4

Are the SEMICOLONS part of your value?
By that I mean are you looking for something "like

;ABCD;

or are you looking for something like

ABCD

If the second, then remove the ; from you code

tmpFormulierung = "%" + tmpKursBez + "%"
5

Thank you, Frederick, now I can be sure that my two-pipe-syntax is correct in RealDatabase aswell. I have still to seach another incorrectness in my syntax.

for Dave: Thank you for your notice. Yes, I need the semikolons as a seperatrion between certain parts of the string

Christian Hahn.

6

tmpFormulierung = “’%;” + tmpKursBez + “;%’”
Sinqle quote before the first % and after the last %

7

So I did. Didn’t I?
NOT LIKE ‘" + tmpFormulierung + "’ ")
tmpFormulierung = “%;” + tmpKursBez + “;%”
The textsize in this forum is rather small.
Christian Hahn.

8

Ok
Maybe tmpKursBez contains a not escaped ’ ?
Try to put your select sql command as string
dim sql_test as string=“SELECT Name, Kurse FROM KursBelegung WHERE Name = '” + tmpName + “’ " + _
" AND Kurse NOT LIKE '” + tmpFormulierung + "’ "
rs = DB_Inst.SQLSelect(sql_test)

and post the sql_test contents

9

Oh yes, that’s a good idea and very convenient.
Thanks, Christian Hahn.