[patch_done] MSSQL transaction

[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

[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..

[delphichem]

Hi, thank you

[marsupilami]

Hello Michael, hello delphichem,

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

[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?

[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

[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

[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

[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..

[delphichem]

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

[EgonHugeist]

delphichem,

protocol???? I guess yor're still using mssql as protocol! Choose FreeTds_MsSQL>=2005 and it will work like expected.

[delphichem]

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

[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?

[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

[delphichem]

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