Skip to content

ODBCStmt

[Source]

A simple API for performing SQL queries using ODBC for maximum portability.

This is a synchronous API so its use should bear that in mind. An asynchronous API will follow later.

Prerequisites

This API handles queries. Before you can execute a query, you need to connect to a database. Before you can connect to a database, you need an Environment.

See the documentation of ODBCDbc and ODBCEnv for more detail one what these two objects are and how they are used. The example below is barebones for those objects.

Usage

The simplest case is when your query doesn't take any parameters or return a data set. For example, a table creation. In this case, we can simply prepare the statement and directly execute it:

var stm: ODBCStmt = dbc.stmt()?

stm
  .> prepare("create table demotable (myint integer unique, mystr varchar(400))")?
  .> execute()?

Inserting Data

In order to insert data into this table we prepare the insert into SQL statement and provide the values (parameters) to be inserted into the columns, later. In our statement, we use ? as placeholders for these values:

stm
  .> prepare("insert into demotable (myint, mystr) values (?,?)")?

A prepared statement can be executed any number of times, but each execution will likely have different values to be populated for each row.

So before we can pass values to our prepared statement, we have to create pony objects to represent them. In our table above we have a SQLInteger (I32), and a SQLVarchar(400). For some types we have to specify the size of the buffer, for others such as SQLInteger, we don't as their sizes are known.

var myint: SQLInteger = SQLInteger
var mystr: SQLVarchar = SQLVarchar(400)

NOTE: If your buffer is too small, your statement will fail.

Once defined we bind them to the correct parameter by adding them in order:

stm.bind_parameter(myint)?
stm.bind_parameter(mystr)?

The prepared statement can now be executed any number of times. In this example we'll execute it ten times:

for cnt in Range[I32](1,10) do
  myint.write(cnt)
  mystr.write("This is a string with a number: " + cnt.string())

  stm.execute()?
end

Querying Data

In order to query data, we prepare and bind_parameters as before:

var intin: SQLInteger = SQLInteger
stm
  .> prepare("select myint, mystr from demotable where myint > ?")?
  .> bind_parameter(intin)?

But we also need to provide buffers for output:

var intout: SQLInteger = SQLInteger
var strout: SQLVarchar = SQLVarchar(400)

stm
  .> bind_column(intout)?
  .> bind_column(strout)?

Once these buffers are all allocated, we can execute our query:

intin.write(8)
while (stm.fetch_scroll(SqlFetchNext)?) do
  Debug.out("myint: " + intout.read()?.string() +
            "mystr: " + strout.read())
end

Handling Errors

In this API, we use partial functions in all our functions to indicate if a call failed. If a call failed, we can determine why by examining the contents of the field errtext.

Notes

Some rough edges: - This is a synchronous API. This could cause you problems. It is however a prerequisite for an asynchronous API which is better suited to pony. - The API is likely to change.

class ref ODBCStmt is
  SqlState ref

Implements

Public fields

var strict: Bool val

[Source]

Public Functions

alloc

[Source]

fun ref alloc()
: (SQLSuccess val | SQLSuccessWithInfo val | SQLStillExecuting val | 
    SQLError val | SQLInvalidHandle val | SQLNeedData val | 
    PonyDriverError val | SQLNoData val)

Returns

sqlstates

[Source]

Returns an array of SQL States.

fun box sqlstates()
: Array[(String val , String val)] val

Returns

prepare

[Source]

Used to 'prepare' a SQL statement.

Any response from the ODBC driver other than a non-warning success will result in a thrown error.

fun ref prepare(
  str: String val,
  sl: SourceLoc val = __loc)
: Bool val ?

Parameters

Returns

bind_parameter

[Source]

Used to bind a parameter to a prepared query.

All parameters in this API are passed via SQLType objects. This object represents a textual buffer. You must allocate sufficient space in this buffer before you bind it to a column.

Parameters must be bound in order.

Any response from the ODBC driver other than a non-warning success will result in a thrown error.

