Inserting a record in sybase db table using stored procedure - delphi programming

Question

I am new at programming with delphi. I am currently creating a simple notebook program and i need some help. I have a form called contacts with 5 tEdit fields. I am thinking i could create a stored procedure in my sybase database to insert record into Contacts table, so I can call it with my delphi programm. How do I call this procedure in delphi? the values that will be inserted should be taken from users input into these tEdit fields. Anyone has any suggestions? Or am I thinking the wrong way? thanks in advance

Answer 1

You have several options here, and it will depend on what VCL controls you are using. (1). You can insert via a tTable component. This let's you have a quick, easy, low level control. You drop the component on the form, set the component properties (tablename, etc), then something like

MyTable.Open;
MyTable.Insert; (or maybe append)
MyTable.FieldByName('MY_FIELD').AsString := 'Bob'; // set the field values
MyTable.post;

(2). Use SQL. Drop a SQL component on the form. Set the SQLText property, using parameters; for example : "Insert into table (MyField) values :X". My opinion is that this is easier to do in complex situations, correlated subselects, etc.

MySQL.Close;
MySQL.ParamByName('X').AsString := 'BOB';
ExecSQL;

(3). Use stored procedures. - The advantage to this is that they are useable by multiple applications, and can be changed easily. If you want to update the SQL code, you update it once (in the database), versus having to change it in an app, and then distribute the app to multiple users.

The code for this will be nearly identify to (2), although I don't know the specifics of your VCL library. In effect though, you will specify the routine to run, specify the parameter values, and then execute the stored procedure.

Note that all these routines will return an error code or exception code. It is best practice to always check for that...

Here is a little more complex example, using a SQL statement called qLoader. qLoader exists on a datamodule. I am passing a parameter, executing the SQL statement, then iterating through all the results.

  try
  with dmXLate.qLoader do
     begin

     Close;
     ParamByName('DBTYPE').AsString := DBType;
     Open;

     while not dmXLate.qLoader.Eof do
         begin
         // Here is where we process each result
         UserName:= dmXLate.qLoader.FieldByName('USERNAME').AsString;    

         dmXLate.qLoader.Next;
         end;


      end;

except
      on E: Exception do
      begin
        ShowMEssage(E.Message);
        exit;
      end;
    end;