VDatabase Class: SQL Methods

NEW in 7.0

Declaration:

CreateSqlStatement( 
    inQuery as String ) as VSqlStatement

// Valentina for Xojo (V4RB)
Prepare( 
    inQuery as String ) as VPreparedStatement

Parameters:

• inQuery - The SQL string of a query.

Description:

You can use this function to create VSqlStatement object. VSqlStatement is useful to efficiently execute sql statement multiple times.

See Also

• VSqlStatement

Example:

dim stmt as VSqlStatement
stmt = db.CreateSqlStatement( "UPDATE person SET name = :1 WHERE name = :2" )

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 an 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 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

NOTE: For the commands having 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 cursor.
• inLockType -The lock type for records of a cursor.
• inCursorDirection - The direction of a cursor.
• inBinds - The array of bind parameters. Usually, this is an 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 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 a 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 the user types 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