fun ref bind_parameter(
  i: SQLType ref,
  sl: SourceLoc val = __loc)
: Bool val ?

Parameters

Returns

bind_column

[Source]

Used to bind a column in a result-set for the prepared query.

All columns in this API are received via SQLType objects. This object represents a textual buffer. For efficiency reasons you should allocate sufficient space in this buffer before you bind it to a column.

"Should", because this API will resize a buffer if it determines that the buffer was insufficiently sized.

Columns must be bound in order.

Any response from the ODBC driver other than a non-warning success will result in a thrown error.

fun ref bind_column(
  i: SQLType ref,
  sl: SourceLoc val = __loc)
: Bool val ?

Parameters

Returns

execute

[Source]

Before executing your prepared command you should populate your parameters with the necessary data.

Any response from the ODBC driver other than a non-warning success will result in a thrown error.

fun ref execute(
  sl: SourceLoc val = __loc)
: Bool val ?

Parameters

Returns

direct_exec

[Source]

Directly executes the provided statement.

Any response from the ODBC driver other than a non-warning success will result in a thrown error.

fun ref direct_exec(
  statement: String val,
  sl: SourceLoc val = __loc)
: Bool val ?

Parameters

Returns

rowcount

[Source]

Warning: The ODBC standard does not mandate this function's correctness.

This call should return the number of affected rows.

Any response from the ODBC driver other than a non-warning success will result in a thrown error.

fun ref rowcount(
  sl: SourceLoc val = __loc)
: I64 val ?

Parameters

Returns

num_result_cols

[Source]

Warning: The ODBC standard does not mandate this function's correctness.

Any response from the ODBC driver other than a non-warning success will result in a thrown error.

fun ref num_result_cols(
  rv: CBoxedI16 ref,
  sl: SourceLoc val = __loc)
: Bool val ?

Parameters

Returns

get_type_info

[Source]

Calls the ODBC API SQLGetTypeInfo. If you don't provide a type ID it will default to SQL_ALL_TYPES.

After executing this you will need to bind columns and use fetch to fetch the data.

fun ref get_type_info(
  sqltype: I16 val = 0,
  sl: SourceLoc val = __loc)
: Bool val ?

Parameters

Returns

columns

[Source]

See the ODBC API documentation for SQLColumns for a full description.

fun ref columns(
  catalog: String val = "",
  schema: String val = "",
  table: String val = "",
  column: String val = "",
  sl: SourceLoc val = __loc)
: Bool val ?

Parameters

Returns

get_data

[Source]

See SQLGetData for more information.

This is used if you decide to read data directly as opposed to binding columns and populating buffers automatically. The main reason to choose this approach is for huge columns or if you need more direct control.

fun ref get_data(
  column: U16 val,
  sqltype: SQLType ref)
: Bool val ?

Parameters

Returns

fetch

[Source]

Fetches a row of data from the result set.

This function returns true is there are more rows, false if this was the last row in the set.

fun ref fetch(
  sl: SourceLoc val = __loc)
: Bool val ?

Parameters

Returns

fetch_scroll

[Source]

This function causes the ODBC driver to populate your buffers with the specified row of your result set. The default behaviour SqlFetchNext, the next row in your result set.

Any response from the ODBC driver other than a non-warning success will result in a thrown error.

This function returns true is there are more rows, false if this was the last row in the set.

fun ref fetch_scroll(
  d: (SqlFetchNext val | SqlFetchFirst val | SqlFetchLast val | 
    SqlFetchPrior val | SqlFetchAbsolute val | SqlFetchRelative val | 
    SqlFetchBookmark val) = reference,
  offset: I64 val = 0,
  sl: SourceLoc val = __loc)
: Bool val ?

Parameters

Returns

finish

[Source]

Closes the result-set using SQLFreeStmt.

fun ref finish(
  sl: SourceLoc val = __loc)
: Bool val ?

Parameters

Returns

get_sth

[Source]

fun box get_sth()
: ODBCHandleStmt tag

Returns