Welcome Guest, Not a member yet? Register   Sign In
MSSQL, temporary tables and CI


I'm writing a new internal dashboard for the company I work at using CI and have almost finished the reporting feature. I've come across a bit of a weird problem though which I don't know how to solve (and I can't spot anything within the forums / knowledgebase).

What I'm doing is building up my own SQL query using variables within a form submission and then using the DB class to run the query ( $this->db->query($sql) ). This approach works fine but is causing me some problems when I start to create a more complicated queries which require the use of temporary tables.

I've tested my queries extensively outside of CI and they work perfectly everytime (ran within a simple PHP script and the mssql_query($sql) syntax), but within CI sometimes they work, sometimes they don't. Sometimes the whole page fails giving me the error that num_rows() is called on a non object, other times the page renders but with no data. DB Debuging doesn't return any error numbers. Other quieries without the the temp tables execute fine everytime...

Is it something to do with persistent connections and the way CI deals with "page" loads (the sql is coming from the db, into a model, controller, back to model etc...).


Aha! Solved it!

Looks like the drop commands at the bottom of the query aren't ran correctly and the temporary tables still exist in the catalog. Chucked a drop check for existence and drop to the top of the SP and job's a goodun.

Theme © iAndrew 2016 - Forum software by © MyBB