Recent

Author Topic: ODBC nightmare  (Read 4303 times)

Zath

  • Sr. Member
  • ****
  • Posts: 337
ODBC nightmare
« on: May 15, 2016, 11:28:38 pm »
I've read all sorts of examples on how to connect ODBC to MS SQL in Lazarus.
I've even contemplated going back to D2007 and ADO because I had that working... Grrr.

Can someone tell me in words of one syllable how to do it please ?

Assume I have a form with an ODBCConnection, SQLQuery, SQLTransaction, DBEdit.  (do I need these or something else ?)

Details are :-

ZATH-PC\SQLEXPRESS
user= abcd
pwd= 1234

Database = LOM2Res
Table name = Monsters
Field = Fld_one
Driver = ODBC Driver 11 for SQL Server

What properties should I populate ?
Many examples seem to want to populate the properties from code instead of within the component at design time. Why ?

How do I connect to the table to get the DBedit to show Fld_one ?

Thanks



LacaK

  • Hero Member
  • *****
  • Posts: 577
Re: ODBC nightmare
« Reply #1 on: May 16, 2016, 07:21:34 am »
What properties should I populate ?
Many examples seem to want to populate the properties from code instead of within the component at design time. Why ?
No problem set properties using form designer ...
Try look at http://wiki.freepascal.org/ODBCConn

Zath

  • Sr. Member
  • ****
  • Posts: 337
Re: ODBC nightmare
« Reply #2 on: May 16, 2016, 09:54:49 am »
Thanks Lacak. I've already been to that page before.
Tried what was said and still failed.
One of the links to the mssql option seems to go to a commercial site. I don't really want to buy anything.
I will have another go and try the connectionstrings.com link. Might get something click differently this time.

miab3

  • Full Member
  • ***
  • Posts: 103
Re: ODBC nightmare
« Reply #3 on: May 16, 2016, 10:37:01 am »

Zath

  • Sr. Member
  • ****
  • Posts: 337
Re: ODBC nightmare
« Reply #4 on: May 16, 2016, 10:52:39 am »
I've seen Zeos in the forum and considered it for an ADO connection.
Is that what you're suggesting ?

Do I go to packages/lazarus ?

miab3

  • Full Member
  • ***
  • Posts: 103
Re: ODBC nightmare
« Reply #5 on: May 16, 2016, 02:31:06 pm »
I'm talking about ZEOS 7.3 and MSSQL ODBC and OleDB.

Michal

LacaK

  • Hero Member
  • *****
  • Posts: 577
Re: ODBC nightmare
« Reply #6 on: May 16, 2016, 02:55:47 pm »
In case of TODBCConnection there is important:
DatabaseName which converts to DSN=
UserName which converts to USR=
Password which converts to PWD=
in connection string ...
So chceck these settings.

eny

  • Hero Member
  • *****
  • Posts: 1588
Re: ODBC nightmare
« Reply #7 on: May 16, 2016, 10:14:24 pm »
In case of TODBCConnection there is important:
DatabaseName which converts to DSN=
UserName which converts to USR=
Password which converts to PWD=
in connection string ...
So chceck these settings.
The ODBC connection has a Params property. Set the parameters in there, especially the database name.
The property DatabaseName does not work for MS SQL connections (it's a bug).
And as LacaK metioned use DSN=<name> and not DatabaseName.

Or on Window$ set up an ODBC connection via the config utility.
All posts based on: Win10 (Win64); Lazarus 1.8.0 'stable' (#56594 win64) unless specified otherwise...

Zath

  • Sr. Member
  • ****
  • Posts: 337
Re: ODBC nightmare
« Reply #8 on: May 17, 2016, 12:21:04 am »
I have set up the ODBC connection with the utility. That's an easy job.

Quote
The property DatabaseName does not work for MS SQL connections (it's a bug).
And as LacaK metioned use DSN=<name> and not DatabaseName.

This explains some of the problems I was having then. Reading all the info, it says ODBC doesn't use DSN.


LacaK

  • Hero Member
  • *****
  • Posts: 577
Re: ODBC nightmare
« Reply #9 on: May 17, 2016, 07:18:41 am »
hm,
if you set TODBCConnection.DatabaseName then it is used for DSN= in connection string.
So it is used, but is used for Data Source Name as defined in ODBC driver manager.
(if you do not define named connection in ODBC driver manager then of course DatabaseName is useless)

if you want setup Database as per MS SQL Server https://msdn.microsoft.com/en-us/library/ms130822.aspx you must add to TODBCConnection.Params:
'Database=mydatabase'
and probably:
'Server=myserver\myinstance'