lrd_save_value

Saves the value of a placeholder descriptor.

LRDRET lrd_save_value( LRD_VAR_DESC *mptVarDesc, unsigned long muIiIndex, unsigned long muliOption, char *mpszParamName );

The lrd_save_value function saves the dynamic value of a placeholder descriptor. This function is used for Correlating Database Statements that set output placeholders (such as certain stored procedures on Oracle). This dynamically saved value can be used by other database statements within the same script. Note that lrd_save_value always follows an lrd_exec statement.

In instances where table columns have constraints such as Unique, correlating your script's queries may be the only way to run the script. For example, if during recording, you inserted a value into a table with a Unique key constraint, you will be unable to insert the same value again. If you try to run the script as recorded, you will get a database error.

For more details refer to the Function Header File lrd.h in the include directory.

Return Values

See LRD Return Values.

Parameterization

You cannot use standard parameterization for any arguments in this function.

Example

Below are two examples for lrd_save_value. Example 1 shows reuse of an ID. Example 2 shows running a script where there is a Unique constraint.

lrd_save_value example 1

In the following example, a user performed a stored routine, get_emp_id, to get the id of an employee named John. Using lrd_assign_bind, the id is assigned to the placeholder, id. In the modified script, lrd_save_value saves the value of the placeholder descriptor to the parameter emp_id. The value of the placeholder is saved dynamically during each script run, and used in the second query.

/* Recorded script */

lrd_stmt(Csr1, "begin :id=get_emp_id('john') end", ...);
                lrd_bind_col(Csr1, 1, &ID_D1, 0, 0);
                lrd_bind_placeholder(Csr1, "id", &ID_D1, 0, 0);
                lrd_exec(Csr1, ...);
                lrd_stmt(Csr1, "select salary from payment where id=:id",...);
                lrd_assign_bind(Csr1, "id", "777", ...);
                lrd_exec(Csr1, ...);
                 /* Modified script */
                lrd_stmt(Csr1, "begin :id=get_emp_id('john') end", ...);
                lrd_bind_placeholder(Csr1, "id", &ID_D1, 0, 0);
                lrd_exec(Csr1, ...);
                lrd_save_value(&ID_D1, 0, 0, "emp_id");
                lrd_stmt(Csr1, "select salary from payment where id=:id",...);
                lrd_assign_bind(Csr1, "id", "<emp_id>", ...);
                lrd_exec(Csr1, ...);

lrd_save_value example 2

The following example illustrates a query with a Unique constraint using lrd_save_col and lrd_save_value. A company's personnel department wants to insert a new record into the employee database. The new employee is assigned the next available ID number. Two employees cannot have the same ID number—ID numbers must be unique.

To determine the next available number, the operator performs a query for the number of records in the employees table. The new employee number, is one more than the total number of records. In the example below, the query returned a count of 4 records and therefore assigned the new employee an ID of 5, or newemp=5. The table's columns are Name, ID, DOB, Dept, and DeptNo.

lrd_stmt(Csr2, "select count(*) from employees", -1, 1 /*Deferred*/,.. 2 /*Ora V7*/, 0);
                lrd_bind_col(Csr2, 1, &COUNT_D1, 0, 0);
                lrd_exec(Csr2, 0, 0, 0, 0, 0);
                lrd_fetch(Csr2, 1, 1, 0, PrintRow2, 0);
                lrd_stmt(Csr2, "\t\tdeclare\n \tPROCEDURE new_emp (empno in integer, "
                    "newemp out integer) is\n\t\tbegin\n\t\t\tnewemp:=empno+1;\n\t\tend "
                    "new_emp;\n\t begin\n\t\t\tnew_emp(:empno,:newemp);\n\t\tend;", -1, -1 
                    /*Deferred*/, 2 /*Ora V7*/, 0);
                lrd_assign_bind(Csr2, "empno", "4", &empno_D2, 0, 0, 0);
                lrd_assign_bind(Csr2, "newemp", "0", &newemp_D3, 0, 0, 0);
                lrd_exec(Csr2, 0, 0, 0, 0, 0);
                lrd_stmt(Csr2, "insert into employees values ('TOM JONES',5,"
                        "'1-JAN-97','R&D',22)", -1, 0 /*Non deferred*/, 2 /*Ora V7*/, 0);
                lrd_assign_bind(Csr2, "newemp", "5", &newemp_D4, 0, 0, 0);
                lrd_exec(Csr2, 0, 0, 0, 0, 0);
					

If you repeat the recorded query in its current form, the script would attempt to insert the new employee with the same employee number, 5. The script would fail, since the empno column has a Unique constraint. To enable the script to run, you save the current number of employees dynamically to a parameter using lrd_save_col before the fetch statement.

The following changes, indicated in bold, were made to the above script:

/* Retrieve the number of records in the employees table */
                lrd_stmt(Csr1, "select count(*) from employees", -1, 1 /*Deferred*/, 2 /*Ora V7*/, 0);
                lrd_bind_col(Csr1, 1, &COUNT_D1, 0, 0);
                lrd_exec(Csr1, 0, 0, 0, 0, 0);
                lrd_save_col(Csr1, 1, 1, 0, "row_cnt");
                lrd_fetch(Csr1, 1, 1, 0, PrintRow2, 0);
					

After saving the count value from the first fetch, the script derives the next value with a stored procedure, using the formula newemp=empno+1. The placeholder descriptor, newemp, receives this new value when the stored procedure is executed.

To use the newly assigned value of the placeholder, you save it to a variable using the lrd_save_value function. The script below saves the result of the stored procedure to the new_no variable. The new value is inserted into the table with the rest of the elements of the employee record.

/* Define next employee's number: newemp = empno +1 */
                lrd_stmt(Csr1, "\t\tdeclare\n \tPROCEDURE new_emp (empno in integer, "
                    "newemp out integer) is\n\t\tbegin\n\t\t\tnewemp:=empno+1;\n\t\tend "
                    "new_emp;\n\t begin\n\t\t\tnew_emp(:empno,:newemp);\n\t\tend;", -1, -1
                     /*Deferred*/, 2 /*Ora V7*/, 0);
                /* Bind the placeholder values to the variables. */
                lrd_assign_bind(Csr1, "empno", "<row_cnt>", &empno_D2, 0, 0, 0);
                lrd_assign_bind(Csr1, "newemp", "0", &newemp_D3, 0, 0, 0);
                lrd_exec(Csr1, 0, 0, 0, 0, 0);
                lrd_save_value(&newemp_D3, 0, 0, "new_no");
                lrd_stmt(Csr2, "insert into employees values ('TOM JONES',:newemp,"
                        "'1-JAN-97','R&D',22)", -1, 0 /*Non deferred*/, 2 /*Ora V7*/, 0);
                lrd_assign_bind(Csr2, "newemp", "<new_no>", &newemp_D4, 0, 0, 0); 
                lrd_exec(Csr2, 0, 0, 0, 0, 0);