Example of SQLInsert, SQLUpdate and SQLDelete requested

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
bernr
Posts: 17
Joined: Fri 14 Dec 2007 22:07

Example of SQLInsert, SQLUpdate and SQLDelete requested

Post by bernr » Thu 28 Aug 2008 23:59

I was wondering if someone could give me an example on how to use SQLInsert, SQLUpdate and SQLDelete. when using TMSQuery.

Thank You.

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Mon 01 Sep 2008 07:11

You can find the detailed description of its properties in the MyDAC help.

bernr
Posts: 17
Joined: Fri 14 Dec 2007 22:07

Post by bernr » Tue 02 Sep 2008 02:50

I already read the help, all it says is
property SQLInsert: TStrings;

Description
Use SQLInsert property to specify SQL statement that will be used when applying an insertion to a dataset. Statements can be parameterized queries. Names of the parameters should be the same as field names. Parameters prefixed with OLD_ allow to use current values of fields prior to actual operation.

Use TCustomMSDataSet.Options.ReturnParams to return OUT parameters back to dataset.

To create SQLInsert statement at design time, use query statements editor.

Note:
If you specify SQLInsert not depending onTCustomMSDataSet.Options.QueryIdentity the value of Identity filed won't be returned on execution Insert(Append)..Post.To avoid the problem, you should add following code to the end of SQLInsert:

SQL Server 2000:
SET : = SCOPE_IDENTITY()

SQL Server 7:
SET : = @@Identity

- name of Identity field.

Also you should set type of the last parameter to ptInputOutput for
TCustomDADataSet.BeforeUpdateExecute method:

if stInsert in StatementTypes then
Params[Params.Count - 1].ParamType := ptInputOutput;

Example

INSERT INTO Orders
(Shipname)
VALUES
(:Shipname)

See Also
SQLUpdate
SQLDelete
This does tell my how to use it. I set up the SQLInsert, but now how do I call it using a TMSQuery? Some code example would be nice.

Thanks Bern[/quote]

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Wed 03 Sep 2008 14:15

You can not call SQLInsert query directly. SQLInsert is an SQL statement that is been called automatically when applying an insertion to a dataset.

For example, you have the following SQL query:

Code: Select all

MyQuery.SQL.Text := 'SELECT * FROM TableName';
Then when you use Insert .. Post, like this:

Code: Select all

MyQuery.Insert;
MyQuery.FieldByName('FieldName').AsInteger := 1;
MyQuery.Post;
when the Post method is called the SQLInsert query will be executed.

Post Reply