Table of Contents
VSqlStatement Class: SQL Methods
Description:
After you got the VSqlStatement object and optionally set binding you can execute this statement.
VSqlStatement.SqlSelect()
SqlSelect( inCursorLocation as EVCursorLocation = kClientSide, inLockType as EVLockType = kReadOnly, inCursorDirection as EVCursorDirection = kForwardOnly ) As VCursor
Parameters:
- inCursorLocation - The location of cusror.
- inLockType - The lock type for records of a cursor.
- inCursorDirection - The direction of a cursor.
Description:
SqlSelect() method returns the resulting table as a cursor of type VCursor. SqlSelect() should be used with SQL commands which produce cursor as a result. These are: SELECT statement, SHOW statements, CALL procedure that returns a cursor.
Note: When finished with a cursor, you should assign it the value nil to destroy it and free memory.
The optional parameters inCursorLocation, inLockType, inCursorDirection allows you to control the behavior of the cursor. See the documentation on Valentina Kernel and VServer for more details about record locks.
You can set the following parameters with these values:
inCursorLocation: * kClientSide = 1 * kServerSide = 2 * kServerSideBulk = 3 inLockType: * kNoLocks = 1 * kReadOnly = 2 * kReadWrite = 3 inCursorDirection: * kForwardOnly = 1 * kRandom = 2
By default these parameters get the following values:
kClientSide, kReadOnly, kForwardOnly
See Also
Example:
dim stmt as VSqlStatement dim curs as VCursor stmt = db.CreateSqlStatement( "SELECT * FROM T" ) curs = stmt.SqlSelect()
Example:
stmt = db.CreateSqlStatement( "SELECT * FROM T" )
curs = db.SqlSelect( EVCursorLocation.kServerSide,
EVLockType.kReadWrite,
EVCursorDirection.kRandom )
Example:
stmt = db.CreateSqlStatement( "SELECT * FROM T WHERE f1 = :1, f2 > :2" ) stmt.Bind_string(0, "john") stmt.Bind_vint32(1, 25) curs = db.SqlSelect( EVCursorLocation.kServerSide, EVLockType.kReadWrite, EVCursorDirection.kRandom )
VSqlStatement.SqlExecute()
Declaration:
SqlExecute() as Integer
Description:
You can use this function to execute any SQL command supported by Valentina except for a command that returns a cursor as a result (e.g. SELECT). This is fully covered in the documentation on ValentinaSQL.
This returns the number of affected rows.
See Also
Example:
dim stmt as VSqlStatement stmt = db.CreateSqlStatement( "UPDATE person SET name = 'john' WHERE name = 'jehn'" ) recCount = stmt.SQLExecute()
Example:
dim stmt as VSqlStatement stmt = db.CreateSqlStatement( "UPDATE person SET name = :1 WHERE name = :2" ) stmt.Bind_string(0,'john') stmt.Bind_string(1,'jehn') recCount = stmt.SQLExecute()
VSqlStatement.SqlQuery()
Declaration:
SqlQuery( inCursorLocation as EVCursorLocation = kClientSide, inLockType as EVLockType = kReadOnly, inCursorDirection as EVCursorDirection = kForwardOnly ) As VQueryResult
Parameters:
- inCursorLocation - The location of cusror.
- inLockType -The lock type for records of a cursor.
- inCursorDirection - The direction of a cursor.
Description:
SqlQuery() method is very similar to SqlSelect() by syntax, so see the description of parameters in that method. The difference is that SqlQuery() is able to accept any SQL command, i.e. it is a combination of both SqlExecute() and SqlSelect() methods.
As result SqlQuery() returns VQueryResult - a small class, which is able to keep any result of any Valentina SQL command.
This command can be useful if you should be able to accept any SQL command and you don't know what exactly this command is. For example if user type SQL query self or if you get SQL command from some file.
See Also
- VSqlStatement.SqlSelect()
- VQueryResult
Example:
dim stmt as VSqlStatement dim res as VQueryResult dim curs as VCursor stmt = db.CreateSqlStatement( strAnySqlCommand ) res = stmt.SqlQuery( strAnySqlCommand ) if res.type = EVQueryType.kCursor then curs = res.Cursor end if