Can't activate the SQLQuery [MSSQL via ODBC]
lazarus / 16-Databases

----------------------------------------------------------------------
Uncle Tom · 2013-04-22 14:48

Hi All,

I'm a Newbie to Lazarus (worked with Delphi more than 10 years ago
...).
I've spend all day now to search the forums ... with no result ... so
here I am.

I'm working on an application that needs to connect to a MSSQL
Database (MSSQL 2008 R2).
Here is my situation:

1. I've created an ODBC entry for the MSSQL Database.
2. I added the ODBCConnection component to my form and applied the
connection data
3. I added a Datasource and SQLQuery component and linkend them.
4. Also added a SQLTransaction component and linked it to the others
as well.

I activated the connection and datasource successfully.

... all fine and dandy ... so far.

In the SQL property of the SQLQuery I can enter my SQL statement and
even get a result from the connected database.

I added a DBGrid component to my form, linked it with my datasource.

Now if I run my application, I can't set the Active property of the
SQLQuery component to true
      [ SQLQuery1.Close;
        SQLQuery1.SQL.Clear;
        SQLQuery1.SQL.Text := 'select * from FixNames';

       SQLTransaction1.Active := True;
        SQLQuery1.Active := True;
        SQLQuery1.Open; ]

and hence the DBGrid does not show any content.

I'm also unable to activate the SQLQuery component in the IDE as well.

Before I start a discussion, why not to use the MSSQLConnection
component -> I would need to recompile the fpc, since the dblib.dll is
not set to "ntwdblib.dll" ... and I have no clou how to do so ...

I think I'm missing something minor, since the SQL statement works
fine in the SQL property.

Maybe one of you Pros have a idea ...

Thank you all for your help.

----------------------------------------------------------------------
BigChimp · 2013-04-22 15:05

Quote from: Uncle Tom on April 22, 2013, 04:48:29 pmBefore I start a
discussion, why not to use the MSSQLConnection component -> I would
need to recompile the fpc, since the dblib.dll is not set to
"ntwdblib.dll" ... and I have no clou how to do so ...
http://wiki.lazarus.freepascal.org/mssqlconn#Installation
You could simply download FreeTDS dblib. No idea what you mean with
"dblib.dll being set to ntwdblib.dll"

----------------------------------------------------------------------
Uncle Tom · 2013-04-22 15:52

Hey BigChimp,

thank you for your quick response.
The good news, the MSSQLConnection is working perfectly!

Unfortunately the problem with activating the SQLQuery persists.

Any ideas anyone?

----------------------------------------------------------------------
BigChimp · 2013-04-23 05:23

You could post a small demo project via project/publish projects as a
zip with the complete code that shows this problem (e.g. against the
MS SQL master database).

I'm assuming you're not seeing any errors/exceptions when running or
debugging the code?

Thanks

----------------------------------------------------------------------
Uncle Tom · 2013-04-23 06:29

Good Morning All,

I've attached the project as .zip file.

No, unfortunately I do not get an error or exception -> If so, this
would make things maybe a bit easier.
Just let me know, if you need more information.

Thanks

----------------------------------------------------------------------
BigChimp · 2013-04-23 06:51

Right, no DDL attached. Never mind, I'll rebuild it...

----------------------------------------------------------------------
BigChimp · 2013-04-23 07:29

Don't fill in the query's datasource. That is only used for
master/detail: http://wiki.lazarus.freepascal.org/MasterDetail

I suspect that setting the unidirectional property of the query won't
be appreciated by the datasource and its bound controls
(datagrid,dbnavigator): you can only go forward not backward, in the
dataset.

See SQLDB tutorial1 & 2 for more info on how to set up db GUI
applications.
Edit: see http://wiki.lazarus.freepascal.org/mssqlconn for links to
mssqlconn documentation and some more info

Also, I:
- added some comments in the code; commented out duplicate
query.active etc
- cosmetic: reformatted the code with the source/Jedi Code format tool
- Set some parameters according to mssqlconn help (see comments)

Works for me with laz 1.0.8/fpc 2.6.0 and laz trunk/fpc trunk.

----------------------------------------------------------------------
Uncle Tom · 2013-04-23 09:44

Hey BigChimp,

... you're my hero of the day
I knew it was something minor, as assigning the Datasource in the
SQLQuery.
That did it.
Also your comments are very much appreciated.

Thank you for your quick and professional support.

I might be back for more 

Cheers

----------------------------------------------------------------------
BigChimp · 2013-04-23 09:46

Thanks & glad it's working