整理一下SC裡面關於SQL的指令，自寫程式時使用

整理 SC 裡面，自訂程式時，如何使用 SC提供的SQL指令

一、關於 connection：

1. 使用SC Project 設定現成的 Connection

2. sc_connection_edit("Connetion_Name", $arr_conn)

1º Parameter: Connection name to be edited.

2º Parameter: Array of items containing the connection information to be edited. Check out the indices of the array:

Indice	Description	Example
['server']	Database server (host)	$arr_conn['server'] = "127.0.0.1"
['user']	Database username	$arr_conn['user'] = "root"
['password']	Database password	$arr_conn['password'] = "secretpass123"
['database']	Database name used in the connection	$arr_conn['database'] = "sc_samples"
['persistent']	Defines if the connection is persistent or not	$arr_conn['persistent'] = "Y" / "N"
['encoding']	Configure the connection encoding	$arr_conn['encoding'] = "utf8"

Note:Is not required to use all the indices in the array, we can pass only the required ones.

Example:

$arr_conn = array();

$arr_conn['user'] = "admin2";
$arr_conn['password'] = "admin2pass";
$arr_conn['database'] = "sc_samples2";

sc_connection_edit("sc_connection", $arr_conn);

3. sc_connection_new("Connection_Name", $arr_conn)

The new connection will only be available in the next application.

1º Parameter: Connection name.

Note: If there is a connection created within the Scriptcase with the same name, this macro has no effect. Connections created within the Scriptcase prevail. If you want to edit an existing connection, see the documentation for the macro sc_connection_edit.

2º Parameter: Array of items containing the connection information. Check out the indices of the array:

 

Indice	Description	Example
['drive']	Driver of the database used for the connection (see table below)	$arr_conn['drive'] = "oci8"
['server']	Database server (host)	$arr_conn['server'] = "127.0.0.1"
['user']	Database username	$arr_conn['user'] = "root"
['password']	Database password	$arr_conn['password'] = "secretpass123"
['database']	Database name used in the connection	$arr_conn['database'] = "sc_samples"
['persistent']	Defines if the connection is persistent or not	$arr_conn['persistent'] = "Y" / "N"
['encoding']	Configure the connection encoding	$arr_conn['encoding'] = "utf8"

Note: It is required that all items are filled, with the exception of items ['persistent'] and ['encoding'].

Example:

$arr_conn = array();

$arr_conn['drive'] = "mysqlt";
$arr_conn['server'] = "127.0.0.1";
$arr_conn['user'] = "root";
$arr_conn['password'] = "pass123";
$arr_conn['database'] = "sc_samples";
$arr_conn['persistent'] = "Y";
$arr_conn['encoding'] = "utf8";

sc_connection_new("new_conn_mysql", $arr_conn);

二、SQL SELECT

sc_select (dataset, "SQL Command", "Connection")

sc_lookup (Dataset, "SQL Command", "Connection")

sc_select(dataset, "SQL Command", "Connection")

This macro executes the SQL commands passed as parameter and access the "dataset" in the command.

Different from sc_lookup macro, this macro doesn't manipulate the dataset (the user is responsible for all the manipulation).

If an error occurs in the sql command execution, the variable attributed to the database returns as "false" and the error message is available in the "dataset_error" variable.

The connection parameter is optional, use only if the command is executed in a data base different from the specified in the application. In this parameter it is not possible to use variables.

Ex. 1:
sc_select(my_data, "select clientId, clientName, limitecred from costumers");
if ({my_data} === false) {
    echo "Access error. Message =". {my_data_erro};
} else {
    while (!{my_data}->EOF) {
          {clientName} = {my_data}->fields[1];
          {my_data}->MoveNext();
     }
    {my_data}->Close();
}



Ex. 2: The SQL command can passed as application fields (local variables) or of global variables.
sc_select(dataset,"select price order from order where clientId = '{clientId}' and cod_Seller = [var_glo_seller]");

Note: The command must always be finished with semicolon";".

sc_lookup(Dataset, "SQL Command", "Connection")

This macro allows the user to execute SQL commands and returns the result to the "dataset" variable. The "dataset" structure is an array (line/column).

The "connection" parameter is optional. Use when the command is executed in a database different from that specified for the application.
Note: The connection parameter does not accept variable. You must enter the name of the connection that the SQL command will execute.

Ex. 1:
sc_lookup(dataset, "select customer_id, customer_name, credit_limit from customers" );

To have access to the first line (Dataset), use :
{customer_id} = {dataset[0][0]};
{customer_name} = {dataset[0][1]};
{credit_limit } = {dataset[0][2]};

To have access to the second line (Dataset), use:
{customer_id} = {dataset[1][0]};
{customer_name} = {dataset[1][1]};
{credit_limit} = {dataset[1][2]};

If occurs error in the execution of the SQL command, the variable attributed to the dataset will return as "false" and the error message will be available in the "dataset_error" variable. It is also important to verify the select returned data, to prevent access to non-existent variables, once the output array only will be created if the select command returns data.

Ex. 2:
sc_lookup(my_data, "select customer_id, customer_name, credit_limit from customers");
if ({my_data} === false)
{
echo "Access error. Message=". {my_data_error} ;
}
elseif (empty({my_data}))
{
echo "Select command didn't return data";
}
else
{
{customer_id} = {my_data[0][0]};
{customer_name} = {my_data[0][1]};
{credit_limit} = {my_data[0][2]};
}



Ex. 3: The SQL command also can be composed of application fields (local variables) or of global variables:
sc_lookup(dataset, "select order_value from orders where clientid = '{customer_id} ' and salesman_id = [var_glo_salesman]");

Note: The command must always be finished with a semicolon ";".

Note2: For a big result returned in the dataset we recommend the use of the macro sc_select instead of this one.

sc_sql_injection ({My_Field}) or ($My_Variable)

sc_sql_protect (Value, "Type", "Connection")