|
|
|
It is not unusual for an application to execute the same command on a database repeatedly, varying only the value or values used in the command. SQL database developers often use parameterized commands to code redundant commands, typically, commands whose input values come from user input. Parameterized commands may be the only way long data such as LVARCHAR and LVARBINARY can be queried, inserted or updated.
FairCom DB SQL supports both named and unnamed parameters, however, not in mixed usage. FairCom DB SQL accepts as parameter marks a question mark (?) or a name starting with a colon (:name). With unnamed parameters, the order in which parameters are used in the FairCom DB SQL command must be respected when setting parameter values and properties. With named parameters, you are free to use any order. The following are examples of valid parameters:
insert into tab2 values (?)
or
insert into tab2 values (:name)
The following examples show how to add data to a table using parameters.
.NET VB Example
Sub AddName()
Dim conString As String = “User=ADMIN;Password=ADMIN;Database=ctreeSQL”
Dim hConnection As New CtreeSqlConnection(conString)
Dim hCommand As New CtreeSqlCommand(hConnection)
hConnection.Open()
hCommand.CommandText = “create table tab2 (name char(20), age integer)”
hCommand.ExecuteNonQuery()
hCommand.Parameters.Add(“name”, CtreeSqlType.Char)
hCommand.Parameters.Add(“age”, CtreeSqlType.Integer)
hCommand.CommandText = “insert into tab2 values (:name, :age)”
hCommand.Parameters[“name”].Value = “Mary”
hCommand.Parameters[“age”].Value = 1
hCommand.ExecuteNonQuery()
hConnection.Close()
End Sub
.NET C# Example
void CreateTable()
{
String conString = “User=ADMIN;Password=ADMIN;Database=ctreeSQL”;
CtreeSqlConnection hConnection = new CtreeSqlConnection(conString);
CtreeSqlCommand hCommand = new CtreeSqlCommand(hConnection);
hConnection.Open();
hCommand.CommandText = “create table tab1 (name char(20), age integer)”;
hCommand.ExecuteNonQuery();
hCommand.Parameters.Add(“name”, CtreeSqlType.Char);
hCommand.Parameters.Add(“age”, CtreeSqlType.Integer);
hCommand.CommandText = “insert into tab2 values (:name, :age)”;
hCommand.Parameters[“name”].Value = “Mary”;
hCommand.Parameters[“age”].Value = 1;
hCommand.ExecuteNonQuery();
hConnection.Close();
}
.NET C++ Example
void CreateTable()
{
String* conString = “User=ADMIN;Password=ADMIN;Database=ctreeSQL”;
CtreeSqlConnection* hConnection = new CtreeSqlConnection(conString);
CtreeSqlCommand* hCommand = new CtreeSqlCommand(hConnection);
hConnection->Open();
hCommand->CommandText = “create table tab1 (name char(20), age integer)”;
hCommand->ExecuteNonQuery();
hCommand->Parameters->Add(“name”, CtreeSqlType::Char);
hCommand->Parameters->Add(“age”, CtreeSqlType::Integer);
hCommand->CommandText = “insert into tab2 values (:name, :age)”;
hCommand->Parameters[“name”]->Value = “Mary”;
hCommand->Parameters[“age”]->Value = 1;
hCommand->ExecuteNonQuery();
hConnection->Close();
.NET Delphi Example
procedure AddName
var
conString : String;
hConnection : CtreeSqlConnection;
hCommand : CtreeSqlCommand;
begin
conString := 'User=ADMIN;Password=ADMIN;Database=ctreeSQL';
hConnection := CtreeSqlConnection.Create(conString);
hCommand := CtreeSqlCommand(hConnection);
hConnection.Open();
hCommand.CommandRText := 'create table tab2 (name char(20), age integer);
hCommand.ExecuteNonQuery();
hCommand.Parameters.Add('name', CtreeSqlType.Char);
hCommand.Parameters.Add('age', CtreeSqlType.Integer);
hCommand.CommandText := 'insert into tab2 values (:name, :age)';
hCommand.Parameters['name'].Value := 'Mary';
hCommand.Parameters['age'].Value := 1;
hCommand.ExecuteNonQuery();
hConnection.Close();
end;
