Start Building Professional
Web Apps Today

empty cell creates invalid sql

Hi, testing sending data to a database table and all is working well. However, the update fails when a cell is left empty.
With debugger on, I looked in the log and it looks as though the sql that is generated puts quotes around the value and therefore triggering a sql error due to the fact that the field in the database is an integer field.

Quotes around a number seem to be ok, but an empty string is not according to postgres.

Is there a way to set a cell data type to be integer? Or perhaps I will have to somehow send '0' when the cell is empty rather than ''

Here is the log entry:

Edit operation started [GRID]
1_gr_id => 1
1_c0 => 1
1_c1 => 1234
1_c2 => blahblah
1_c3 =>
1_!nativeeditor_status => updated
ids => 1

Row data [1]
gr_id => 1
0 => 1
1 => 1234
2 => blahblah
3 =>
!nativeeditor_status => updated

Exec SQL: UPDATE pos_line_data SET line_no='1',m_product_id='1234',product_name='blahblah',quantity='' WHERE pos_line_data_id='1'

PostgreSQL error: ERROR: invalid input syntax for integer: ""

Yes, both MySQL and PostgreSQL doesn't mind about quotes ( which prevents sql injection ) , but if you have in DB an integer field and incoming data is a string - the problem may occur ( MySQL mostly ignore it, but may be different for PostgreSQL )

>>Or perhaps I will have to somehow send '0' when the cell is empty rather than ''
function my_code($action){
      $quantity = $action->get_value("quantity");
      if ($quantity == "" ) //or any other check here
               $action->set_value("quantity","0"); //set 0 as default value
}
$grid->event->attach("beforeProcessing","my_code")

such code need to be added before render_sql or render_table command

Alternatively you can adjust client code to use 0 as default value of column ( but server side hook is preferable, because it will validate data for all kinds of operations )