[SOLVED]Params-MySql-column 'id' cannot be null

[aliboy]

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]

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]

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]

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.');


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?

I 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:

Can not open a Resultset

it may occurs for  stored procedure variable ? (IN)


thank you for reply.

[EgonHugeist]

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]

hi

@EgonHugeist

thank you for your kindness.

I will try test it.

[goodname]

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]

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