What are the advantages and disadvantages of using bind variables in SQLExec statements?

Summary:

What are the advantages and disadvantages of using bind variables in SQLExec statements?

Full Article:

Answer1

Bind variables are used to pass variables to the SQLExec statement. If you do not use bind variables you will have to hard code the values in the statement.

For example, if you were doing your timesheet, the system knows who you are (eg; %UserID). So maybe you want to know the email address of the “%userid”. How would you pull the email address back from PSOprDefn table? SQLExec(“SELECT A.EMAILID FROM PSORPDEFN A WHERE A.OPRID = :1”, %UserID);

Answer2

The simplest explanation I can think of would be, by using the bind variable, you eliminate the possibility of introducing bad values to the SQL you are trying to construct. By using the bind variable, you are very much aware of what value goes into it.

Answer3

Not only is it easier to use bind variables instead of concatenating the SQL yourself in code, using bind variables is a better performance option on most databases. When a statement uses bind variables instead of ‘hardcoded’ values, it can be stored in the DB cache and the SQL does not have to be parsed again.

Answer4

It is much better for the performance in general when you use bind variables instead of building a complete sentence and then executing it. When you use bind variables, you do not need to specify the data type of each of them.

As for disadvantages, one could be that you have to be careful with the length of the sentences, but, in general, I have not had any problem with the bind variables.

*Questions excerpted from ITToolBox.com*

Disclaimer: Contents are not reviewed for correctness and are not endorsed or recommended by PeopleSoft-Planet.com.

Write your comment