| Author |
Topic |
|
lab
Starting Member
14 Posts |
 Posted - 2005-03-14 : 15:09:47
|
| I've been trying for a couple of days to use transactions in a C++ program and each time I insert something in a table, I cannot read for that table unless I perform a commit. But since I want to import a DB from one format to another I need to read the DB after the import to know what ID the DB gave me to create the links and I dont want to commit the datas because if an error occur later I want to roll back everything. I use the query analyser and the table doesn't get blocked in it so I don't understand why it would in my program.Im using 2 DB one Access which I read from and one MS SQL 2000 which I wanna write to. For now I've been using CDaoDatabase as connection to both Database. I know there is something about ISOLATION LEVEL. I know that by default its supposed to be set at READ COMMITTED. I was wondering if its possible to change the default values to READ UNCOMMITTED because I tried to send the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED using the DB->Execute(sql string) function but it give me an error.Does anyone have and idea on what I can do to fix the problem ???Thx,Steve |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-03-14 : 15:17:37
|
| Do all your work in stored procedures and you have this problem...Brett8-) |
 |
|
|
lab
Starting Member
14 Posts |
Posted - 2005-03-14 : 15:29:22
|
| There are some reasons why I can really code everything in stored procedures. I really must do it all from my C++ program ... I just need a way to read uncommitted data from my transaction in my work space. I mean, if the query analyser can do it why shoulnd I be able to ? I just need an exemple of the way to do it ...Any other suggestions?Steve |
|
|
|
lab
Starting Member
14 Posts |
Posted - 2005-03-14 : 15:32:18
|
| I mean if only there was an option to clic in the server enterprise manager to set the DB default Isolation level to READ UNCOMMITTED I would be happy ! But I never found it and I'm pretty sure I looked everywhere .. Steve |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-03-14 : 15:36:28
|
are you using VC++ 6.0?if so try using recordset pointers:_RecordsetPtri worked with them in 2001 and i remember they were cool. but since ado.net came out i pushed them outof my mind  but msdn for VC++ 6.0 should have info on them.Go with the flow & have fun! Else fight the flow  |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-03-14 : 15:51:55
|
| see if you can include "set transaction isolation level read uncommitted" as part of your connection string. Alternatively, if you're sending dynamically built sql statments to Sql server, try using the table hint "with (nolock)" for all your tables that you want to dirty reads from.Be One with the OptimizerTG |
|
|
|
lab
Starting Member
14 Posts |
Posted - 2005-03-14 : 15:52:30
|
| Yes im using VC++ 6.0 and I need to use controls that are part of a fresh installed windows (no .net framework, MDAC or Jet installation... :S) I've looked into the MSDN library about _RecordsetPtr they are ADO component... I'm gonna try to get familiar with them! I just hope they dont require extra drivers, because my program as to run on any fresh installed platform ...In the mean time if someone got info on how to change the default Isolation level plz let me know...Steve |
|
|
|
lab
Starting Member
14 Posts |
Posted - 2005-03-14 : 15:56:20
|
| I tried the with (nolock) option but if failed the DAO check as if its no recognized ... So every time I add this to my SQL statement it throws me an execption... :S for adding the SQL sintaxe directly to the connection string, well i dont really see how i could do it but I'll definitly give it a try ... Steve |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-03-14 : 15:58:33
|
| "with (nolock)" used to be just "nolock" so you might want to try that as well.Be One with the OptimizerTG |
|
|
|
lab
Starting Member
14 Posts |
Posted - 2005-03-14 : 16:02:13
|
| TG, Does this request make sense ? SELECT * FROM testtable nolock WHERE id=1;or does the nolock goes somewhere else ?Steve |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-03-14 : 16:03:57
|
| well TG preety much covered the sql side so i'll just add:use ADO not DAO. ADO is much faster and proper MDAC is in every version of windows since 2000.older versions aren't even properly supported anymore...Go with the flow & have fun! Else fight the flow |
|
|
|
lab
Starting Member
14 Posts |
Posted - 2005-03-14 : 16:09:40
|
| after what I just saw from running commands from the query analyser will the DB was locked form my program, nolock by itself doesnt work, with (nolock) works and if ADO works with the WITH (nolock), I should be able to perform what I need to do ... I'll be trying to code that now since I've just been answering post for the last hour (nearly live chat .. lol) Thx, a Lot for your help guys and ill keep you in touch if it worked or not ... Steve |
|
|
|
lab
Starting Member
14 Posts |
Posted - 2005-03-15 : 10:52:16
|
| Thx to everyone who helped me on this project, everything seems to work now ! Steve |
|
|
|
|
Transaction using C++