Page 1 of 1
ZDBC connection to MSSQL LocalDB
Posted: 02.11.2013, 00:18
by 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.
Re: ZDBC connection to MSSQL LocalDB
Posted: 09.11.2013, 17:54
by EgonHugeist
Use the TZURL.Database for the Connection-String.
Re: ZDBC connection to MSSQL LocalDB
Posted: 10.11.2013, 14:30
by 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.
Re: ZDBC connection to MSSQL LocalDB
Posted: 30.12.2014, 16:43
by 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.
Re: ZDBC connection to MSSQL LocalDB
Posted: 01.01.2015, 20:47
by EgonHugeist
Re: ZDBC connection to MSSQL LocalDB
Posted: 02.01.2015, 10:09
by Alfa000
Maybe is it still
I am not authorised to read this forum
Re: ZDBC connection to MSSQL LocalDB
Posted: 02.01.2015, 20:00
by 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!
Re: ZDBC connection to MSSQL LocalDB
Posted: 06.01.2015, 19:24
by 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
Re: ZDBC connection to MSSQL LocalDB
Posted: 06.01.2015, 19:46
by 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.
Re: ZDBC connection to MSSQL LocalDB
Posted: 06.01.2015, 21:39
by 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
Re: ZDBC connection to MSSQL LocalDB
Posted: 07.01.2015, 20:35
by 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:
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.
Re: ZDBC connection to MSSQL LocalDB
Posted: 15.01.2015, 22:43
by 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)
