SOLVED where is the problem in my code,sql statements or the DB
connection
lazarus / 16-Databases

----------------------------------------------------------------------
yanes19 · 2013-05-21 21:51

Hi all of Lazarus community ;
first of all , I'm a Databases newbie my program uses a FireBird DB
the SELECT Statements works fine , But when I want to insert some data
it raises the exception :
"Operation cannot be performed on an inactive dataset "
here is a part of my code where the exception raises :
 Code: [Select]procedure TForm_main.BitBtn_save_ficheClick(Sender:
TObject);
begin
  SQLQuery1.Close;
  SQLQuery1.Insert;
  SQLQuery1.SQL.Text := 'INSERT INTO patients (ID_PATIENT
,TITRE_PATIENT ,NOM_PATIENT,PRENOM_PATIENT ,'+
       'DATE_NAISSANCE
,ETAT_CIVIL,PROFESSION,TELEPHONES,ADRESSE,NUM_SEC_SOCIALE,REFERE_PAR,'
+
       'MEDECIN_TRAITANT, VISITES_ANTERIEURES, DATE_VISITE,
M_CHRONIQUES, TRAITEMENTS_ANTERIEURS,' +
       'ANALYSES_MEDICALES, HOSPITALISATIONS, DIAGNOSTIC, ORDONNANCE,
AUTRE_NOTES, TAUX_PAYE) values '  +
       '(:ID_PATIENT ,:TITRE_PATIENT ,:NOM_PATIENT,:PRENOM_PATIENT
,:DATE_NAISSANCE,:ETAT_CIVIL,:PROFESSION, ' +
       ' :TELEPHONES,:ADRESSE , :NUM_SEC_SOCIALE, :REFERE_PAR,
:MEDECIN_TRAITANT,:VISITES_ANTERIEURES,'+
       ' :DATE_VISITE, :M_CHRONIQUES,
:TRAITEMENTS_ANTERIEURS,:ANALYSES_MEDICALES, :HOSPITALISATIONS, ' +
       ' :DIAGNOSTIC, :ORDONNANCE, :AUTRE_NOTES, :TAUX_PAYE );';

  sql_query_Params_assign ;
  
  IBConnection1.Connected := True;
  SQLTransaction1.Active := True;
  SQLQuery1.ExecSQL;
  Savechanges ;

end;
procedure TForm_main.sql_query_Params_assign ();
begin
   SQLQuery1.Params.ParamByName('ID_PATIENT').AsString :=
Edit_ID_PATIENT.Text;
   SQLQuery1.Params.ParamByName('TITRE_PATIENT').AsString :=
Combo_TITRE_PATIENT.Text;
   SQLQuery1.Params.ParamByName('NOM_PATIENT').AsString :=
Edit_NOM_PATIENT.Text;
   SQLQuery1.Params.ParamByName('PRENOM_PATIENT').AsString :=
Edit_PRENOM_PATIENT.Text;
   SQLQuery1.Params.ParamByName('DATE_NAISSANCE').AsDate :=
DateEdit_BIRTH.Date;
   SQLQuery1.Params.ParamByName('ETAT_CIVIL').AsString :=
Combo_ETAT_CIVIL.Text;
   SQLQuery1.Params.ParamByName('PROFESSION').AsString :=
Edit_PROFESSION.Text;
   SQLQuery1.Params.ParamByName('TELEPHONES').AsString :=
Edit_TELEPHONES.Text;
   SQLQuery1.Params.ParamByName('ADRESSE').AsString :=
Edit_ADRESS.Text;
   SQLQuery1.Params.ParamByName('NUM_SEC_SOCIALE').AsString :=
Edit_NUM_SEC_SOCIALE.Text;
   SQLQuery1.Params.ParamByName('ID_PATIENT').AsDate :=
DateEdit_DATE_VISITE.Date;
   SQLQuery1.Params.ParamByName('REFERE_PAR').AsString :=
Edit_REFERE_PAR.Text;
   SQLQuery1.Params.ParamByName('MEDECIN_TRAITANT').AsString :=
Edit_MEDECIN_TRAITANT.Text;
   SQLQuery1.Params.ParamByName('VISITES_ANTERIEURES').AsString :=
Edit_VISITES_ANTERIEURES.Text;
   SQLQuery1.Params.ParamByName('M_CHRONIQUES').AsString :=
Edit_M_CHRONIQUES.Text;
   SQLQuery1.Params.ParamByName('TRAITEMENTS_ANTERIEURS').AsString :=
Edit_TRAITEMENTS_ANTERIEURS.Text;
   SQLQuery1.Params.ParamByName('ANALYSES_MEDICALES').AsString :=
