Recent

Author Topic: [SOLVED]Params-MySql-column 'id' cannot be null  (Read 8316 times)

aliboy

  • New Member
  • *
  • Posts: 23
[SOLVED]Params-MySql-column 'id' cannot be null
« on: December 07, 2012, 02:29:59 pm »
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.
« Last Edit: December 10, 2012, 10:22:46 am by aliboy »

goodname

  • Sr. Member
  • ****
  • Posts: 297
Re: Params-MySql-column 'id' cannot be null
« Reply #1 on: December 07, 2012, 03:54:51 pm »
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

  • Jr. Member
  • **
  • Posts: 62
Re: Params-MySql-column 'id' cannot be null
« Reply #2 on: December 07, 2012, 08:15:04 pm »
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

  • New Member
  • *
  • Posts: 23
Re: Params-MySql-column 'id' cannot be null
« Reply #3 on: December 07, 2012, 09:39:04 pm »
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:

Quote
Can not open a Resultset

it may occurs for  stored procedure variable ? (IN)


thank you for reply.

EgonHugeist

  • Jr. Member
  • **
  • Posts: 62
Re: Params-MySql-column 'id' cannot be null
« Reply #4 on: December 08, 2012, 12:00:22 am »
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

  • New Member
  • *
  • Posts: 23
Re: Params-MySql-column 'id' cannot be null
« Reply #5 on: December 08, 2012, 01:45:33 pm »

hi

@EgonHugeist

thank you for your kindness.

I will try test it.

goodname

  • Sr. Member
  • ****
  • Posts: 297
Re: Params-MySql-column 'id' cannot be null
« Reply #6 on: December 08, 2012, 04:51:34 pm »
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
« Last Edit: December 08, 2012, 05:12:16 pm by goodname »

aliboy

  • New Member
  • *
  • Posts: 23
Re: Params-MySql-column 'id' cannot be null
« Reply #7 on: December 10, 2012, 10:09:05 am »
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