Mysql Typs (Null) Typ (Integer)

[question]

Hello,
I am using Delphi with mysql, i have a table which contains the data.For example
Table A has ID,Name,Phone and so on , the empty field are filled with "Null"
ID=1, Name=To,Phone=null,

I would like to copy the data from Table A to Table B, but the problem is, if there is no value in a filed then its filled with '0' instead of Null and i got a error messege

"Variante des Typs (Null) konnte nicht in Typ (Integer) konvertiert werden."

ID=1,Name=Tom,Phone=0

my question is, why one table put Null and others 0,since both are same field and same type

need suggestion, i wanna filled the emtpy field not with 0 rather Null,how to do it?

Code:

Query.SQL.Clear;
    Query.SQL.Add('SELECT * FROM TableA WHERE ID = :ID');
    Query.ParamByName('ID').AsInteger := NewID;
    Query.Open;

    while (Query.Eof) and (Query.RecordCount > 0) do

   begin
      QueryB.Close;
      QueryB.SQL.Clear;

      QueryB.SQL.Add('INSERT INTO `TableB` (`ParentID`,`NewID`, and so on
     ,.......
      QueryB.ParamByName('ParentID').AsInteger := Query.FieldByName('ParentID').AsInteger;
      QueryB.ParamByName('NewID').AsInteger := NewID;

I would like to fill the empty field with "null" instad of "0" what should i change ?

[sx2008]

Delphi-Quellcode:

// Fill a parameter with NULL
QueryB.ParamByName('NewID').Value := Null; 

// Fill a field with NULL
QueryB['SomeField'] := Null;
// alternative way to set a field to NULL
QueryB.FieldByName('SomeField').Clear;

// testing if a field is NULL
if Query.FieldByName('SomeField').IsNull then ...



// Copy a Field that might be NULL
// the VCL converts different datatypes on the fly e.g. from Integer -> string
QueryB['DestinationField'] := QueryA['SourceField'];
// this is the same as (long version of the line above)
QueryB.FieldByName('DestinationField').Value := QueryA.FieldByName('SourceField').Value;



// and some bonus info...
// how to set the SQL string of a query in a single line without using .Clear() followed by a .Add()
Query.SQL.Text := 'SELECT * FROM TableA WHERE ID = :ID';

[question]

Code:

QueryA.ParamByName('ID').AsInteger := QueryBID.AsInteger //this using the field of VCL Component)
QueryYA.ParamByName('ID').AsInteger :=  ueryB.FieldByName(('ID').AsInteger;// this using  sqil query fieldbyname

value of both query can be different, i mean Null and 0?

[Valle]

You can copy data without using Delphi:

Code:

-- untested, might need some more parenthesis
INSERT INTO `B` (foo, bar)
SELECT foo, bar FROM `A` WHERE id = 42;

That requires less network traffic and should be much faster.

Maybe that would be a better alternative for you.

[sx2008]

Zitat:

Zitat von question (Beitrag 1226167)

Code:

QueryA.ParamByName('ID').AsInteger := QueryBID.AsInteger //this using the field of VCL Component)
QueryYA.ParamByName('ID').AsInteger :=  ueryB.FieldByName(('ID').AsInteger;// this using  sqil query fieldbyname

If you want to transfer a Value that could be both a integer or NULL you have to use the data type "Variant".
A

Delphi-Quellcode:

Variant

can contain different data type like integer, floats, booleans, strings.
And a Variant can also be

Delphi-Quellcode:

Null

!
This means that

Delphi-Quellcode:

Variant

is a nullable data type.
To get or set the value of a field you can use the

Delphi-Quellcode:

property Value

.
The Properties AsString, AsInteger, AsBoolean,... are specialised properties.

Delphi-Quellcode:

// your code example using property Value instead of property AsInteger
QueryA.ParamByName('ID').Value := QueryBID.Value; //this using the field of VCL Component)
QueryYA.ParamByName('ID').Value :=  ueryB.FieldByName(('ID').Value;// this using  sqil query fieldbyname