VSqlStatement Class: Bind Methods

Description:

After you got the VSqlStatement object (VPreparedStatement in the V4RB) you can set bound values (if needed).

Below you can see a group of similar methods to bind values of different types into the statement object. Internally they are stored as an array.

Please notice, that these methods index array from zero! If you are using “:1” notation of SQL binding in a query, to avoid mistakes you can use the TIP shown in the example below, do “n-1” in code. Another way to specify placeholders is to use “?”, which follows to SQL Standard.

ValentinaDB allows binding to VSqlStatement many records. Usually, this is for INSERTs. See below the multi-row methods section.

Examples:

dim stmt1 as VSqlStatement = db.CreateSqlStatement( "INSERT INTO T1 VALUES( :1, :2, :3 );" )
 
for i = 1 to 100
    stmt1.bind_int32 ( 1-1, 578 )
    stmt1.bind_null  ( 2-1 )
    stmt1.bind_string( 3-1, "Jason" )
 
    affectedRows = stmt1.SqlExecute()
next

dim stmt1 as VSqlStatement = db.CreateSqlStatement( "INSERT INTO T1 VALUES( ?, ?, ? );" )
 
for i = 1 to 100
    stmt1.bind_int32 ( 0, 578 )
    stmt1.bind_null  ( 1 )
    stmt1.bind_string( 2, "Jason" )
 
    affectedRows = stmt1.SqlExecute()
next

Examples for Xojo:

The above first example for “Valentina for Xojo” ADK looks as:

dim stmt1 as VPreparedStatement = db.Prepare( "INSERT INTO T1 VALUES( :1, :2, :3 );" )
 
for i = 1 to 100
    stmt1.bind_int32 ( 1-1, 578 )
    stmt1.bind_null  ( 2-1 )
    stmt1.bind_string( 3-1, "Jason" )
 
    affectedRows = stmt1.SqlExecute()
next

• VDatabase Class: SQL Methods
• Valentina ADK API FAQs

Declaration:

Bind_null( InIndex as Integer )	

Description:

This method is to set “sql-null” value into the current binding row.

Parameters:

• InIndex - zero-based value position in binding row.

Example:

stmt.Bind_null(0)

Declaration:

Bind_bool( InIndex as Integer, inValue as Boolean )	

Description:

This method is to set boolean value into the current binding row.

Parameters:

• InIndex - zero-based value position in binding row.
• InValue - value.

Example:

stmt.Bind_bool(0, true)

Declaration:

Bind_uint8( InIndex as Integer, inValue as Byte )	

Description:

This method is to set byte value into the current binding row.

Parameters:

• InIndex - zero-based value position in binding row.
• InValue - value.

Example:

stmt.Bind_uint8(0, 'a')

Declaration:

Bind_uint16( InIndex as Integer, inValue as SmallUInt )	

Description:

This method is to set smallUInt value into the current binding row.

Parameters:

• InIndex - zero-based value position in binding row.
• InValue - value.

Example:

stmt.Bind_uint16(0, 1)

Declaration:

Bind_int16( InIndex as Integer, inValue as SmallInt )	

Description:

This method is to set smallInt value into the current binding row.

Parameters:

• InIndex - zero-based value position in binding row.
• InValue - value.

Example:

stmt.Bind_int16(0, -1)

Declaration:

Bind_uint32( InIndex as Integer, inValue as UInteger )	

Description:

This method is to set UInteger value into the current binding row.

Parameters:

• InIndex - zero-based value position in binding row.
• InValue - value.

Example:

stmt.Bind_uint32(0 , 1)

Declaration:

Bind_int32( InIndex as Integer, inValue as Integer )	

Description:

This method is to set Integer value into the current binding row.

Parameters:

• InIndex - zero-based value position in binding row.
• InValue - value.

Example:

stmt.Bind_int32(0, -1)

Declaration:

Bind_uint64( InIndex as Integer, inValue as BigUInt )	

Description:

This method is to set BigUInt value into the current binding row.

Parameters:

• InIndex - zero-based value position in binding row.
• InValue - value.

Example:

stmt.Bind_uint64(0, 1)

Declaration:

Bind_int64( InIndex as Integer, inValue as BigInt )	

Description:

This method is to set BigInt value into the current binding row.

Parameters:

• InIndex - zero-based value position in binding row.
• InValue - value.

Example:

stmt.Bind_int64(0, -1)

Declaration:

Bind_float(InIndex as Integer, inValue as Float)	

Description:

This method is to set Float value into the current binding row.

Parameters:

• InIndex - zero-based value position in binding row.
• InValue - value.

Example:

stmt.Bind_float(0, 1.1)

Declaration:

Bind_double( InIndex as Integer, inValue as Double )	

Description:

This method is to set Double value into the current binding row.

Parameters:

• InIndex - zero-based value position in binding row.
• InValue - value.

Example:

stmt.Bind_double(0, 1.1)

Declaration:

Bind_string( InIndex as Integer, inValue as String )	

Description:

This method is to set String value into the current binding row.

Parameters:

• InIndex - zero-based value position in binding row.
• InValue - value.

Example:

stmt.Bind_string(0, 'abc')

Declaration:

Bind_binary( InIndex as Integer, inValue as ByteArray )	

Description:

This method is to set binary value into the current binding row.

Parameters:

• InIndex - zero-based value position in binding row.
• InValue - value.

Example:

stmt.Bind_binary(0, value)

Declaration:

Init()	

Description:

This method is used for multi-row binding purposes. Call it to delete the previous binding matrix. You need this usually when writing loop, which reuse the same SqlStatement object.

Example:

//@ Prepare 2x2 binding matrix:
 
// The first binding row:
stmt.Bind_bool(0,true)
stmt.Bind_null(1)
 
// The second binding row:
stmt.AddBindRow()
stmt.Bind_bool(0,false)
stmt.Bind_null(1)
 
// Execute statement:
stmt.SqlExecute()
 
 
// ------------
//@ Prepare 3x2 binding matrix:
//
 
// Init statement to be able to set another multi/single-row binding:
stmt.Init()
 
// The first binding row:
stmt.Bind_bool(0,false)
stmt.Bind_null(1)
 
// The second binding row:
stmt.AddBindRow()
stmt.Bind_bool(0,true)
stmt.Bind_null(1)
 
// The third binding row:
stmt.AddBindRow()
stmt.Bind_bool(0,true)
stmt.Bind_null(1)
 
// Execute statement:
stmt.SqlExecute()

Declaration:

AddBindRow()	

Description:

This method is used for multi-row binding purposes. Call it to add a new row to the binding matrix.

Example:

'
' Prepare 2x2 binding matrix:
'
 
' First binding row:
stmt.Bind_bool(0,true)
stmt.Bind_null(1)
 
' Second binding row:
stmt.AddBindRow()
stmt.Bind_bool(0,false)
stmt.Bind_null(1)
 
'
' Execute statement:
'
stmt.SqlExecute()

Example:

func( params )
 
    stmt = db.Prepare( "DELETE FROM T WHERE RecID = ?" )
 
    for( i = 1; i <= 10; ++i )
    { 
        RecID = ...
        stmt.Bind_int(0, RecID )
 
        if( i < 10 )
          stmt.AddBindRow()
    }
 
    stmt.SqlExecute()  // Delete 10 records at once.
 
end func