OpenDBX

Rebol [
	file: %dbx.r
	author: "Graham Chiu"
	Date: 7-Jul-2010
	license: 'LGPL
	notes: {
		bindings for http://linuxnetworks.de/doc/index.php/OpenDBX
		opendbx
	}
]

; Ladislav's structure utilities
; do %peekpoke.r

dbx: load/library %opendbxWin32/libopendbx-1.dll

create-db-handle: func [][ make struct! [ptr [integer!]] none ]
create-resultset-ptr: :create-db-handle ; func [][ make struct! [ptr [integer!]] none ]
create-lo-ptr: :create-db-handle
	
create-timeval-struc: func [][ make struct! [ [integer!] [integer!]] none ]

ODBX_TYPE_BOOLEAN: 0
ODBX_TYPE_SMALLINT: 1
ODBX_TYPE_INTEGER: 2
ODBX_TYPE_BIGINT: 3
ODBX_TYPE_DECIMAL: 7
ODBX_TYPE_REAL: 8
ODBX_TYPE_DOUBLE: 9
ODBX_TYPE_FLOAT: 15
ODBX_TYPE_CHAR: 16
ODBX_TYPE_NCHAR: 17
ODBX_TYPE_VARCHAR: 18
ODBX_TYPE_NVARCHAR: 19
ODBX_TYPE_CLOB: 32
ODBX_TYPE_NCLOB: 33
ODBX_TYPE_XML: 34
ODBX_TYPE_BLOB: 47
ODBX_TYPE_TIME: 48
ODBX_TYPE_TIMETZ: 49
ODBX_TYPE_TIMESTAMP: 50
ODBX_TYPE_TIMESTAMPTZ: 51
ODBX_TYPE_DATE: 52
ODBX_TYPE_INTERVAL: 53
ODBX_TYPE_ARRAY: 64
ODBX_TYPE_MULTISET: 65
ODBX_TYPE_DATALINK: 80
ODBX_TYPE_UNKNOWN: 255

odbx_error: make routine! [ 
	handle [integer!] 
	error [integer!] 
	return: [string!]
] dbx "odbx_error" 

odbx_capabilities: make routine! [
	handle [integer!]
	cap [integer!]
	return: [integer!]
] dbx "odbx_capabilities"

odbx_init: make routine! [ 
    db-handle [struct! [[integer!]]]
 	backend 	[string!] 
 	host 		[string!] 
 	port 		[string!] 
    return:   [integer!]
]  dbx "odbx_init"

odbx_finish: make routine![
	handle [integer!]
	return: [integer!]
] dbx "odbx_finish"

odbx_query: make routine! [
	handle [integer!]
	stmt [string!]
	length [integer!]
	return: [integer!]
] dbx "odbx_query"

odbx-close: func [
	"Close database."
	handle [integer!]
	/local val
][
	if not zero? val: odbx_finish handle [
		odbx_error handle val
	]
]

odbx_bind: make routine! [
	handle [integer!]
	database [string!]
	user [string!]
	password [string!]
	method [integer!]
	return: [integer!]
] dbx "odbx_bind"
	
odbx_unbind: make routine! [
	handle [integer!]
	return: [integer!]
] dbx "odbx_unbind"

; currently crashes rebol
odbx_escape: make routine! [
	handle [integer!]
	from [string!]
	fromlen [integer!]
	to [string!]
	tolen [integer!]
	return: [integer!]
] dbx "odbx_escape"
	
; 3 = results available, 2 = nothing returned
odbx_result: make routine! [
	handle [integer!]
	result [struct! [[integer!]]]
	timeout [struct! [[integer!][integer!]]]
	chunk [integer!]
	return: [integer!]
] dbx "odbx_result"
	
;; ===== resultset operators 

odbx_column_count: make routine! [
	result [integer!]
	return: [integer!]
] dbx "odbx_column_count"	
		