Edit_ANALYSES_MEDICALES.Text;
   SQLQuery1.Params.ParamByName('HOSPITALISATIONS').AsString :=
Edit_HOSPITALISATIONS.Text;
   SQLQuery1.Params.ParamByName('DIAGNOSTIC').AsString :=
Edit_DIAGNOSTIC.Text;
   SQLQuery1.Params.ParamByName('ORDONNANCE').AsString :=
Edit_ORDONNANCE.Text;
   SQLQuery1.Params.ParamByName('AUTRE_NOTES').AsString :=
Edit_AUTRE_NOTES.Text;
   SQLQuery1.Params.ParamByName('TAUX_PAYE').AsInteger :=
StrToInt(Edit_TAUX_PAYE.Text);

end;                                     

Is it possible that the error is caused by Bad use of parametrized
query ? 
Please help me !

----------------------------------------------------------------------
taazz · 2013-05-22 03:30

Since the sql is not select but insert you do not have to call the
insert method at all. You simple set the params and then call the
execute method. So simple remove the line SQLQuery1.Insert;

----------------------------------------------------------------------
exdatis · 2013-05-22 05:24

See:
http://wiki.freepascal.org/SQLdb_Tutorial1
part of: http://wiki.freepascal.org/Working_With_TSQLQuery
"TSQLQuery is an object that can embody a dataset coming from a
database (RDBMS that uses SQL, such as Firebird, MS SQL Server,
Oracle...). Using a SELECT SQL statement in the TSQLQuery's SQL
property, you can determine what data is retrieved from the database
into the dataset. When the dataset is changed by the program (or
user), the changes can be submitted back to the database.

A TSQLQuery can also be used to directly modify data: if you specify
the desired INSERT, UPDATE, DELETE etc SQL statement in the SQL
property and call the ExecSQL method of the TSQLQuery, the query
object will send the SQL to the database without retrieving any
results. "
Regards
p.s. Also there is possibility to execute direct(db-connection)
see: http://wiki.freepascal.org/SqlDBHowto#How_to_execute_direct_queri
es.2Fmake_a_table.3F

----------------------------------------------------------------------
yanes19 · 2013-05-22 08:46

Thank-you for your time ,
@ taazz : the line with the call to Query's Insert() method have no
effect if removed or left as is 
but it's good to right a correct code

@exdatis : I read the 3 tuts "SQLdb_Tutorial 1 , 2,3 and the other
tuts in the wiki but no chance ,
BUT FIRST , is my SQL statement is correct ?
my parameters assignment is it also correct ?
sorry for my bad english

----------------------------------------------------------------------
Lacak2 · 2013-05-22 12:57

Quote from: yanes19 on May 22, 2013, 10:46:55 amBUT FIRST , is my SQL
statement is correct ?
At first look it seems be ok (";" at end of SQL statement is not
required)

Quote from: yanes19 on May 22, 2013, 10:46:55 ammy parameters
assignment is it also correct ?
yes seems ok

On which line your error pop-ups?

----------------------------------------------------------------------
yanes19 · 2013-05-22 16:29

Thank you  @ Lacak2 ,
The problem now is in the Savechanges() procedure  , 
the exception raises when executing line
:QuoteSQLQuery1.ApplyUpdates;Code: [Select]procedure
TForm_main.Savechanges;
// Saves edits done by user,
begin
  try
    if SQLTransaction1.Active then
    begin
      SQLQuery1.ApplyUpdates;
      SQLTransaction1.Commit;
      //SQLTransaction1.Active now is false
    end;
  except
//..........
  end;
end;             

if I keep the line QuoteSQLQuery1.Close;It raises :"""Operation cannot
be performed on an inactive dataset" ""
but when I comment it , it raises """Operation cannot be performed on
an active dataset" ""
what happened ?

----------------------------------------------------------------------
yanes19 · 2013-05-22 19:18

It's Ok now , it was solved simply by commenting
the line "SQLQuery1.ApplyUpdates;"
The insert statement works fine now ,
But anyone can tell me why '.ApplyUpdates' have to be removed ?

THANKYOU ALL

----------------------------------------------------------------------
Lacak2 · 2013-05-23 05:25

Intention of ApplyUpdates is send local updates , which was done in
local dataset to "database server".
Local updates = SQLQuery1.Insert, Append, Edit ... SQLQuery1.Post or
SQLQuery1.Delete
ApplyUpdates then propagates these local updates to "databese server"
... either uses generated SQL statements (INSERT, UPDATE, DELETE) or
uses provided in InsertSQL, UpdateSQL, DeleteSQL properties of
TSQLQuery.