Memory error on IF date validation for null or not null mysql mariadb
lazarus / 16-Databases

----------------------------------------------------------------------
douglas.cast · 2016-05-16 02:49

Hi guys, I need help with a date null date insert into a database.

My project have a database with 34 tables and 27 of them is related to
the main user table, I've created extra tables for phones, emails,
user picture, considering not all the users will provide (maybe dont
have) all the fields.

Everything is working almost fine until I try to set a IF to set a
null value to the date field inside of my main query.

IF (length(dateofbirth.text)=0) then begin
query.parambyname('dateofbirth').value:=Null;
end
else begin
query.parambyname('dateofbirth').asdate:=strtodate(dateofbirth.text);
end;

This piece of code causes cause a out of memory error, and if I try to
post the date without it i got a EConvertError "" is not a valid date
format, or the except showmessage(error) of my try command if i run it
out of Lazarus, the only way to avoid is type a valid date on all 4
fields but none of them is a NOT NULL inside of the databse, my
database dates is also define like DATE DEFAULT NULL.

My basic structure for the INSERT of my tableis:

//typed only once until my last table change
try
with connection do begin
connection.autocommit
connection.starttransaction

//repeated some times
qry.sql.clear
qry.sql.text:='INSERT INTO table (name), ... VALUES (:name, ...')
qry.exec
qry.sql.clear
qry.sql.text:='SELECT LAST_iNSERT_ID AS id FROM table'
qry.open
idUser:=qry.fieldbyname('id').asinteger
qry.execsql

and hier some other table changes with insert and update values as the
above, always using the open when I set some LAST_INSERT_ID FROM some
table to my var idTemp (using only one to all the pk ids on the 27
tables and idUser to the user), but never closing (I'm not closing
because it's working this way, not sure about it)

try
connection.commit
except
connection.rollback
showmessage('error')

Until now my insert from my first try to last line uses 253 lines,
some of the inserts have the IF in the begining to define if its going
to insert/update or not the values to the database, my main problem is
the date itself.

The ram problem it's from the computer (I've checked).

My pc
AMD Phenon 2 x4 3.something
16 gb of ram
NVidia 1gb vram

ARCH LInux with KDE Plasma desktop, MariaDB 10.1

Ty for the help.

----------------------------------------------------------------------
balazsszekely · 2016-05-16 04:23

Insted of TEdit I would switch to a TDateTimePicker or
TDBDateTimePicker. Anyway if you prefer TEdit,  try something like
this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var  Date:
TDate;begin  if (Length(Trim(DateOfBirth.Text)) <> 0) then  begin   
if not TryStrToDate(DateOfBirth.Text, Date) then    begin     
MessageDlg('Please enter a valid date(Format: ' +
FormatSettings.ShortDateFormat + ')! ', mtInformation, [mbOk], 0);   
 DateOfBirth.SetFocus;    end    else     
Query.ParamByName('DATEOFBIRTH').AsDate := Date;  end  else   
Query.Params.ParamValues['DATEOFBIRTH'] := null;end;

----------------------------------------------------------------------
douglas.cast · 2016-05-16 12:40

In Lazarus:
Project raised exception class 'RunError(203)'
At adress 43ACBC
>Continue
Project raised exception class 'EOutOfMemory' with message:
Out of memory
At adress 43ACBC
>Continue
except error message from my try.

Out of Lazarus:
except error message from my try.

----------------------------------------------------------------------
balazsszekely · 2016-05-16 12:52

Which line gives you that error? Can you attach a demo
application(source only)?

----------------------------------------------------------------------
douglas.cast · 2016-05-16 13:13

the IF generate the error, if i run the application without the IF for
the date and type the date into the field, things run fine.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i       with
icdm.connection do begin          icdm.connection.AutoCommit:=True;  
      icdm.connection.StartTransaction;                         
icdm.qry.SQL.Clear;              icdm.qry.SQL.Text:=             
'INSERT INTO people (name, sex, dateofbirth, maritalstatus, state,
cities, schooling, joindate, typeofbond, occupationtype) VALUES
(:dbname, :dbsex, :dbdateofbirth, :dbmaritalstatus, :dbstate,
:dbcities, :dbschooling, :dbjoindate, :dbtypeofbond,
:dboccupationtype)';             
icdm.qry.ParamByName('dbname').AsString:=teName.Text;             
icdm.qry.ParamByName('dbsex').AsInteger:=cbSex.ItemIndex+1;          
                                        //I'm trying the IF in this
line, without IF runs OK, with IF show ram error.                    
    //->I also have a date validation funcition to avoid wrong dates
