Page 1 of 2
[patch_done] MSSQL transaction
Posted: 07.11.2012, 20:31
by delphichem
I use ZEOSLIB 7.0.1 to coonect to MSSQL 2008, all is ok, but Starttransaction method has no effect, because when I call Commit method I get
DBError : [10007] : General SQL Server error: Check messages from the SQL Server.
La requête COMMIT TRANSACTION n'a pas de BEGIN TRANSACTION correspondante
Aproximative translation "the query COMMIT TRANSACTION has no corresponding BEGIN TRANSACTION"
and I see with step by step debugging that every query is commited, It does not wait until the end of transaction
In my connection Autocommit is set to true
Posted: 07.11.2012, 23:33
by EgonHugeist
delphichem,
IMHO that's expected if AutoCommit = True. Can't see there is something wrong.
Switch of AutoCommit, Set TZQuery.CachedUpdates := True and then you can execute the Commit command. On the other hand Zeos does exactly what your current settings want to have: Commit each update right now..
Posted: 08.11.2012, 00:40
by delphichem
Hi, thank you
Posted: 08.11.2012, 07:57
by marsupilami
Hello Michael, hello delphichem,
EgonHugeist wrote:
IMHO that's expected if AutoCommit = True. Can't see there is something wrong.
I am not sure, wether Michael is right. With AutoCommit = True I would expect StartTransaction to start a new explicit transaction that I have to finish with Commit or Rollback explicitly. At least I use it like this at some points in my Applications with Zeos 6 and it works like this there. Did this change in Zeos 7?
@delphichem: Which driver do you use?
Best regards,
Jan
Posted: 08.11.2012, 09:11
by EgonHugeist
marsupilami,
hi Jan. That's a good point i don't know yet. What'i know is you're a firbird user and there nothing has been changed accordingly transactions. The old 'mssql' protocol was buggy according the encoding. which means the most peaople di broke using it since D2009+. I can test it since the FreeTDS implementation.
Jan can you verify your suggestion and mak me a little testcase which i can use to prepare the same behavior for DBLIB/ADO?
Posted: 08.11.2012, 12:14
by delphichem
Hi Jan,
I use ntwdblib.dll library, I've SQL SERVER 2008 installed, but when I try ZEOSLIB I get the message that this dll is missing, so I download it.
This is the first time that I use Zeoslib because now it support Unicode.
Regards
Posted: 09.11.2012, 13:10
by marsupilami
Hello delphichem, hello Michael,
Soo - I did some test and for me everything works as expected. This is my testcase:
Code: Select all
procedure TForm1.Button1Click(Sender: TObject);
begin
Connection.Connect;
try
Connection.StartTransaction;
try
sleep(1000);
Query.SQL.Text := 'insert into Sicherungspfad (BENUTZER, PFADANGABE, LETZTESBEARBEITUNGSDATUM) values(:USER, :PATH, :LDAT)';
Query.ParamByName('USER').Value := 'Eichstaedt';
Query.ParamByName('PATH').AsString := DateTimeToStr(now);
Query.ParamByName('LDAT').AsDateTime := now;
Query.ExecSQL;
Connection.Commit;
except
Connection.Rollback;
raise;
end;
finally
Connection.Disconnect;
end;
end;
The ntwdblib is version number 2000.80.2039.0
The ZeosComponents are the ones from
http://svn.code.sf.net/p/zeoslib/code-0 ... es/testing Rev. 1995
In the Connection I set the following properties:
ClientCodepage=WIN1252
ControlsCodePage=cCP_UTF16
LibraryLocation is set to my ntwdblib.dll
TransactIsolationLevel=tiReadCommitted
@delphichem: How does your source differ from mine? Could you prepare a small program to show the problem? Which Version of ntwdblib.dll do you use?
And another hint: Microsoft has discontinued support for ntwdblib.dll. The official Microsoft way is to use ADO / ODBC now, which you can also use from Zeos. Another option you might want to consider is to use the drivers made by the freetds project - which are supported by Zeos too ;o)
@Michael: well - I use Firebird with Delphi mostly. But I have a MSSQL and Sybase ASE project too, where I don't use Zeos (yet) ;o)
Best regards,
Jan
Posted: 09.11.2012, 19:20
by delphichem
Hi thanks Jan for your help, I really appreciate
I tabke your code and with Autocommit = true I get
DBError : [10007] : General SQL Server error: Check messages from the SQL Server.
La requête COMMIT TRANSACTION n'a pas de BEGIN TRANSACTION correspondante.
With Autocommit = False I get
Invalid operation in non AutoCommit mode
I try to connect my BD with FreeTds_MsSQL>=2005 (I use 2008)
when I try to connect I get
None of the dynamic libraries can be found or is not loadable: D:\Dev\Composants\Zeos\lib\freetds\vs2010x64 !
Use TZConnection.LibraryLocation if the location is invalid.
Nota: D:\Dev\Composants\Zeos\lib\freetds\vs2010x64 is the path of the msdblibd.dll and msdblibr.dll
With D:\Dev\Composants\Zeos\lib\freetds\borland I get the same error.
The version of my ntwdblib.dll = 2000.80.2039.0
Best regards,
Hichem
Posted: 09.11.2012, 20:17
by EgonHugeist
delphichem,
None of the dynamic libraries can be found or is not loadable: D:\Dev\Composants\Zeos\lib\freetds\vs2010x64
isn't there something missing? You need the path + library-name.
Today i downloaded the ntwdblib.dll same version... Started our testsuites and OHHH no nice results. Some more bugs like on FreeTDS. Actually i can't say if we have a problem or the ntwdblib.dll because the DBLIB is the same except some constants which differ between TDS\Sybase\mssql. I propose you use FreeTDS instead..
marsupilami,
Jan can you tell me how i can find out the current IP ASE uses? Currently i can only connect via ADO but i want to test this protocol too..
Posted: 09.11.2012, 21:16
by delphichem
EgonHugeist wrote:
isn't there something missing? You need the path + library-name.
When I specify the full path with the file name, D:\Dev\Composants\Zeos\lib\freetds\vs2010x86\msdblibr.dll
I get
Violation d'accès à l'adresse 5F366521. Lecture de l'adresse 00000000
On 64bits version I get
D:\Dev\Composants\Zeos\lib\freetds\vs2010x64\msdblibr.dll !
Use TZConnection.LibraryLocation if the location is invalid.
If I choose Borland one, D:\Dev\Composants\Zeos\lib\freetds\borland\msdblibr.dll
I get
Violation d'accès à l'adresse 057969B8. Lecture de l'adresse 00000000.
So I can't use FreeTDS
Posted: 10.11.2012, 11:00
by EgonHugeist
delphichem,
protocol???? I guess yor're still using mssql as protocol! Choose FreeTds_MsSQL>=2005 and it will work like expected.
Posted: 10.11.2012, 11:50
by delphichem
EgonHugeist wrote:delphichem,
protocol???? I guess yor're still using mssql as protocol! Choose FreeTds_MsSQL>=2005 and it will work like expected.
Hi, as I mentioned in my post before the last, I try protol FreeTds_MsSQL>=2005 because I use MSSql 2008, I don't undestand what happens, is this issue happens only for me???? I use Zeos last SVN from yestoday
Posted: 10.11.2012, 12:48
by EgonHugeist
delphichem,
Hi, as I mentioned in my post before the last, I try protol FreeTds_MsSQL>=2005 because I use MSSql 2008, I don't undestand what happens, is this issue happens only for me???? I use Zeos last SVN from yestoday
hmm it seems so. I use the 2012 developer edition but i know others who have no such issues. Did you try copy the dll to system32\syswow64 and fogett about libraryloaction?
Posted: 10.11.2012, 13:00
by miab3
I use:
Microsoft ® SQL Server ® 2008 R2 SP1 - Express Edition Version: 10.50.2500.0
and connect to it via Zeos-FreeTDS with:
Delphi2006;
Delphi2007 / C++;
DelphiXE2 32/64/C++;
Lazarus Win 2.7.1 32/64;
Lazaruz Linux 2.7.1 Debian 64
Michal
Posted: 10.11.2012, 13:18
by delphichem
EgonHugeist wrote:delphichem,
hmm it seems so. I use the 2012 developer edition but i know others who have no such issues. Did you try copy the dll to system32\syswow64 and fogett about libraryloaction?
I copied the DLL to System32 ans syswow64, and delete librarylocation, but I get the same exception as mentionned
Nota: I try the borland, vs2010x64 and vs2010x86 libraries