The Way Of Life: CodeIgniter cannot Insert NULL on MSSQL

Sunday, April 28, 2013

CodeIgniter cannot Insert NULL on MSSQL

Recently, I experienced a strange case when i was porting a desktop application to a web application. I am using CodeIgniter / PHP framework as a front end and for back end I am  using ExtJS, for Database Server I am using the Microsoft SQLServer.
When I run the store procedure from CodeIgniter,
the result  from stored procedures are not completed.But  if I run it from  Query Analyzer , it run perfectly and produces a perfect result . If in Query Analyzer success with 221 rows, in CodeIgniter / PHP only success with 107 rows. First analysis is due to time out, then I fix this by editing php.ini and increase the time out setting and the problems still occurs.


 Then I optimize store procedures that previously when I run it, it takes almost 2 minutes to about 24 second, but it doesn't help.  I was desperate, until I tried to connect query using pure php command , that use  mssql_, from there I found out that there is an error that  CodeIgniter cannot insert NULL for a particular table.   

$proc = mssql_init('Store_Procedure_Name', $conn);

And the error does not appear when I run it using modules from CodeIgniter, finally I fixed some code in stored procedures which is INSERT NULL value.  

ISNULL(field_name,' ') 

Finally IT SOLVED!, the store procedure runs perfectly when executed using CodeIgniter.

Some tips to avoid cases such as the above are:
1. Avoid NULL
value posibility on INSERT query you created.
2. Create 
query as simple as possible , that will help you when debuging in the future.

The conclusion is CodeIgniter refused INSERT NULL but will not display an error, while in Query Analyzer is  allow that to happen. This case maybe can make you confused when you convert from desktop applications to web applications. I hope these tips useful for you.

Labels: , , , ,


Post a Comment

<< Home