How do you bind an ODBC parameter to an autonumber field and retrieve it later?

Discussions related to Visual Prolog
Peter Muraya
VIP Member
Posts: 146
Joined: 5 Dec 2012 7:29

How do you bind an ODBC parameter to an autonumber field and retrieve it later?

Unread post by Peter Muraya » 11 Aug 2015 18:08

Hi,
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.
Intended improvement

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.  
To test whether the binding would work (the way I thought it should) I wrote the following code:-

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
There are a number of issues.
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.
Mutall Data Management Technical Support

User avatar
Thomas Linder Puls
VIP Member
Posts: 1625
Joined: 28 Feb 2000 0:01

Unread post by Thomas Linder Puls » 11 Aug 2015 20:02

I don't have real insight in this problem.

But I believe there is no way to combine the insert with the fetching of the auto generated value (in MS Access).

autoincrement

The resulting values of autoincrement fields will have to be fetched in a separate query:

I.e. first insert: insert into study(name) values ('test')

Then fetch the generated value: select study from study where name = 'test'

But that requires that name='test' only is true for the new record.

identity

If the column is an identity column, the value will still have to be fetched in a separate query but in this case it can be fetched using the @@identity function:

I.e. first insert: insert into study(name) values ('test')

Then fetch the last generated identity value: select @@identity as identity.

See @@IDENTITY (Transact-SQL).
Regards Thomas Linder Puls
PDC

Peter Muraya
VIP Member
Posts: 146
Joined: 5 Dec 2012 7:29

Unread post by Peter Muraya » 12 Aug 2015 9:05

Thanks Thomas, that was useful. Indeed the autonumber field is an identity field and so the @@identity function would be applicable. I have learnt of other alternative functions -- scope_identity() and ident_current(...) but Access won't recognize them. I have revised my test code to include your suggestion. Here it is:-

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 input parameters only*/           Sql = ("insert into [study] (name) values (?)"),           /*           Prepare the sql statement to receive output and input parameters*/           Stmt:prepare(Sql),           /*           Bind the input parameter*/           Stmt:bindParameter_string(1, 30, odbcStatement::input),           /*           The data type for the second  paramater is a string*/           Stmt:setparametervalue(1, core::string("Test")),           /*           Excute the sql statement*/           Stmt:execute(),             /*           Create a new odbc statement for querying the identity column*/           Qry = odbcStatement::new(Connection),           /*           Read the identity column*/           Qry:execDirect("select @@identity"),             if Qry:fetch()           then                Autonumber = Qry:getColumnValue_integer(1),                console::write(Autonumber)           else                console::write("Something went wrong")           end if. end implement main   goal     console::runUtf8(main::run),     console::readchar()=_.
Now what I have to test is whether the following predicate is a better replacement to my original Dbase:exist()=Autonumber statement in comment labeled ...1 of the pseudo code. My gut feeling tells me that it should be.

Code: Select all

get_autonumber(Connection)=Autonumber:-           Qry = odbcStatement::new(Connection),           /*           Read the identity column*/           Qry:execDirect("select @@identity"),             if Qry:fetch()           then                Autonumber = Qry:getColumnValue_integer(1),           else                exception::raise_error("Something went wrong")           end if.
Mutall Data Management Technical Support

Post Reply