Share this page

# Example: lrd_save_value

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);

Tips to improve your search results... VuGen Function Reference Version 25.1-25.3 Function Reference 25.1-25.3 All View Local Help Center View Online Help Center Version: