[patch_done] MSSQL transaction

Forum related to MS SQL Server

Moderators: gto, cipto_kh, EgonHugeist

delphichem
Fresh Boarder
Fresh Boarder
Posts: 10
Joined: 07.11.2012, 20:08

[patch_done] MSSQL transaction

Postby delphichem » 07.11.2012, 20:31

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

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1739
Joined: 31.03.2011, 22:38

Postby EgonHugeist » 07.11.2012, 23:33

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..
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image

delphichem
Fresh Boarder
Fresh Boarder
Posts: 10
Joined: 07.11.2012, 20:08

Postby delphichem » 08.11.2012, 00:40

Hi, thank you :)

marsupilami
Gold Boarder
Gold Boarder
Posts: 658
Joined: 17.01.2011, 14:17

Postby marsupilami » 08.11.2012, 07:57

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

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1739
Joined: 31.03.2011, 22:38

Postby EgonHugeist » 08.11.2012, 09:11

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?
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image

delphichem
Fresh Boarder
Fresh Boarder
Posts: 10
Joined: 07.11.2012, 20:08

Postby delphichem » 08.11.2012, 12:14

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
Gold Boarder
Gold Boarder
Posts: 658
Joined: 17.01.2011, 14:17

Postby marsupilami » 09.11.2012, 13:10

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
Fresh Boarder
Fresh Boarder
Posts: 10
Joined: 07.11.2012, 20:08

Postby delphichem » 09.11.2012, 19:20

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

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1739
Joined: 31.03.2011, 22:38

Postby EgonHugeist » 09.11.2012, 20:17

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..
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image

delphichem
Fresh Boarder
Fresh Boarder
Posts: 10
Joined: 07.11.2012, 20:08

Postby delphichem » 09.11.2012, 21:16

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

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1739
Joined: 31.03.2011, 22:38

Postby EgonHugeist » 10.11.2012, 11:00

delphichem,

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

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image

delphichem
Fresh Boarder
Fresh Boarder
Posts: 10
Joined: 07.11.2012, 20:08

Postby delphichem » 10.11.2012, 11:50

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

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1739
Joined: 31.03.2011, 22:38

Postby EgonHugeist » 10.11.2012, 12:48

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?
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image

miab3
Zeos Test Team
Zeos Test Team
Posts: 941
Joined: 11.05.2012, 12:32
Location: Poland

Postby miab3 » 10.11.2012, 13:00

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
Fresh Boarder
Fresh Boarder
Posts: 10
Joined: 07.11.2012, 20:08

Postby delphichem » 10.11.2012, 13:18

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
:cry:


Return to “MS SQL Server”

Who is online

Users browsing this forum: No registered users and 1 guest