SQLite2: parameters in SQL()

uncategorized
1

Hi,

I am trying to translate this PROP:SQL to the new SQL().

month_limit{PROP:SQL} = 'insert into month_limit values(''' & rptyear & '-' & format(month,@n02) & '-01' & ''')'

This version:

SQL(month_limit,'insert into month_limit values(<34>' + ?1 + '-' + ?2 + '-01' & '<34>);',rptyear,mform)

gives me Unknown identifier for both ?1 and ?2, where mform = format(month,@n02)

What am I doing wrong?

Jon

2

I’m not at my pc, so i cant test, but id say there are two things in play here. One, or both, are likely the culprit.

A) the quotes <34> are unnessary. String values dont need quotes.

B) youre doing server-side string concatenation. Which is unnecessary (and slow). Simply format the whole date client side as a single string. So then you have a call with just 1 parameter, no quotes.

3

Thanks Bruce,

I have tried what I think you mean, but not successfully. And interestingly, it looks like your example for SQL() does in fact include server-side string concatenation. Certainly difficult to tell from those examples that binding a string in the SQL doesn’t require quotes. Anyway…

Here is my code:

	mform = rptyear & '-' & format(month,@n02) & '-01'
	log(month_limit,'mform is ' & mform)
	SQL(month_limit,'delete from month_limit;')	
	SQL(month_limit,'insert into month_limit values( + ?1 + );',mform)

And here is the log output:

[SQLite2][F][3683][051062S][JonWaterhouse][18640][2][10/10/25][11:25:02][][.\email_tracking.sqlite][month_limit][66]mform is 2022-01-01
[SQLite2][F][3683][051062S][JonWaterhouse][18640][2][10/10/25][11:25:02][][.\email_tracking.sqlite][month_limit][66][CLAR]GETPROPERTY(31989 PROP:Object,0)
[SQLite2][F][3683][051062S][JonWaterhouse][18640][2][10/10/25][11:25:02][][.\email_tracking.sqlite][month_limit][66][CLAR]SQL(delete from month_limit;)
[SQLite2][F][3683][051062S][JonWaterhouse][18640][2][10/10/25][11:25:02][][.\email_tracking.sqlite][month_limit][66][SQL ]delete from month_limit;
[SQLite2][F][3683][051062S][JonWaterhouse][18640][2][10/10/25][11:25:02][][.\email_tracking.sqlite][month_limit][66][SQL ]0 rows, 0 columns in result set
[SQLite2][F][3683][051062S][JonWaterhouse][18640][2][10/10/25][11:25:02][][.\email_tracking.sqlite][month_limit][66][CLAR]GETPROPERTY(31989 PROP:Object,0)
[SQLite2][F][3683][051062S][JonWaterhouse][18640][2][10/10/25][11:25:02][][.\email_tracking.sqlite][month_limit][66][CLAR]SQL(insert into month_limit values( + ?1 + );)
[SQLite2][F][3683][051062S][JonWaterhouse][18640][2][10/10/25][11:25:02][][.\email_tracking.sqlite][month_limit][66][CLAR]SETERROR :90 File System Error
[SQLite2][F][3683][051062S][JonWaterhouse][18640][2][10/10/25][11:25:02][][.\email_tracking.sqlite][month_limit][66][CLAR]SETFILEERROR :1 [p040]SQLite Error.[month_limit]

Does that help pin down what I am doing wrong? The original error is gone, unsurprisingly, because the ?1 is now just “in a string”. But I would have expected to see some indication that mform is being bound as part of sending the statement in the log file.

Jon

4

Hi Jon,

It’s been a while since I last played with the CapeSoft drivers, so take this as a bit of an educated guess — but I think Bruce was pointing out that you don’t need the + ?1 + part at all.

That + pattern only applies in cases where you’re doing something like a LIKE '%'+?1+'%' (concatenating wildcards). Inside a VALUES() clause, it just produces invalid SQL.

You can let the driver handle all the quoting and binding automatically:

mform = rptyear & '-' & format(month,@n02) & '-01'
SQL(month_limit, 'DELETE FROM month_limit;')
SQL(month_limit, 'INSERT INTO month_limit VALUES (?1);', mform)

Or, if there’s a specific column:

SQL(month_limit, 'INSERT INTO month_limit (MonthStart) VALUES (?1);', mform)

The log won’t show the bound parameter value — it just logs the prepared SQL — but it’ll execute correctly once you remove the + symbols.

I hope that helps.
Mark

5

Thanks Mark,

Makes perfect sense, and works, which are both good. Those +s in the example in the documentation obviously confused the shit out of me. Since the only SQL-type driver (which would use LIKE) so far is SQLite, and the concatenation operator is ||, same as it is in Oracle, I thought the +s were some special code inside of what was sent to the driver so it could do the binding. The MSSQL concatenation operator is a +, so maybe the examples in the documentation would have made more sense to to an MSSQL user. Add in that I’m replacing PROP:SQL where you are basically concatenating strings of boilerplate and formatted Clarion variables, and that probably warped my understanding, too.

Jon

6

It’s an excellent question overall Jon. I am updating the documentation to try and clear up the misconceptions (and also include this as a simpler example.)
Incidentally if you run it now with logging on I think the [BIND] will appear in the log. (Check and let me know please.)

I agree the use of + as the concatenation symbol there wasn’t over-helpful. Unfortunately there are sufficient differences in SQL syntax to make these examples tricky to construct.

Lastly, where ever possible, do things like string concatenation on the client, not the server. In this case passing rptyear, and mform as separate parameters was probably slower than passing the whole string as you’re doing now.