I'm carrying out an intensive database operation that is performing poorly and which I think there is some room for improvement by binding an ODBC parameter to an autonumber field. I'm not sure if it is an appropriate thing to do or not, and if it is, how to do it correctly.
The table I am reading from and writing to is in a Microsoft Access database and has 2 types of fields: an autonumber and a text. The first piece of code illustrates the general flow of the read/write operation that I want to improve by doing away with code starting from comment labeled ..1
Code: Select all
/*
Open database via ODBC connection, prepare an appropriate SQL statement and bind the text field to a buffer using the input pameter mode*/
dont include the autonumber*/
Dbase = open_dbase1(),
/*
Perform the intensive database operation*/
foreach Record=get_some_record() do
if
Dbase:exists(Record:criteria)=Autonumber
then
Dbase:update(Autonumber, Record)
else
Dbase:insert(Record),
/*
Fetch the autonumber.........................................................1*/
if Dbase:exists(Record:criteria)=Autonumber
then
/*
Save the autonumber locally for other uses*/
Dbase:save(Autonumber)
else
exception::raise_error("Something went wrong")
end if
end if
end foreach.
Code: Select all
/*
Open database via ODBC connection, prepare the appropriate SQL statement and bind text parameter INCLUDING THE AUTONUMBER field*/
Dbase = open_dbase2(),
/*
Perform the intensive database operation*/
foreach Record=get_some_record() do
/*
Extract the parameter values from the record and use them to set the database buffers*/
Dbase:setparametervalue(Record)
if
Dbase:exists()=Autonumber
then
Dbase:update(Autonumber)
else
Dbase:insert(),
/*
Read the bounded autonumber field*/
Autonumber = Dbase:getParameterValue_integer(1),
Dbase:save(Autonumber)
end if
end foreach.
Code: Select all
implement main
open core
constants
/*
The sample Access database file*/
path:string = @"C:\Mutall project\Mutall foundation classes\applications\samples\1 No xpollinnation\database.accdb".
clauses
run() :-
/*
Create a new ODBC connection*/
Connection=odbcConnection::new(),
/*
Compile the connection string for e.g., Microsoft Access database*/
ConnectionStr = string::format("Driver={Microsoft Access Driver (*.mdb, *.accdb)}; Dbq=%; Uid=Admin; Pwd=;", path),
/*
Do the connection, using driver specific information. Set the task handle to null. (Not sure what the last parameter is for).*/
if not(_ConStrOut = Connection:driverConnect(nullHandle, ConnectionStr, odbc_native::sql_driver_complete))
then console::write("ODBC driver connection failed") end if,
/*
Create a new odbc statement*/
Stmt = odbcStatement::new(Connection),
/*
Construct an Sql statement with 2 parameters, output and input*/
Sql = ("insert into [study] (study, name) values (?,?)"),
/*
Prepare the sql statement to receive output and input parameters*/
Stmt:prepare(Sql),
/*
Bind the parameter output. Access does not support the output parameterMode, but it does support the inputOutput*/
Stmt:bindParameter_integer(1,odbcStatement::inputOutput),
/*
Bind the input parameter*/
Stmt:bindParameter_string(2, 30, odbcStatement::input),
/*
The data type for the first parameter is an autonumber/integer which Access should assign automatically*/
/*
The data type for the second paramater is a string*/
Stmt:setparametervalue(2, core::string("Test")),
/*
Excute the sql statement*/
Stmt:execute(),
if Stmt:fetch()%................................2
then
Autonumber = Stmt:getParameterValue_integer(1),
console::write(Autonumber)
else
console::write("Data not found")
end if
end implement main
1) Access does not support the odbcStatement::output option so I used odbcStatement::inputOutput; I am not sure if it is a correct thing to do.
2) The autonumber field is set to 0; it does not seem to change as I add more records
3) I am not sure about the code after step marked 2 that is trying to retrieve the autonumber.
Thanks for any light you can shed on this issue.