CodeIgniter Forums
Loading a SQL file to run queries. - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forum-5.html)
+--- Forum: General Help (https://forum.codeigniter.com/forum-24.html)
+--- Thread: Loading a SQL file to run queries. (/thread-70752.html)



Loading a SQL file to run queries. - htaningco - 05-24-2018

So I have the following.. I am trying to load a SQL file like this.

$params = array(
  'foo' => 'test',
  'bar' => 'case'
);

$filename = 'test.sql';
$sql = $this->load->file($filename, true);
$query = $this->sedona_db->query($sql,$params); 


the 'test.sql' file looks something like this:

Declare @foo varchar(10) = ?
Declare @bar varchar(10) = ?

Select * ... something something where foo = @foo
Exec someStoredProc @foo

--------------------------------------

I would get an error msg like this:
mssql_query(): message: Must declare the scalar variable "@foo"(severity 15)


RE: Loading a SQL file to run queries. - htaningco - 05-31-2018

I figured out the issue to this.

In the SQL file I needed to redeclare the params again in the code.
Because of the complexity of having multiple statements firing, the datatype of the params are lost in scope.