ZDBC connection to MSSQL LocalDB

[Alfa000]

Hello.

I need connect to SQL Server Express LocalDB with your IZConnection (i need only ZDBC api). How use connection string for this connection?

For testing purpose i create this simple code with TAdoConnection. This code works perfect.

Code: Select all

  con1 := TADOConnection.Create(Self);
  con1.ConnectionString := 'Provider=SQLNCLI11.1;UID=a;PWD=b;Server=(localdb)\test;Database=mydb;MARS_Connection=yes';
  con1.Connected := True;

  iTmp := 0;
  con1.Execute('INSERT INTO tab1 (ID, value) VALUES (1, 2);', iTmp);

  Assert(iTmp = 1);

How i do this same stuff with IZConnection and IZStatement objects?

Sorry for my poor english.

[EgonHugeist]

Use the TZURL.Database for the Connection-String.

[Alfa000]

Yes, this works.

Code: Select all

var
  u: TZURL;
  con: IZConnection;
  stat: IZStatement;
  iTmp: Integer;
begin
  u := TZURL.Create;
  u.Protocol := 'ado';
  u.HostName := '';
  u.Database := 'Provider=SQLNCLI11.1;UID=a;PWD=b;Server=(localdb)\test;Database=mydb;MARS_Connection=yes';
  u.UserName := '';
  u.Password := '';
  u.Port := 0;

  con :=  DriverManager.GetConnection(u.URL);
  stat := con.CreateStatement;
  iTmp := stat.ExecuteUpdate('INSERT INTO tab1 (ID, value) VALUES (1, 2);');
  Assert( iTmp = 1);

Thanks.

[Alfa000]

Hello EgonHugeist. How i do the same connection to MSSQL LocalDB througth new OLEDB support (initialized now in testing-7.3 branch)?

Thanks for reply.

[EgonHugeist]

So it's no longer a developers secret?!

http://zeoslib.sourceforge.net/viewtopi ... 48&t=20529

[Alfa000]

Maybe is it still I am not authorised to read this forum

[EgonHugeist]

Sorry didn't noticed the devmember-permissions.... Since i'm one of them.

Forum is unlocked, now. Accordingly your question: Use it like ADO.

Database should be your connect-string.
User and PWD are supported in Connect-string or manual with the ZURL.[public properties].

Happy testing!

[Alfa000]

Ok, if i use this code:

Code: Select all

var
  u: TZURL;
  con: IZConnection;
  stat: IZStatement;
  iTmp: Integer;
begin
  u := TZURL.Create;
  u.Protocol := 'oledb';
  u.Database := 'Provider=SQLNCLI11.1;UID=a;PWD=b;Server=(localdb)\test;Database=mydb;MARS_Connection=yes';

  con :=  DriverManager.GetConnection(u.URL); //This line raise exception because TZAbstractConnection.FIZPlainDriver is nil

exception is riased during getconnection. For some reason FIZPlainDriver in TZAbstractConnection is not assigned

[EgonHugeist]

Propose you open a new thread in OleDB section.

Accordingly your issue:

I think the Driver was not found.. Set a breakpoint to ZDbcOleDB.pas in initialization block and check if the TZOleDBDriver was created.
I do NOT have a problem to use the DBC Driver. So take care ZDbcOleDB.pas could be found from your IDE. Best practice: add ZDbcOleDB to your uses list of your project.

[Alfa000]

Thanks for reply. I trace TZOleDBDriver create process. This was good, i use ZDbcOleDB.pas correctly. But i was finally found reason of issue. Protocol is case sensitive . 'oledb' not work, but 'OleDB' work properly.

At the end, this code work

Code: Select all

var
  u: TZURL;
  con: IZConnection;
  stat: IZStatement;
  RS: IZResultSet;
begin
  u := TZURL.Create;
  u.Protocol := 'OleDB';
  u.Database :='Provider=SQLNCLI11.1;UID=a;PWD=b;Server=(localdb)\test;Database=mydb;MARS_Connection=yes';

  con :=  DriverManager.GetConnection(u.URL);
  stat := con.CreateStatement;
  RS := stat.ExecuteQuery('SELECT @@MAX_CONNECTIONS AS "Max Connections"');
  Assert( RS.Next);
  ShowMessage(IntToStr(RS.GetInt(1))); ;

Great work, i will continue with OleDB testing

[Alfa000]

What about blob?

If i define table:

Code: Select all

CREATE TABLE "testtable" (
	"Id" INT IDENTITY(1,1) PRIMARY KEY,
	"My_memo" NVARCHAR(MAX) NULL DEFAULT NULL
);

If table has one record {"Id": 1, "My_memo": NULL} and if i execute this select:

Code: Select all

SELECT "Id" FROM testtable;

all works ok.

But if i execute this:

Code: Select all

SELECT "Id","My_memo"  FROM testtable;

then raise error "OLEDB Error 80040E08 - ERROR: Binding information is invalid." in TZOleDBResultSet.Next method.

[EgonHugeist]

Please open a new thread for new issues.

Does this issue still remain? I did introduce the ISequentialStream reading for the Lobs (Which doesn't match this field -> no LOB its a oversized Unicode-varychar-Field. Lob would be NTEXT)