Hi,
I'll try to explain:
1.) "word 1 of line 1 of field fcritex"
We know it's 1 number that we expect as argument. Right?
What could happen if we'd allow longer strings? Well, there's
the immortal XKCD example ;-)
Only this wouldn't work in Livecode, you cannot use 2 commands in 1 string.
But you could enter this into field fcritex:
The SQL statement resulting would be:
Code: Select all
SELECT * FROM users WHERE userID LIKE '1' OR id LIKE '%';
and, assumed there's a row "id", return all records.
The "of line 1" part is to make sure you'd not accidentally get CRs from the field - it's not really needed. "Word 1 of field ..." would be enough already, it returns everything in the field until the first space char.
But this would also accept "empty" (word 1 of empty = empty), and empty would return all records, again:
Code: Select all
SELECT * FROM users WHERE userID LIKE '%';
So we test if our result really is a number, and this makes sure we query for what we actually intend to do.
Experience shows that it's crucial to "sanitize" all & any direct user input - even the most agreeable, guileless, common user is easily able to blow up your carefully crafted code with sophistically malicious input, if given a chance. And if it's only copying a cell from Excel & pasting it - the obvious reason for the CR problem in the OP.
2.) "' " " '" :)
It's actually quite simple. When creating SQL statements to use with LC it's common that you want a variable somewhere in your code. A quick way to insert those is to have a certain "clip" handy, to copy:
Then you write your SQL statement with a placeholder:
Code: Select all
SELECT * FROM users WHERE userID LIKE '99%';
Now select the placeholder "99", paste, and you have:
Code: Select all
SELECT * FROM users WHERE userID LIKE '" & MyX & "%';
Change "myX" to the desired variable name, Bingo.
Klaus' approach is a bit different. He uses quoting functions to make sure we have correct quoting pairs. So, since we want our LIKE param as a string (which needs single quotes; may myVar be 99):
Code: Select all
myVar & "%" --> 99% = anything that starts with 99
he'd use:
and since we need to append this to:
Code: Select all
"SELECT * FROM users WHERE userID LIKE "
he'd actually write:
So:
Code: Select all
put "SELECT * FROM users WHERE userID LIKE " & q(q2(myVar & "%")) into StrSQL
results in:
Code: Select all
SELECT * FROM users WHERE userID LIKE '99%'
Since we can omit the trailing semicolon this is a working SQL statement.
Guess the confusion in your last post is mainly about the mixing up of:
- the string concatenation in LC (which uses double quotes) and
- the required string quoting in SQL (that uses single quotes)
Adding to the confusion can be table and field names in your database that are reserved words (date, text, order ...) - such MUST be quoted, too! Best use "back tick", "grave", "`" for this, it works in most SQL dialects.
Advanced SQL statements alone can be quite a mess of brackets, single quotes and back ticks.
Concatenating such from LC strings, using variables, greatly adds to the chaos.
The result can easily be close to incomprehensible.
This is where "SQL Yoga" (topic of this forum) comes to aid - it tries to help here. If this isn't enough, you may try the way I do it (and I do this for a living):
- I create my desired SQL statement in a SQL manager (here: SQLite manager, HeidiSQL, MS Access) - using real variables. Until the query works flawlessly, gives the desired results in an acceptable time, in the correct order. Then I copy the raw SQL.
- This is pasted into a good text editor (here: EditPad pro). I use premade "clips" & search/replace settings to replace my placeholders with variable names, adjust line lengths & add the "decoration" for LC ("quote" at each line start, "quote, space, &, space, backslash" at each line end but the last).
- This then is pasted into LC between "put " & " into StrSQL". A tab formats it, done.
Always works (if I didn't mess up ...), even with most voluminous & cryptic statements. ;-)
Have fun!