ZDBC connection to MSSQL LocalDB

Forum related to MS SQL Server

Moderators: gto, cipto_kh, EgonHugeist

Alfa000
Fresh Boarder
Fresh Boarder
Posts: 17
Joined: 05.12.2012, 09:09

ZDBC connection to MSSQL LocalDB

Postby Alfa000 » 02.11.2013, 00:18

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.
Daniel Andrascik

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

Re: ZDBC connection to MSSQL LocalDB

Postby EgonHugeist » 09.11.2013, 17:54

Use the TZURL.Database for the Connection-String.
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

Alfa000
Fresh Boarder
Fresh Boarder
Posts: 17
Joined: 05.12.2012, 09:09

Re: ZDBC connection to MSSQL LocalDB

Postby Alfa000 » 10.11.2013, 14:30

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. 8)
Daniel Andrascik

Alfa000
Fresh Boarder
Fresh Boarder
Posts: 17
Joined: 05.12.2012, 09:09

Re: ZDBC connection to MSSQL LocalDB

Postby Alfa000 » 30.12.2014, 16:43

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.
Daniel Andrascik

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

Re: ZDBC connection to MSSQL LocalDB

Postby EgonHugeist » 01.01.2015, 20:47

So it's no longer a developers secret?! :D

viewtopic.php?f=48&t=20529
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

Alfa000
Fresh Boarder
Fresh Boarder
Posts: 17
Joined: 05.12.2012, 09:09

Re: ZDBC connection to MSSQL LocalDB

Postby Alfa000 » 02.01.2015, 10:09

Maybe is it still :wink: I am not authorised to read this forum :(
You do not have the required permissions to view the files attached to this post.
Daniel Andrascik

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

Re: ZDBC connection to MSSQL LocalDB

Postby EgonHugeist » 02.01.2015, 20:00

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!
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

Alfa000
Fresh Boarder
Fresh Boarder
Posts: 17
Joined: 05.12.2012, 09:09

Re: ZDBC connection to MSSQL LocalDB

Postby Alfa000 » 06.01.2015, 19:24

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 :shock:
Daniel Andrascik

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

Re: ZDBC connection to MSSQL LocalDB

Postby EgonHugeist » 06.01.2015, 19:46

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

Alfa000
Fresh Boarder
Fresh Boarder
Posts: 17
Joined: 05.12.2012, 09:09

Re: ZDBC connection to MSSQL LocalDB

Postby Alfa000 » 06.01.2015, 21:39

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 :shock: . 'oledb' not work, but 'OleDB' work properly.

At the end, this code work :up:

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 8)
Daniel Andrascik

Alfa000
Fresh Boarder
Fresh Boarder
Posts: 17
Joined: 05.12.2012, 09:09

Re: ZDBC connection to MSSQL LocalDB

Postby Alfa000 » 07.01.2015, 20:35

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.
Daniel Andrascik

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

Re: ZDBC connection to MSSQL LocalDB

Postby EgonHugeist » 15.01.2015, 22:43

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)
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


Return to “MS SQL Server”

Who is online

Users browsing this forum: No registered users and 1 guest