ODBC, mssql and dbexpress

[Connie McBride 0]

Using Delphi 10 Seattle with dbExpress and Delphi 11 Alexandria with both dbExpress and firedac

connecting to : sqlserver 2008 - 2022

I am using an ini to initialize the connections, and now I need to change it from using the native client libraries to the ODBC library.

I keep getting 'unknown driver message, with the unknown driver set to whatever I set drivername to

(I have tried odbc and ODBC Driver 17 for SQL Server)

 

 

at one point, I didn't get a connection error anymore, but doing the first query returned an error running the first query on a view, saying the view wasn't found.

 

Can someone please help fix?

 

[QC2022]
DriverUnit=Data.DBXOdbc
DriverPackageLoader=TDBXOdbcDriverLoader,DBXOdbcDriver230.bpl
DriverAssemblyLoader=Borland.Data.TDBXOdbcDriverLoader,Borland.Data.DbxOdbcDriver,Version=23.0.0.0,Culture=neutral,PublicKeyToken=91d62ebb5b0d1b1b
MetaDataPackageLoader=TDBXOdbcMetaDataCommandFactory,DbxOdbcDriver230.bpl
MetaDataAssemblyLoader=Borland.Data.TDBXOdbcMetaDataCommandFactory,Borland.Data.DbxOdbcDriver,Version=23.0.0.0,Culture=neutral,PublicKeyToken=91d62ebb5b0d1b1b
GetDriverFunc=getSQLDriverMSSQL
LibraryName=dbxmss.dll
VendorLib=msodbcsql17.dll
DriverName = ODBC Driver 17 for SQL Server
MaxBlobSize=50000000
OSAuthentication=False
PrepareSQL=True
ErrorResourceFile=
SchemaOverride=%.dbo
Schema=dbo
HostName=pv06
Database=qcLocal
blobsize=-1
localecode=0000
isolationlevel=ReadCommitted
os authentication=False
prepare sql=False
connecttimeout=60
mars_connection=False
ConnectionString=Driver={ODBC Driver 17 for SQL Server};Server=pv06;DAtabase = qclocal;schema=dbo;Trusted_Connection=yes;

 

[Die Holländer 20]

Did you try to add and test the connection by the Delphi Data Explorer? (Menu-View-Tool Windows-Data Explorer)

[Connie McBride 0]

not sure how to answer that, but when I double click on the odbcConnection, it tells me 

note: I don't want to set up an actual connection, just want to get the ini method to work

[Connie McBride 0]

I setup the connection string:

Driver={ODBC Driver 17 for SQL Server};Server=pv06;DAtabase = qclocal;schema=dbo;Trusted_Connection=yes;

double clicked on it, and opened an sql window.

setup a query (view exists in the dbo schema), and got this:

 

 

[Die Holländer 20]

 

If you add the connection by the data explorer you get a wizard that can help you to get the proper parameters for your .ini file

Are you sure that the database parameter in your ini must be ";DAtabase = qclocal;" (not sure if it is case sensitive.. DAtabase?)

 

[Connie McBride 0]

I don't seem to have a connection issue with firedac.

I am having the connection issue with dbexpress (I am in process of converting to firedac, so currently have both connections)

when I use the data explorer (using dbexpress), this is how I set it up:

 

I have also tried:

when I click 'test connection', I get:

 

 

 

[H05 0]

This may not apply given I'm using MS Sql and not dbexpress but in your connection string do you need the Provider? This is mine when connecting using a "trusted connection". 

 

Where server/db need to be populated still:
'Provider=MSOLEDBSQL;Driver={ODBC Driver 17 for SQL Server};Persist Security Info=False;Server=%s;Database=%s;Integrated Security=SSPI;'

 

[Connie McBride 0]

I managed to get the odbc to work for the dbexpress connection by setting the connection string in the ini file to:

#ConnectionString=Driver={ODBC Driver 17 for SQL Server};server=pv06;database=qcLocal;uid=ccm;pwd=ccm
ConnectionString=dsn=qclocal;database=qcLocal;

 

 

 

of course, now the firedac connection doesn't work....

(still working on that)