odbx_row_fetch: make routine! [
	result [integer!]
	return: [integer!]
] dbx "odbx_row_fetch"

odbx_result_finish: make routine! [
	result [integer!]
	return: [integer!]
] dbx "odbx_result_finish"

;; ===== end of resultset operators

;; ===== these operate on the current row retrieved by row_fetch

; 0 is first column
odbx_column_name: make routine! [
	result [integer!]
	pos [integer!]
	return: [string!]
] dbx "odbx_column_name"

; crashes rebol if returns a string as per the docs ( char* )
odbx_column_type: make routine! [
	result [integer!]
	pos [integer!]
	return: [integer!]
] dbx "odbx_column_type"

; 0 is first column
; use this one for non CLOB/BLOB fields
odbx_field_value: make routine! [
	result [integer!]
	pos [integer!]
	return: [string!]
] dbx "odbx_field_value"	

; use this one for CLOB and BLOB fields
odbx_field_value_ptr: make routine! [
	result [integer!]
	pos [integer!]
	return: [int]
] dbx "odbx_field_value"	

odbx_field_length: make routine! [
	result [integer!]
	pos [integer!]
	return: [integer!]
] dbx "odbx_field_length"

;; ====== end of row fetch functions ======

;; ====== blob operations =================

odbx_lo_open: make routine! [
	result [integer!]
	lo [struct! [[integer!]]]
	value [integer!]
	return: [integer!]
] dbx "odbx_lo_open"

odbx_lo_close: make routine! [
	lo [integer!]
	return: [integer!]
] dbx "odbx_lo_close"
	
odbx_lo_read: make routine! [
	lo [integer!]
	buffer [string!]
	buflen [integer!]
	return: [integer!]
] dbx "odbx_lo_read"
	
odbx_lo_write: make routine! [
	lo [integer!]
	buffer [string!]
	buflen [integer!]
	return: [integer!]
] dbx "odbx_lo_write"

;; ====== end blobs =======================

; connect to the db server and return a db handle
; r1: odbx_init tmp: make struct! [ ptr [integer!]] none "firebird" "localhost" "3050"
r1: odbx_init db: create-db-handle "firebird" "localhost" "3050"

; open a database with userid and password
r2: odbx_bind db/ptr "emr" "SYSDBA" "masterkey" 0

; check for basic functionality
probe r21: odbx_capabilities db/ptr 0

; check for blob functionality
probe r22: odbx_capabilities db/ptr 1

; create the query
r3: odbx_query db/ptr query: "select * from staff" length? query

; get a resultset back
r4: odbx_result db/ptr result: create-resultset-ptr create-timeval-struc 0

; count the columns in the resultset
r5: odbx_column_count result/ptr

; fetch the first row of the resultset
r6: odbx_row_fetch result/ptr
; loop thru the columns and get the name, type, length, and value
for i 0 13 1 [
	print [ "name: " odbx_column_name result/ptr i ]
	print [ "type: " odbx_column_type result/ptr i ]
	print [ "length: " odbx_field_length result/ptr i ]
	print [ "value: " odbx_field_value result/ptr i ]
] 

; in this test, my DB has a CLOB in column 9
probe ODBX_TYPE_CLOB = odbx_column_type result/ptr 9

; we are going to create a buffer of 1000 chars to hold the text
buffer: head insert/dup copy "" " " 1000

; first fetch the data pointer
blob: odbx_field_value_ptr result/ptr 9 ; column 9 for this rowset

; now we create a LO structure to hold a pointer to the blob structure
odbx_lo_open result/ptr lo: create-lo-ptr blob

; and now we read from it
count: odbx_lo_read lo/ptr buffer 1000

print [ "Characters read: " count ]
print [ "CLOB data: " buffer ]

; close the LO object
odbx_lo_close lo/ptr

; close the resultset
r7: odbx_result_finish result/ptr

; unbind the db
r9: odbx_unbind db/ptr

; close db
r5: odbx_finish db/ptr