Re^2: DBD::SQLite, how to pass array in query via placeholder?

Thanks but this module is not abstract enough for my purposes. Or perhaps I didn't grok it in full yet. Can you define the following query:

SELECT SPLIT, syn(SPLIT),
(SELECT COUNT(*) FROM cagent WHERE ACD = $1 AND SPLIT = $2 AND
LOC_ID = ANY ($3) AND LOGID IS NOT NULL AND WORKMODE = 40),
(SELECT COUNT(*) FROM cagent WHERE ACD = $1 AND SPLIT = $2 AND
LOC_ID = ANY ($3) AND LOGID IS NOT NULL AND WORKMODE = 30),
(SELECT COUNT(*) FROM cagent WHERE ACD = $1 AND SPLIT = $2 AND
LOC_ID = ANY ($3) AND LOGID IS NOT NULL AND WORKMODE = 50),
(SELECT COUNT(*) FROM cagent WHERE ACD = $1 AND SPLIT = $2 AND
LOC_ID = ANY ($3) AND LOGID IS NOT NULL AND WORKMODE = 220),
(SELECT COUNT(*) FROM cagent WHERE ACD = $1 AND SPLIT = $2 AND
LOC_ID = ANY ($3) AND LOGID IS NOT NULL),
(SELECT COUNT(*) FROM cagent WHERE ACD = $1 AND SPLIT = $2 AND
LOC_ID = ANY ($3) AND LOGID IS NOT NULL AND WORKMODE = 20),
(SELECT COUNT(*) FROM cagent WHERE ACD = $1 AND SPLIT = $2 AND
LOC_ID = ANY ($3) AND LOGID IS NOT NULL AND WORKMODE = 80)
FROM csplit WHERE ACD = $1 AND SPLIT = $2
[download]

...in terms of SQL::Abstract? I don't see a way, at least no straightforward way.

Regards,
Alex.

Comment on Re^2: DBD::SQLite, how to pass array in query via placeholder? Download Code

Replies are listed 'Best First'.
Re^3: DBD::SQLite, how to pass array in query via placeholder? by Jenda (Abbot) on Apr 11, 2011 at 09:20 UTC
I wonder ... wouldn't something like this perform better and be easier to tweak for SQL::Abstract? SELECT SPLIT, syn(SPLIT), SUM(CASE WHEN WORKMODE = 40 THEN 1 ELSE 0 END), SUM(CASE WHEN WORKMODE = 30 THEN 1 ELSE 0 END), SUM(CASE WHEN WORKMODE = 50 THEN 1 ELSE 0 END), SUM(CASE WHEN WORKMODE = 220) THEN 1 ELSE 0 END, COUNT(), SUM(CASE WHEN WORKMODE = 20 THEN 1 ELSE 0 END), SUM(CASE WHEN WORKMODE = 80 THEN 1 ELSE 0 END) FROM csplit JOIN cagent ON cagent.ACD = csplit.ACD AND cagent.SPLIT = csplit.SPL +IT WHERE csplit.ACD = $1 and csplit.SPLIT = $2 and cagent.LOC_ID = ANY ($3) and cagent.LOGID IS NOT NULL GROUP BY SPLIT [download] Jenda Enoch was right!* Enjoy the last years of Rome.	[reply] [d/l]
Re^4: DBD::SQLite, how to pass array in query via placeholder? by dwalin (Monk) on Apr 11, 2011 at 19:22 UTC
Jenda, I agree that query in my example may not be the most optimal, but I believe it is to be out of scope for my question. In fact, I won't use SQL::Abstract in this case just because I don't need it. I've got a library of such queries already written and tested, and it's a waste of time to rebuild them with SQL::Abstract just for the sake of it. Besides, it doesn't solve my problem with DBD::SQLite, and with DBD::Postgres these queries work as expected without SQL::Abstract so I just don't need it. Thanks for suggestion though. Regards, Alex.	[reply]
Re^4: DBD::SQLite, how to pass array in query via placeholder? by Anonymous Monk on Jun 30, 2012 at 13:47 UTC
function showRecords2() { var firstName2 = document.getElementById('f +irstName22').value; results4.innerHTML = ''; db.transaction(function(tx4) { //tx.executeSql(selectAllStatement2, [], function(tx, result +) { //var firstName2 = document.getElementById('firstName22'); //tx.executeSql('SELECT * FROM Strokes WHERE email = ?', [ema +il], renderResults); // tx4.executeSql('SELECT * FROM Contacts WHERE firstNam +e="Joo" ', [], function(tx4, result4) { tx4.executeSql('SELECT * FROM Contacts WHERE firstName="' + +firstName2 + '" ', [], function(tx4, result4) { dataset = result4.rows; // report(result.rows[0].firstName, result.rows[0].lastName) +; for (var i = 0, item = null; i < dataset.length; i++) { item = dataset.item(i); results4.innerHTML +='<table width="320" border="7"><tr +> <td style="font-size:18px" width="90">' + item['id'] + '</td>' ++ '<td width="30" style="font-size:17px">' + item['lastName'] + '</td +>' + '<td>' + '<a href="#" onclick="loadRecord('+i+')">Edit -- </a +> ' + '</td>' + '<td>' + '<a href="#" onclick="deleteRecord('+it +em['id']+')">Delete</a></td></tr></table>'; //results.innerHTML += // '<li>' + item['lastName'] + ' , ' + item['firstNam +e'] + ', ' + item['phone'] + '----------- <a href="#" onclick="loadRe +cord('+i+')">edit</a></li>'; } }); }); [download]	[reply] [d/l]