+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: [SOLVED] SQL update using OUTPUT clause (/showthread.php?tid=67128)
[SOLVED] SQL update using OUTPUT clause - henesnarfel - 01-18-2017
I'm trying to use the OUTPUT clause here https://msdn.microsoft.com/en-us/library/ms177564.aspx
What I'm doing is updating a column that contains a key for a record. So say the key is 10. I need to update it to 11 and return that 10. This can be accomplished by using a simple update query but codeigniter isn't liking it and won't return any results even though it does complete.
This query does not return any results and I get an error trying to use result_array(), result(), or row()
Code:
UPDATE
KEY_COUNTERS
SET
KEYS_COUNTERS = KEYS_COUNTERS + 1
OUTPUT
deleted.KEYS_COUNTERS
WHERE
KEY_COUNTERS_ID = 'KEY'This query runs as well and I can run result_array() and get the deleted key but I don't want to have to run a select and then update.
Code:
DECLARE @KEY INT;
SELECT
@KEY = KEYS_COUNTERS+1
FROM
KEY_COUNTERS
WHERE
KEY_COUNTERS_ID = 'KEY'
UPDATE
KEY_COUNTERS
SET
KEYS_COUNTERS = @KEY
OUTPUT
deleted.KEYS_COUNTERS
WHERE
KEY_COUNTERS_ID = 'KEY'Can anyone tell me why the first query doesn't return results but the bottom one does. If I run this query in SQL management studio I get the same result so only codeigniter isn't working. The only thing I can think is that since the first query only has the Update query that it doesn't return results because of codeigniter.
RE: SQL update using OUTPUT clause - henesnarfel - 01-18-2017
Well I guess in case someone else has a similar issue. I dug into the framework code and documentation found out that you can force the query to return the Result Object.
https://www.codeigniter.com/userguide3/database/db_driver_reference.html#CI_DB_driver::query
so when running the function query() you can do the following query($sql, array(), TRUE) and no matter what you will get the result object which is what I needed.