Download ODBC Connectivity

SQLDOUBLE
SQLREAL
SQLINTEGER
SQLSMALLINT
SQLLEN
} COLUMNS;
RData [MAX
R4Data[MAX
IData [MAX
I2Data[MAX
IndPtr[MAX
ROWS
ROWS
ROWS
ROWS
ROWS
FETCH];
FETCH];
FETCH];
FETCH];
FETCH];
The first six entries are returned by a call to SQLDescribeCol: DataType
is used to select the buffer to use. There are separate buffers for doubleprecision, single-precision, 32-bit and 16-bit integer and character/byte data.
When character/data buffers are allocated, datalen records the length allocated per row (which is based on the value returned as ColSize). The
IndPtr value is used to record the actual size of the item in the current row
for variable length character and binary types, and for all nullable types the
special value SQL NULL DATA (-1) indicates an SQL null value.
The other main C-level operation is to send data to ODBC driver for
sqlSave and sqlUpdate. These use INSERT INTO and UPDATE queries respectively, and for fast = TRUE use parametrized queries. So we have the
queries (split across lines for display)
> sqlSave(channel, USArrests, rownames = "State", addPK = TRUE, verbose = TRUE)
Query: CREATE TABLE "USArrests"
("State" varchar(255) NOT NULL PRIMARY KEY, "Murder" double, "Assault" integer,
"UrbanPop" integer, "Rape" double)
Query: INSERT INTO "USArrests"
( "State", "Murder", "Assault", "UrbanPop", "Rape" ) VALUES ( ?,?,?,?,? )
Binding: ’State’ DataType 12, ColSize 255
Binding: ’Murder’ DataType 8, ColSize 15
Binding: ’Assault’ DataType 4, ColSize 10
Binding: ’UrbanPop’ DataType 4, ColSize 10
Binding: ’Rape’ DataType 8, ColSize 15
Parameters:
...
> sqlUpdate(channel, foo, "USArrests", verbose=TRUE)
Query: UPDATE "USArrests" SET "Assault"=? WHERE "State"=?
Binding: ’Assault’ DataType 4, ColSize 10
Binding: ’State’ DataType 12, ColSize 255
Parameters:
...
At C level, this works by calling SQLPrepare to record the insert/update
query on the statement handle, then calling SQLBindParameter to bind a
buffer for each column with values to be sent, and finally in a loop over rows
copying the data into the buffer and calling SQLExecute on the statement
handle.
The same buffer structure is used as when retrieving result sets. The difference is that the arguments which were ouptuts from SQLBindCol and inputs
to SQLBindParameter, so we need to use sqlColumns to retrieve the column
characteristics of the table and pass these down to the C interface.
34