that runs on the ON EXIT on the date fields                         
//->Can't set imcomplete dates, mask to type the /, numbers only and
backspace, can't set any kind of future date or dates older than 120
years from actual yer.                                        icdm.qry
.ParamByName('dbdateofbirth').AsDate:=StrToDate(teDateOfBirth.Text); 
                                                               icdm.qr
y.ParamByName('dbmaritalstatus').AsInteger:=cbMaritalStatus.ItemIndex+
1;             
icdm.qry.ParamByName('dbstate').AsInteger:=cbState.ItemIndex+1;      
      icdm.qry.ParamByName('dbcities').AsInteger:=cbCity.ItemIndex+1; 
            icdm.qry.ParamByName('dbschooling').AsInteger:=cbSchooling
.ItemIndex+1;              icdm.qry.ParamByName('dbjoindate').AsDate:=
StrToDate(teJoinDate.Text);              icdm.qry.ParamByName('dbtypeo
fbond').AsInteger:=cbTypeOfBond.ItemIndex+1;              icdm.qry.Par
amByName('dboccupationtype').AsInteger:=cbOccupationType.ItemIndex+1; 
            icdm.qry.ExecSQL;              icdm.qry.SQL.Clear;       
     icdm.qry.SQL.Text:='SELECT LAST_INSERT_ID() AS iddb FROM
people';              icdm.qry.Open;             
idUser:=icdm.qry.FieldByName('iddb').AsInteger;             
icdm.qry.ExecSQL;                                                    
//ADRESS COMPLEMENT              if
(Length(teAdressComplement.Text)>0) then begin               
icdm.qry.SQL.Clear;                icdm.qry.SQL.Text:='INSERT INTO
adresscomplement (sid, description) VALUES (:dbsid, :dbdescription)'; 
              icdm.qry.ParamByName('dbsid').AsInteger:=idUser;       
      
icdm.qry.ParamByName('dbdescription').AsString:=teComplement.Text;   
           icdm.qry.ExecSQL;              end;                       
               //PHONE FIELD 1              if (cbPhoneType1.ItemIndex
> -1) and (tePhone1.Text <> '') then begin               
icdm.qry.SQL.Clear;                icdm.qry.SQL.Text:='INSERT INTO
phonenumber (pnid, pnumber) VALUES (:dbpnid, :dbnumber)';            
  icdm.qry.ParamByName('dbpnid').AsInteger:=cbPhoneType1.ItemIndex+1; 
             
icdm.qry.ParamByName('dbnumber').AsString:=tePhone1.Text;            
  icdm.qry.ExecSQL;                 icdm.qry.SQL.Clear;              
icdm.qry.SQL.Text:='SELECT LAST_INSERT_ID() AS pnid FROM
phonenumber';                icdm.qry.Open;               
idTemp:=icdm.qry.FieldByName('pnid').AsInteger;               
icdm.qry.ExecSQL;                 icdm.qry.SQL.Clear;               
icdm.qry.SQL.Text:='INSERT INTO phonerelation (sid, pnid) VALUES
(:dbuid, :dbpnid)';               
icdm.qry.ParamByName('dbuid').AsInteger:=idUser;               
icdm.qry.ParamByName('dbpnid').AsInteger:=idTemp;               
icdm.qry.ExecSQL;                                                    
            //STREETH ADRESS                if
(cbStreethAdress.ItemIndex > -1) then begin               
icdm.qry.SQL.Clear;                icdm.qry.SQL.Text:='SELECT said AS
dbsaid FROM streeths WHERE sastreethname = :dbstreethname';          
   
icdm.qry.ParamByName('dbstreethname').AsString:=cbStreethAdress.Text; 
              icdm.qry.Open;               
idTemp:=icdm.qry.FieldByName('dbsaid').AsInteger;               
icdm.qry.ExecSQL;                icdm.qry.Close;               
icdm.qry.SQL.Clear;                icdm.qry.SQL.Text:='UPDATE people
SET sstreethadress = :dbsaid  WHERE sid = :dbsid';               
icdm.qry.ParamByName('dbsid').AsInteger:=idUser;               
icdm.qry.ParamByName('dbsaid').AsInteger:=idTemp;               
icdm.qry.ExecSQL;              end;                                  
                     //USER IMAGE                           if
opdUserImg.FileName <> '' then                 begin                 
 imgTemp:=TFileStream.Create(opdUserImg.FileName, fmOpenRead);       
          icdm.qry.SQL.Clear;                 
 icdm.qry.SQL.Text:='INSERT INTO userimage (sid, uiimage) VALUES
(:dbsid, :dbuiimage)';                 
 icdm.qry.Params[0].Name:='dbsid';                 
 icdm.qry.Params[0].Value:=idUser;                 
 icdm.qry.Params[1].Name:='dbuiimage';                 
 icdm.qry.Params[1].LoadFromStream(imgTemp, ftGraphic);              
   icdm.qry.ExecSQL;                   imgTemp.Free;   
