VDatabase Class: SQL Methods

Declaration:

SqlExecute( 
    inQuery as String, 
    inBinds() as StringOrVariant ) as Integer

Parameters:

• inQuery - The SQL string of a query.
• inBinds - The array of bind parameters. Usually this is array of Strings or array of VARIANTs. See API Reference specific to your language.

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.

For commands that have an EXPR (expression) clause in the syntax, you can define an array of bind parameters. Such commands usually are INSERT, DELETE, UPDATE.

See Also

• Valentina SQL Reference

Example:

recCount = db.SQLExecute( "UPDATE person SET name = 'john' 
                           WHERE name = 'jehn'" )

Example:

dim Binds(-1) as String
 
Binds.append 'john'
Binds.append 'jehn'
 
recCount = db.SQLExecute( 
                 "UPDATE person SET name = :1 WHERE name = :2", Binds )

Example:

// the same as above but more concise
recCount = db.SQLExecute( 
                 "UPDATE person SET name = :1 WHERE name = :2", 
                 Array( "john", "jehn" ) )

Declaration:

SqlSelect( 
    inQuery as String, 
    inCursorLocation as EVCursorLocation = kClientSide, 
    inLockType as EVLockType = kReadOnly, 
    inCursorDirection as EVCursorDirection = kForwardOnly
    inBinds() as String = nil ) As VCursor

Parameters:

• inQuery - The SQL string of a query.
• inCursorLocation - The location of cusror.
• inLockType - The lock type for records of a cursor.
• inCursorDirection - The direction of a cursor.
• inBinds - The array of bind parameters. Usually this is array of Strings or array of VARIANTs. See API Reference specific to your language.

Description:

SqlSelect() method gets an SQL query as the string parameter, resolves it, then returns the resulting table as a cursor of type VCursor. SqlSelect() should be used with SQL commands that produce cursor as result. These are: SELECT statement, SHOW statements, CALL procedure that returns a cursor.

Note: When finished with a cursor, you must assign it the value nil to destroy it and free memory.

The optional parameters inCursorLocation, inLockType, inCursorDirection allow 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

NOTE: For commands that have an EXPR (expression) clause in the syntax, you can define an array of bind parameters.

See Also

• Valentina SQL Reference

Example:

dim curs as VCursor
curs = db.SqlSelect( "SELECT * FROM T" )

Example:

curs = db.SqlSelect( "SELECT * FROM T ",  
                        EVCursorLocation.kServerSide, 
                         EVLockType.kReadWrite, 
                         EVCursorDirection.kRandom )

Example:

curs = db.SqlSelect( "SELECT * FROM T WHERE f1 = :1, f2 > :2",
                         EVCursorLocation.kServerSide,
                         EVLockType.kReadWrite, 
                         EVCursorDirection.kRandom,	
                         Array("john", "25" ) )

Declaration:

SqlQuery( 
    inQuery as String, 
    inCursorLocation as EVCursorLocation = kClientSide, 
    inLockType as EVLockType = kReadOnly, 
    inCursorDirection as EVCursorDirection = kForwardOnly
    inBinds() as String = nil ) As VQueryResult

Parameters:

• inQuery - The SQL string of a query.
• inCursorLocation - The location of cusror.
• inLockType -The lock type for records of a cursor.
• inCursorDirection - The direction of a cursor.
• inBinds - The array of bind parameters. Usually this is array of Strings or array of VARIANTs. See API Reference specific to your language.

Description:

SqlQuery() method is very similar to SqlSelect() by syntax, so see description of parameters in that method. Difference is that SqlQuery() is able to accept any SQL command, i.e. it is 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 must be able accept any SQL command and you don't know what exactly this command is. For example this can be if user type SQL query self or if you get SQL command from some file.

See Also

• VDatabase.SqlSelect()
• VQueryResult
• Valentina SQL Reference

Example:

dim res as VQueryResult
dim curs as VCursor
 
res = db.SqlQuery( strAnySqlCommand )
 
if res.type = EVQueryType.kCursor then
   curs = res.Cursor
end if