[Success] with Freespider reading data fromMS SQL via ODBC
lazarus / 38-Networking and Web Programming

----------------------------------------------------------------------
tjpren · 2012-01-16 01:30

All,

I've had great fun and success with Freespider, and have included
source code for others, if interested.

Basically, I'm compiling a web page that propmpts the user for a basic
search field, and using that search field to return a query from MS
SQL 2005.  The query result is displayed on a web page.  I'm using
ODBC for the connection (hence the SQLdb package has to be added to
the IDE).

I think FreeSpider is an excellent module, and hope the developer
continues with the work in developing it's useability.

I'm using Komposer to develop the HTML code for the page thast
displays the data, using the FreeSpider developed Tags.

Interested in any other web projects that others have developed with
FreeSpider.

Regards

----------------------------------------------------------------------
motaz · 2012-01-16 07:51

Glad to know that.
I've used FreeSpider for a web service application (Multi-tier as a
middle tier), to perform actions in MySQL database. clients are
applications that connect to FreeSpider application using HTTP
component written in Delphi.

I've rewritten this module using Java Servlet because the team members
were using Java. Servlet version performance was very bad and
unreliable. I always get database connection error when jobs are too
huge in the web service. I couldn't solve this problem (I'm new to
Java), and that application was very critical and it was working in a
telecom company in core network.
Finally I have returned back FreeSpider version of that application
and it works fine in heavy load since then. It work for more than a
year and a half, executing millions of requests.

----------------------------------------------------------------------
tjpren · 2012-01-16 09:38

Hi Motaz,

I'm pretty locked in to reading and displaying data from MS SQL.  Our
work platforms are all MS SQL, so as a result, I have to develop
around this. 

Till recently, I was working with VB.net, but have become recently
converted to Open Pascal via Lazarus.

Modules are a little bit restricted in connection to proprietry
systems (ie MS), but I've generally found workarounds - eg ODBC to
connect.  Have you any experience with MS databases?

Regards

----------------------------------------------------------------------
BigChimp · 2012-01-16 13:54

Tjpren,

Lacak2 has been developing an MSSQL connection component for SQLDB
that uses FreeTDS to natively connect to MSSQL (it should also work
for Sybase ASE).

You might be interesting in testing that.

See forum post
http://lazarus.freepascal.org/index.php/topic,15135.0.html
(if using Windows, IIRC Lacak2 has a compiled FreeTDS dll somewhere in
that thread)
and bug report with latest code:
http://bugs.freepascal.org/view.php?id=17303

----------------------------------------------------------------------
tjpren · 2012-01-16 23:35

Thanks BigChimp,

I'll give it a go.  I have to use Windows (7) as our company has
standardised on that platform.

However, I'm just needing some clarification -
1. do I need to download and compile FreeTDS, or use the FreeTDS.dll
2. how do I actually use the RC2 files - I've added packages to the
IDE lately.  Do I paste the files into the SQLdb package directory,
and re-add the SQLDIB package, or 3. do I put them directly into the
project directory.

Regards

----------------------------------------------------------------------
BigChimp · 2012-01-17 10:01

You may need to use Windows, but do you need to use MSSQL as well? 
(e.g. PostgreSQL and Firebird run well on Windows; Firebird has an
embedded version that allows running without network connectivity but
with the same performance as regular Client/Server Firebird)
<rant over>

1. You can use FreeTDS.dll; you'll probably also need libiconv2.dll.
Note that there are a lot of older versions floating around that miss
some bugfixes. The latest mantis bugtracker file (mssqlconn_rc2.zip)
has those dlls already.
2. You can use the connector by itself, no need to install packages
into Lazarus (add use mssqlconn, use something like FConnection :=
TMSSQLConnection.Create; the mssqlconn readme also says you can set
the property ConnectorType=MSSQLServer).
3. Yes, you can put the connector source files directly in the project
directory.

If you want to do visual design with Lazarus, I don't know whether the
SQLConnector component would pick up the mssqlconn connector in your
project directory; you might need to put mssqlconn etc in the sqldb
sources directory and recompile, but haven't tried that.
Probably Lacak2, md or ludob would know that...

For a command line example, see my simple SQL command line processor
https://bitbucket.org/reiniero/fpc_laz_patch_playground/src,
subdirectory SQL)

Good luck

----------------------------------------------------------------------
tjpren · 2012-01-18 22:44

Hello BigChimp,

Unfortunatley, my work only uses MS products, so I'm limited in my
choice of DB,
although I have some latitude in the selection of tools.

I tried the RC2 package, and it worked quite well in my limited
testing, with
connection to an MS SQL2005 database.  I was able to run a simple
query, a stored
procedure, and connect to a View.

Generally, the Lazarus IDE GUI environment was fine, although it
lacked the
mssqlconn object. Also, I was doing it within the framework of the
FreeSpider,
and it also performed well.

If anyone else is interested, my steps were:
1. Download the RC2 package from the above link.
2. Register the dlls in the Windows/System32 directory
3. Copy the *.pp to both your project directory, and Lazarus Sqldb
package
directory
4. Start up Lazarus, and either install or uninstall/reinstall the
Sqldb
package
5. Within the project, I used a SQLConnector, SQLTransaction,
Datasource, and SQLQuery
6. Add mssqlconn; to the Uses section at the start of the project
7. Added before the Implementation section:
   Var   
   FConnection: TMSSQLConnection;
8. Added the following connections in the body of the code:
      FConnection := TMSSQLConnection.Create(nil);
      FConnection.CharSet:='UTF-8';
      FConnection.HostName:='XP-PC';
      FConnection.DatabaseName:='xppc';
      FConnection.UserName:='sa';
      FConnection.Password:='admin1';  
9. Also noticed that I had to add exactly the same info the
SQLConnector
10. On first compliation I got an error in mssqlconn.pp with respect
to:
   PBCD(buffer)^:=StrToBCD(bcdstr, FSQLFormatSettings);
//PBCD(buffer)^:=dbnumerictobcd(dbnum);
Didn't know what it meant, so for a test, I commented it out with //. 
This was the
mssqlconn.pp in the project directory.
11. Recompiled without any error, although I got a lot of hints about
missing
parameters not used.

I look forward to the module being added formally to the Sqldb
package, and the GUI
environment.

I'll continue testing, and see if I can refine my steps - I wonder if
I can do away
with 2, and 9 as an example.  I also wonder if I can dispense with
having the *.pp's in
both the project and package directories.

kind regards