I repeate this process a couple of times, 4 phones, the streeth adress
system for postal code, neighborhood, city, state, and some Inserts in
another tables like user image, email, observations and etc.

The combo boxes (cb's) are loaded from the database, but how user can
add fields to the database while his typing the informations, i set
the search to take the id and insert into another places, the +1 is to
assign the right value to the search when i have cb's with default
values.

I don't think this can be a issue but I also use this code for
currency and date format change on form create:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   {$IFDEF
LINUX}   // CURRENCY    CurrencyString := 'R$';    CurrencyFormat :=
2;    DecimalSeparator := ',';    ThousandSeparator := '.';   // DATE 
  DateSeparator := '/';    ShortDateFormat := 'dd/mm/yyyy';  {$ENDIF}

----------------------------------------------------------------------
douglas.cast · 2016-05-18 00:18

Before some research, split and put this code in another place in my
application, i discover that the IF is causing some kind of
interference with the file stream for the image file, if i run the
code without the if statement to set the date like null i can use the
file stream, but if i turn the if on i need to turn off the file
stream, some ideias?

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i           
  if (Length(DateOfBirth.Text)=10) then begin                icdm.qry.
ParamByName('dateofbirth').AsDate:=StrToDate(DateOfBirth.Text); end  
          else begin               
icdm.qryInsertSheep.Params.ParamValues['dbdateofbirth']:=NullDate;
 end;     
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i           
  if opdUserImg.FileName <> '' then begin                 
 imgTemp:=TFileStream.Create(opdUserImg.FileName, fmOpenRead);       
          icdm.qrySQL.Clear;                 
 icdm.qrySQL.Text:='INSERT INTO userimage (id, imgid VALUES (:id,
:imgid)';                   icdm.qrySQL.Params[0].Name:='id';        
         icdm.qrySQL.Params[0].Value:=user;                 
 icdm.qrySQL.Params[1].Name:='imgid                  
icdm.qrySQL.Params[1].LoadFromStream(imgTemp, ftGraphic);            
     icdm.qrySQL.ExecSQL;                   imgTemp.Free;            
 end;   
One work fine without the other.

----------------------------------------------------------------------
balazsszekely · 2016-05-18 04:48

Quote@douglas.cast
Before some research, split and put this code in another place in my
application, i discover that the IF is causing some kind of
interference with the file stream for the image file, if i run the
code without the if statement to set the date like null i can use the
file stream, but if i turn the if on i need to turn off the file
stream, some ideias?Put a breakpoint to the following line:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   if
(Length(DateOfBirth.Text)=10) thenWhen the program breaks, press
F8(Step over) until you find the line which gives you the exception.
Make sure language exception is
checked(Tools-->Options-->Debugger-->Language Exceptions-->Notify on
Lazarus Exceptions). 
EOutOfMemory is most likely caused by the stream. What type is
dbuiimage? Blob?  Make sure you set imgTemp.Position to 0 before
LoadFromStream.

----------------------------------------------------------------------
douglas.cast · 2016-05-18 16:30

Quote from: GetMem on May 18, 2016, 06:48:25 amPut a breakpoint to the
following line:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   if
(Length(DateOfBirth.Text)=10) thenWhen the program breaks, press
F8(Step over) until you find the line which gives you the exception.
Make sure language exception is
checked(Tools-->Options-->Debugger-->Language Exceptions-->Notify on
Lazarus Exceptions). 
EOutOfMemory is most likely caused by the stream. What type is
dbuiimage? Blob?  Make sure you set imgTemp.Position to 0 before
LoadFromStream.

dbuiimage is a regular blobfield, not tiny or similar.

Inside of Tools-->Options-->Debugger-->Language Exceptions I have,
EAbort, ECodetoolError, EFOpenErro and Notify on Lazarus Exceptions
all checked.

I can't set the Break anyware on that line 'Error: BREAK not allowed',
tryied in different parts of the command and no sucess, but exit works
fine, I've tryed the exit in the date conversion (like the last
parameter) and no error reported in both cases.

If I set the position to 0 (before or after) the application just stop
when I click on the button, nothing happens before.

I've tryied another way, with memory stream and without any stream,
error is the same if I don't set a image on opd.

Before a showmessage (opd.filename) I got my home folder adress in
that field /users/doug, I'll try another if with another param to
check the error later (suggestions are welcome) to see what happens.

By some reason the code works if i fill all the fields, when I come
back I'll try the exit on all ifs that I have and see what I can find,
any ideas are welcome.