When multiple transaction objects are created, the real database transaction will only be committed when the last EDLTransaction object is committed. However, when one EDLTransaction object is rolled back, the database transaction is always rolled back immediately, even if there are other open EDLTransaction objects.

• DBMS.SQLServer
• DBMS.Oracle
• DBMS.Pervasive
• DBMs.MySQL 

For use of parameters in the query, see the Query function.

The QueryTimeout is the number of seconds after which the function will throw an EDLTimeoutException error. The default value is 30 seconds.

Example:

conn.Exec("UPDATE humres SET comp = @P1 " & _
                        "WHERE comp = @P2 AND costcenter = @P3", _
                        New String() {"001", "002", "R&D"})

Example:

conn.Insert("Items", _
                        New String() {"Code", "Description", "syscreated"}, _
                        New Object() {"ABC", "Letter board", _
                        New SQLFunction("GETDATE()")})
                        

All arguments (except Table) can either be a single value or an array of values, but the "values" must match dimension with the "names". E.g., if the Key argument is a single string, then the KeyValue argument must be a single value. If the Columns argument is an array of strings, the ColumnValues must be an array of values with exactly the same upper bound.

Example:

 conn.Merge("Resources", "HID", 847, _
                          New String() {"ID", "FullName", "UserName"}, _
                          New Object() {New SQLFunction("NEWID()"), _
                        "Sebastian Toet", "toet0845-6"})

For use of parameters see the Query function.

The DataTypes argument should contain the data types of the query parameters.

Example:

Dim qu As EDLPreparedQuery = _
                        conn.Prepare("SELECT fullname FROM humres WHERE res_id=@P1", _
                        EDLDataTypes.Int, EDLQueryOptions.SingleValue)
                        Dim name As String
                        name  = qu.Query(310)
                        name = qu.Query(845)
                        name = qu.Query(994)

qu = conn.Prepare("DELETE FROM humres WHERE res_id = @P1", _
                        EDLDataTypes.Int)
                        qu.Exec(845)
                        qu.Exec(1024)

1. By default as a 2-dimensional array, if no options are supplied.
2. A 1-dimensional array if EDLQueryOptions.SingleRow is supplied.
3. A single value if EDLQueryOptions.SingleValue is supplied
4. An array of arrays if EDLQueryOptions.MultipleResultsets is supplied
5. An object set to "Nothing" if there are no query results
6. An IDataReader interface if EDLQueryOptions.ReturnDataReader is supplied. The calling application must close this object, before any other query is performed.
7. By default all database Null values are translated to Nothing values. Only of the EdlQueryOptions.Nulls is supplied the Null values are retained.

Parametrized queries must use the following paremeter names in the SQL query (no question marks):

• The parameter names @P1, @P2, etc, in the order of the parameters supplied to the Query method
• Parameter names that are supplied in the ParamNames argument

Example:

Dim data(,) As Object = conn.Query( _
                        "SELECT ID, syscreated, Description " & _
                        "FROM Absences " & _
                        "WHERE Type = @P1 AND EmpID = @P2 " & _
                        "ORDER BY syscreated DESC", _
                        New Integer() {0, 845})

Stored procedures can be called as:

• Supply only the name of the stored procedure
• Supply the EDLQueryOptions.StoredProcedure options
• Supply the names of the arguments of the stored procedure as a string array to the ParamNames argument

Example:

Dim bManager As Boolean = _
                        conn.Query("IsIndirectManager", _
                        New Integer() {empid, hrid, 0}, _
                        EDLQueryOptions.StoredProcedure Or _
                        EDLQueryOptions.SingleValue, _
                        NewString() {"@empid", "@manid", "@amount"}) <> 0