Making an SQL call
- The basic structure of a call to SQL in Xbasic is:
- Declare the existence of an SQL connection.
- Indicate the connection string.
- Execute the connection string to make the SQL call:
- Open the connection.
- Execute the SQL statement.
- If applicable, retrieve the result set.
- If applicable, release the result set.
- Close the connection.
You will see more examples of how Xbasic interacts with the SQL namespace via variables that use namespace structures as their types.
Along with the instructor, define the basic structure of an SQL call.
- After the arrayCount variable declaration, on a new line, enter the following text:
dim cnx as sql::Connection ‘This is the connection.
Observe the command prompting that displays available SQL methods.
- Make a new line after that, and enter the following text on two lines:
dim cnxStr as c 'The connection string
(new line) cnxStr = "::name::conn"
(conn is the connection string defined in the Workspace.)
- This function will also need a variable to hold the SQL result set. Make another new line, and enter the following text (all on one line):
dim results as sql::ResultSet 'Use the SQL namespace ResultSet structure as var type
- Executing the SQL statements occurs after assigning them to the Character array. After the second SQL statement, make a new line.
- Open the connection. On the new line you just made, enter the following text:
cnx.Open(cnxStr) (Open the connection using the connection string.)
- Then, execute the SQL statement. Make another new line, and enter the following text:
cnx.Execute(sqlStmts[i],args) (Execute the SQL statement in an element of the array, using the arguments.)
- The SQL statement returns a result set, so assign the result set to the corresponding variable defined earlier. Make another new line, and enter the following text:
results = cnx.ResultSet
- After retrieving the result set, it is good practice to release the result set as soon as possible so that there is no lock on the table. Enter the following text on a new line:
- After releasing the result set, close the connection. Enter the following text on a new line:
Stepping through the array
We need to execute several SQL statements that we stored in an array. To do that, we need to examine each element of the array. How many elements were filled?
- To get the count of filled elements, above the line for opening the connection, make a new line and enter arrayCount = sqlStmts.size()
- Define a for loop to execute each statement in turn: below the line for opening the connection, make a new line and enter for i = 1 to arrayCount
- The end of the loop comes after releasing the result set. Make a new line and enter
This part of your function should look like this:
- Save the function.