Switch to: V12V11V10V9V8V7V6V5

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

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