SQL statements for CUI with sqlite
Hello, in 3.0.x, the queries.conf for the cuisql module / post-auth / dialect sqlite state: INSERT IGNORE INTO ${..cui_table} \ This breaks (gracefully) for me, and I'm told it's a syntax error. This page here: https://www.sqlite.org/lang_insert.html says it should be INSERT OR IGNORE Might be worth fixing for 3.0.2. Greetings, Stefan Winter -- Stefan WINTER Ingenieur de Recherche Fondation RESTENA - Réseau Téléinformatique de l'Education Nationale et de la Recherche 6, rue Richard Coudenhove-Kalergi L-1359 Luxembourg Tel: +352 424409 1 Fax: +352 422473 PGP key updated to 4096 Bit RSA - I will encrypt all mails if the recipient's key is known to me http://pgp.mit.edu:11371/pks/lookup?op=get&search=0xC0DE6A358A39DC66
On 3 Feb 2014, at 15:21, Stefan Winter <stefan.winter@restena.lu> wrote:
Hello,
in 3.0.x, the queries.conf for the cuisql module / post-auth / dialect sqlite state:
INSERT IGNORE INTO ${..cui_table} \
This breaks (gracefully) for me, and I'm told it's a syntax error. This page here:
Thanks, fixed. Arran Cudbard-Bell <a.cudbardb@freeradius.org> FreeRADIUS Development Team FD31 3077 42EC 7FCD 32FE 5EE2 56CF 27F9 30A8 CAA2
Hi,
This breaks (gracefully) for me, and I'm told it's a syntax error. This page here:
Thanks, fixed.
Well... after doing this on my machine, I noticed more errors with sqlite: rlm_sql (cuisql): Executing query: 'INSERT OR IGNORE INTO cui (clientipaddress, callingstationid, username, cui, lastaccounting) VALUES ('127.0.0.1', '02-00-00-00-00-01', 'availability-test@education.lu', 'ad40aca101096cde0ce27b387939e4c76d8234ca', NULL) ON DUPLICATE KEY UPDATE lastaccounting='0000-00-00 00:00:00', cui='ad40aca101096cde0ce27b387939e4c76d8234ca'' rlm_sql (cuisql): Database query error: near "ON": syntax error (16) cuisql : No additional queries configured Looking at the same page - the entire "ON DUPLICATE KEY UPDATE" doesn't work in sqlite at all? This would look like an overhaul of that statement is needed. I'll be using a proper database instead now; MySQL is near me :-) Greetings, Stefan Winter -- Stefan WINTER Ingenieur de Recherche Fondation RESTENA - Réseau Téléinformatique de l'Education Nationale et de la Recherche 6, rue Richard Coudenhove-Kalergi L-1359 Luxembourg Tel: +352 424409 1 Fax: +352 422473 PGP key updated to 4096 Bit RSA - I will encrypt all mails if the recipient's key is known to me http://pgp.mit.edu:11371/pks/lookup?op=get&search=0xC0DE6A358A39DC66
On 4 Feb 2014, at 06:46, Stefan Winter <stefan.winter@restena.lu> wrote:
Hi,
This breaks (gracefully) for me, and I'm told it's a syntax error. This page here:
Thanks, fixed.
Well... after doing this on my machine, I noticed more errors with sqlite:
rlm_sql (cuisql): Executing query: 'INSERT OR IGNORE INTO cui (clientipaddress, callingstationid, username, cui, lastaccounting) VALUES ('127.0.0.1', '02-00-00-00-00-01', 'availability-test@education.lu', 'ad40aca101096cde0ce27b387939e4c76d8234ca', NULL) ON DUPLICATE KEY UPDATE lastaccounting='0000-00-00 00:00:00', cui='ad40aca101096cde0ce27b387939e4c76d8234ca'' rlm_sql (cuisql): Database query error: near "ON": syntax error (16) cuisql : No additional queries configured
Looking at the same page - the entire "ON DUPLICATE KEY UPDATE" doesn't work in sqlite at all? This would look like an overhaul of that statement is needed.
INSERT OR REPLACE I already had the fix in master. Arran Cudbard-Bell <a.cudbardb@freeradius.org> FreeRADIUS Development Team FD31 3077 42EC 7FCD 32FE 5EE2 56CF 27F9 30A8 CAA2
participants (2)
-
Arran Cudbard-Bell -
Stefan Winter