[SOLVED]Params-MySql-column 'id' cannot be null
lazarus / 16-Databases

----------------------------------------------------------------------
aliboy · 2012-12-07 13:29

hi

I create a database (amahi) and table(persian) that has 3 columns, in
Workbench i use  insert_p procedure:

Code: [Select]DELIMITER // 

USE `amahi`;

//



DROP procedure IF EXISTS `insert_p`;
//

CREATE PROCEDURE `amahi`.`insert_p` (IN `p_id` int(10),IN `p_name`

varchar(20),IN `p_family` varchar(30))

BEGIN


         INSERT INTO  persian(id,name,family)

         VALUES (p_id,p_name,p_family);


END //
I could connected to MySQL without any errors and  Retrieved data form
it,I'm going to send data vi params.

I design a Button1 on form and write below code onClick Button1:
(notice:Inputs was Initialized)

Code: [Select]   ZQuery2.Active:=true;
   ZQuery2.SQL.Text:='call `amahi`.`insert_p`(:id,:name,:family);';  
//or SQL property of Zquery2
   ZQuery2.Params.Items[0].AsInteger:=StrToInt(Edit1.Text);
   ZQuery2.Params.Items[1].AsString:=Edit2.Text;
   ZQuery2.Params.Items[2].AsString:=Edit3.Text;
   ZQuery2.ExecSQL;
                   
as I run project meet to this error:

Code: [Select]sql error:column 'id' cannot be null

thank you.

----------------------------------------------------------------------
goodname · 2012-12-07 14:54

Are you sure that Edit1.text has a valid number? Test the value using
TryStrToInt.

Code: [Select]var i: Integer
...
if TryStrToInt(Edit1.Text,i) then ZQuery2.Params.Items[0].AsInteger:=i
else ShowMessage('Entered value is not a number.');

----------------------------------------------------------------------
EgonHugeist · 2012-12-07 19:15

I guess godname id right.

Hint: Use the TZStoredProc instead. Then you can also retrieve all
out-parameters (bidirectional) and multiple resultssets.

Good luck.

----------------------------------------------------------------------
aliboy · 2012-12-07 20:39

Quote from: goodname on December 07, 2012, 03:54:51 pmAre you sure
that Edit1.text has a valid number? Test the value using TryStrToInt.

Code: [Select]var i: Integer
...
if TryStrToInt(Edit1.Text,i) then ZQuery2.Params.Items[0].AsInteger:=i
else ShowMessage('Entered value is not a number.');

hi

@goodname:

thank you.the project works with  your solution and id value send to
MySQL but other fields just send Null to table.

how can I get valid input for integer and string?




Quote from: EgonHugeist on December 07, 2012, 08:15:04 pmI guess
godname id right.

Hint: Use the TZStoredProc instead. Then you can also retrieve all
out-parameters (bidirectional) and multiple resultssets.

Good luck.

@EgonHugeist:
thank you.
 Can you show me a tutorial about this component?

A question:

after executed (sending data) the project shows this error:

QuoteCan not open a Resultset
it may occurs for  stored procedure variable ? (IN)


thank you for reply.

----------------------------------------------------------------------
EgonHugeist · 2012-12-07 23:00

I'm sorry there is no tutorial available, i think. Long times i'm
waiting for somebody who helps us 

Code: [Select]  StoredProc.StoredProcName := 'ABTEST'; //available in
th Property-Editor / on changing this, Zeos determines all Parameters
and theire purpose
  StoredProc.ParamByName('P1').AsInteger := 50; //simple value
assignment
  StoredProc.ParamByName('P2').AsInteger := 100;
  StoredProc.ParamByName('P3').AsString := 'a';
  StoredProc.ExecProc; //executes the Stored Procedure and returns all
Out and Return values to the Parameters
  StoredProc.Open; //executes the StoredProcedure and returns (a)
reultset(s);
  StoredProc.FirstResultSet; //returns the first resultset if more are
available
  StoredProc.PreviousResultSet; //prevous resultset
  StoredProc.NextResultSet;
  StoredProc.LastResultSet;
  StoredProc.SetResultSet(const Index: Integer);
  StoredProc.ResultSetCount: Integer;
  StoredProc.BOR: Boolean; //Begin of Resultsets ?
  StoredProc.EOR: Boolean; //End of Resultsets ?

----------------------------------------------------------------------
aliboy · 2012-12-08 12:45

hi

@EgonHugeist

thank you for your kindness.

I will try test it.

----------------------------------------------------------------------
goodname · 2012-12-08 15:51

You could try either of the following.
Code: [Select]if length(Edit2.Text)>0 then ...
if Edit2.Text<>'' then ...
http://lazarus-
ccr.sourceforge.net/docs/rtl/sysutils/stringfunctions.html

----------------------------------------------------------------------
aliboy · 2012-12-10 09:09

thank you for responses

in this case  ZQuery2.Active:=true; cause to send null value to
database(MySQL) and in all case we do not put   ZQuery2.Active:=true;
as the first code then I clear it and my code was:

Code: [Select] 
 ZQuery2.Params.Items[0].AsInteger:=StrToInt(Edit1.Text);
   ZQuery2.Params.Items[1].AsString:=Edit2.Text;
   ZQuery2.Params.Items[2].AsString:=Edit3.Text;
   ZQuery2.ExecSQL;
so with above code we don't need to extra function.

thank to all for responses