Brook 1.0 - SQLdb Driver [RE-OPENED]
lazarus / 38-Networking and Web Programming

----------------------------------------------------------------------
jamhitz · 2013-01-30 18:51

Team,

I have been playing around with Brook Framework and I must say I am
thoroughly impressed so far!!

I have a question: Most (all?) of the demos have a SqlLite3 schema and
implementation; a number may have PostgreSQL schemas but no
implementation; nothing at all for the other DBMS... but of course the
whole point of a demo is to pick up from there and figure it out.
However, I cannot figure out how to use SqlDB to connect to any other
database but SQLLite:

Code: [Select]BrookSettings.Configuration := 'library=sqldb;driver=pqc
onnection;host=127.0.0.1;user=USERNAME;password=PASSWORD;database=TEST
DB';
I have tried replacing the driver with PQ, Postgres, PostgreSQL,
MySQL55, MySQL51.. (of course each to its respective server) nothing
works. However dropping a TPQConnection or a TMysql5*conn on a form
and setting the proper parameters works fine.

What am I missing?

Thanks.

Kind regars
JamHitz

----------------------------------------------------------------------
Silvio Clécio · 2013-01-30 18:56

Hello friend,

In brokers unit, just declare your connection unit, eg:

https://github.com/silvioprog/brook-
site/blob/master/src/release/brokers.pas

Good luck!

----------------------------------------------------------------------
Silvio Clécio · 2013-01-30 18:57

... and change your configuration to:

Code: [Select]BrookSettings.Configuration := 'library=sqldb;driver=pos
tgresql;host=127.0.0.1;user=USERNAME;password=PASSWORD;database=TESTDB
';

----------------------------------------------------------------------
jamhitz · 2013-01-30 19:09

@SilvioProg

Eureka!!!! Can you see the smile?     I am intent on writing a small
(VEEEEERY basic) 101 tutorial on using  Brook for web programming
using FPC/Laz. I hope in this way I will play my little part in making
Brook what I  believe it has the potential to become - the number one
Web Programming Framework for FPC/Laz. In the meantime I stumbling,
falling and finding my way around using the demos and the
documentation.

Thank you.

Kind regards
JamHitz

----------------------------------------------------------------------
Silvio Clécio · 2013-01-30 19:29

Very nice friend! I saw the smile.

What the link to the site where you will post? We may publish it in
the official community of Lazarus on Facebook, GooglePlus etc.

----------------------------------------------------------------------
jamhitz · 2013-01-30 19:37

I will most probably post it to the Lazarus wiki and/or on the
BrookFramework website.

----------------------------------------------------------------------
Silvio Clécio · 2013-01-30 19:39

Great! 

It will be posted in future blog of Brook too!

----------------------------------------------------------------------
jamhitz · 2013-01-31 16:35

I can now be able to connect to a DB. However, attempting to use
TBrookTable or TBrookQuery (by alternating the commented lines):

Code: [Select]procedure TMyAction.Get;
var db: TBrookDatabase;
    q: TBrookQuery;
    t: TBrookTable;
begin
    BrookSettings.Configuration := 'library=sqldb;driver=postgresql;ho
st=SERVER;user=USER;password=PASS;database=db';
    try
        db := TBrookDatabase.Create;
        //q := TBrookQuery.Create(db);
        t := TBrookTable.Create(db, 'gateway');
        try
            WriteLn(t.AsJSON);

            //q.SQL.Text := 'SELECT id,name,description from gateway';
            //q.Open;
            //WriteLn(q.AsJSON);
        except
            on E:Exception do
                Write('ERROR:' + E.Message);
        end;
    finally
        FreeAndNil(db);
        FreeAndnil(q);
        FreeAndNil(t);
    end;

...brings up an error Premature end of script headers: cgi3 .

Ideas?

Thank you.

----------------------------------------------------------------------
Silvio Clécio · 2013-02-02 03:38

Don't use BrookSettings.Configuration in actions, use it on the
brokers unit.

You can facilitate decreasing the coding:

...
TMyAction = class(TBrookDBAction)
...

procedure TMyAction.Get;
begin
  Write(Table.Open.AsJSON); // or Table.AsJSON directly
end;
...
initialization
  TMyAction.Register('gateway', '*');

----------------------------------------------------------------------
jamhitz · 2013-02-02 10:55

@Silvio

I've created a brand new project that uses TBrookDBAction instead of
TBrookAction, but I still get the same error. Here is the broker:

Code: [Select]unit brokers007;

{$mode objfpc}{$H+}

interface

uses
  BrookFCLCGIBroker, BrookUtils, BrookSQLdbBroker, PQConnection;

implementation

initialization
        BrookSettings.Charset := 'library=sqldb;driver=postgresql;host
=127.0.0.1;user=USERNAME;password=PASSWORD;database=TESTDB';
        BrookSettings.Page404 := '404.html';
        BrookSettings.Page500 := '500.html';
end.

..and here is the action:

Code: [Select]unit umain007;

{$mode objfpc}{$H+}

interface

uses
  BrookDBAction, fpJSON;

type
  TMyAction = class(TBrookDBAction)
  public
      procedure Get; override;
  end;

implementation

{ TMyAction }

procedure TMyAction.Get;
begin
    Write(Table.Open.AsJSON);
end;

initialization
  TMyAction.Register('gateway','*');

end.

See the attachment for the response.

JamHitz

----------------------------------------------------------------------
Silvio Clécio · 2013-02-05 18:02

Hello, sorry for my delay.

So, try to run the demo
"\repository\git\brookframework\demos\simple\helloworld\cgi\cgi1.lpi".
I need to find out if the error is in your DB or in your HTTP server.

----------------------------------------------------------------------
jamhitz · 2013-02-06 21:54

Hi

I have tested MOST of the demos in the /simple, /http and /others sub-
directories.  They all work as expected. It is those in the /db sub-
directory that are a problem.

OpenSuse 12/Lazarus 1.1/fpc 2.7/PostgreSQL 9.1/MySQL 5.5

JamHitz

----------------------------------------------------------------------
Silvio Clécio · 2013-02-06 23:16

You can connect using pure SQLdb? Take a test, add a SQLConnector1 in
a form and try to connect.

----------------------------------------------------------------------
jamhitz · 2013-02-07 04:27

Yes, a regular SQLdb-based desktop application works just fine
accessing the very same database with the very same credentials.

In fact CGI applications based of fpWeb and FreeSpider are accessing
the database ok.

----------------------------------------------------------------------
Silvio Clécio · 2013-02-07 10:40

I don't know what can be, because I could not reproduce the error.

Download this demo:

http://www.sendspace.com/file/wkajve

And follow the steps below:

1. # zypper install sqlite3-devel ;
2. Open and run the project "project1.lpi";
3. Call http://localhost in your browser;

This allow you to set a break point and debug your application,
running the it step by step with F8.

----------------------------------------------------------------------
jamhitz · 2013-02-07 15:03

Thank you. I can see some progress. When I compile the program and run
it off the terminal, my browser is able to get the JSON object fetched
from the database (I tested the DB using Database Desktop).

However, I cannot run the app from my IDE, since it is attempting to
bind to Port 80, and my Lazarus does not run as root. How do I change
the port on which the app binds so that I can debug the code by
stepping using a regular user account?

JamHitz

----------------------------------------------------------------------
Silvio Clécio · 2013-02-07 16:52

Hm... without error in the embedded server, we will not need to debug
with this technique.

Are you using apache? Port 80?

Execute the commands below:

Code: [Select]# zypper install postgresql-devel
# zypper install sqlite3-devel
It will install client libs of your database servers.

----------------------------------------------------------------------
jamhitz · 2013-02-08 09:34

Apache is installed, the *-devel packages are installed; and of course
when testing the project, Apache is off, and the Firewall is off. But,
because I am running this as a regular user, I cannot access port 80
(ports < 1024) when running from the Lazarus IDE. However, if I run
the compiled program as root, I can then access my application on port
80.

What I need to do is to change the port to something like 8080 so that
I can be able to debug the program.

I know I can set re-routing in the iptables but I was hoping to not
have to do this.

Thank you.

----------------------------------------------------------------------
Silvio Clécio · 2013-02-13 15:02

Hello,

I think that I found your problem.

See this picture:

http://imagebin.org/246565

So, use BrookSettings.Configuration instead BrookSettings.Charset.

----------------------------------------------------------------------
jamhitz · 2013-02-15 21:16

Hello Silvio

You are right. I think I trusted Code Assist a little too much to
counter-check.  It works ok now. I'll finally get the tutorial going.
Thank you.

----------------------------------------------------------------------
Silvio Clécio · 2013-02-18 01:14

Nice!