Jump to content
dymatrix

Connecting to database

Recommended Posts

Hello,

 

I have SMS Pro, currently just experimenting with it and I just started going through a Node.js course to learn more about it.  I know that I need to use Rest or Node.js to connect to my Postgres  database, but not sure about the next step.   Is there an example/tutorial on some basics?  

 

cheers!

 

- Lou

Share this post


Link to post
Share on other sites

Hi, welcome to SMS!  Did you look in the     Smart Mobile Projects\Demos\Featured Demos\Business\   folder   ?

 

 

Hello,

 

I have SMS Pro, currently just experimenting with it and I just started going through a Node.js course to learn more about it.  I know that I need to use Rest or Node.js to connect to my Postgres  database, but not sure about the next step.   Is there an example/tutorial on some basics?  

 

cheers!

 

- Lou

Share this post


Link to post
Share on other sites

To connect to an external database you need a server-side component

Either a server-script which you can access through ajax/httprequest or through node.js

 

The code below connects to a MySql database through a universal php server script

Just supply your credentials to connect to your db

 

The returning result is handled two-ways in this demo : stored in a variant for easy access and also stored in a TDataset

In real world applications you wouldn't do both of course

 

I suppose Postgress handles requests very similar to MySql

unit Form1;
 
interface
 
uses
  SmartCL.System, SmartCL.Components, SmartCL.Forms, SmartCL.Application,
  SmartCL.Inet, MySQLDB, SmartCL.Controls.Memo;
 
type
  TForm1 = class(TW3Form)
  private
    {$I 'Form1:intf'}
  protected
    procedure InitializeForm; override;
    procedure InitializeObject; override;
    procedure ObjectReady; override;
    procedure Resize; override;
  end;
 
implementation
 
{ TForm1 }
 
procedure TForm1.InitializeForm;
begin
  inherited;
  // this is a good place to initialize components
 
end;
 
procedure TForm1.InitializeObject;
begin
  inherited;
  {$I 'Form1:impl'}
//
end;
 
procedure TForm1.ObjectReady;
begin
  inherited;
 
  if (csReady in ComponentState) then begin
    MySQLDB1.ADomain   := '......';
    MySQLDB1.ADatabase := '......';
    MySQLDB1.AUser     := '.....';
    MySQLDB1.APassword := '.....';
    MySQLDB1.ASelect   := 'SELECT author from news';
 
    MySQLDB1.OnMyDataReady := procedure(sender: TW3HttpRequest)
      begin
        MySQLDB1.Prepare;
        Memo1.Text := '';
        For var i := 0 to MySQLDB1.MaxRows - 1 do begin
          Memo1.Text := Memo1.Text + MySQLDB1.cursor.rows[i].author + #10;
        end;
      end;
 
    MySQLDB1.SQLSelect;
  end;
 
end;
 
procedure TForm1.Resize;
begin
  inherited;
//
end;
 
initialization
  Forms.RegisterForm({$I %FILE%}, TForm1);
end.

and the support unit 

Unit MySQLDB;
 
interface
 
uses
  SmartCL.System, SmartCL.Components, SmartCL.Inet, SmartCL.Controls.Image,
  SmartCL.Spinner, System.DataSet, System.Types, ECMA.JSON;
 
type
 
  TMySQLDB = class(TW3CustomControl)   //can't build non-visual packages
  private
    FHttp : TW3HttpRequest;
 
    FDomain    : String;
    FDatabase  : String;
    FUser      : String;
    FPassword  : String;
    FSelect    : String;
 
    Fcursor    : variant;
    FDBRows    : Integer;
  protected
    procedure InitializeObject; override;
    procedure FinalizeObject; override;
  public
    OnMyDataReady: THttpRequestEvent;
    property ADomain: String   read FDomain   write FDomain;
    property ADatabase: String read FDatabase write FDatabase;
    property AUser: String     read FUser     write FUser;
    property APassword: String read FPassword write FPassword;
    property ASelect: String   read FSelect   write FSelect;
    property cursor: Variant   read Fcursor   write Fcursor;
    property MaxRows: Integer  read FDBRows   write FDBRows;
 
    property OnDataReady: THttpRequestEvent read OnMyDataReady write OnMyDataReady;
    procedure SQLSelect;
    procedure Prepare;
    DataSet : TW3Dataset;
  end;
 
 
implementation
 
procedure TMySQLDB.InitializeObject;
begin
  inherited;
  FDBRows := 0;
 
  DataSet := TW3Dataset.Create;
 
end;
 
procedure TMySQLDB.SQLSelect;
var
  sql_statement : String;
  encodeMySQLDBtr1,encodeMySQLDBtr2,encodeMySQLDBtr3,encodeMySQLDBtr4,encodeMySQLDBtr5 : String;
begin
  FullScreenSpinner.Enter;
 
  FHttp := TW3HttpRequest.Create;
  FHttp.OnDataReady := OnMyDataReady;
 
// generic cross-domain mysql query handler
// works with any mysql database on any domain
 
  FHttp.open("POST","http://www.lynkfs.com/dbsmsmysql.php");
 
  FHttp.setRequestHeader("Content-type","application/x-www-form-urlencoded");
 
  asm @encodeMySQLDBtr1 = encodeURIComponent(@FSelect); end;
  asm @encodeMySQLDBtr2 = encodeURIComponent(@FDomain); end;
  asm @encodeMySQLDBtr3 = encodeURIComponent(@FUser); end;
  asm @encodeMySQLDBtr4 = encodeURIComponent(@FPassword); end;
  asm @encodeMySQLDBtr5 = encodeURIComponent(@FDatabase); end;
 
  sql_statement := 'sql_statement=' + encodeMySQLDBtr1 +
                   '&sms_domain='   + encodeMySQLDBtr2 +
                   '&sms_user='     + encodeMySQLDBtr3 +
                   '&sms_password=' + encodeMySQLDBtr4 +
                   '&sms_database=' + encodeMySQLDBtr5;
 
  FHttp.send(sql_statement);
end;
 
procedure TMySQLDB.Prepare;
var
  SA1,SA2 : Array of String;
  v: variant;
begin
  FullScreenSpinner.Leave;
 
// Method 1 : store data in variant (cursor)
  cursor := JSON.parse(FHttp.ResponseText);
  MaxRows := cursor.rows.length;
 
// Method 2 : store data in TDataSet
  v := TVariant.CreateArray;
  v := cursor.rows;
 
  If MaxRows > 0 then begin
    asm
      var k = 0;
      for(var propertyName in @v[0]) {
        @SA1[k] = propertyName;
        k = k + 1;
      }
    end;
    For var j := 0 to SA1.Length -1 do begin
      Dataset.FieldDefs.Add(SA1[j],ftString);
    end;
 
    DataSet.CreateDataset;
 
    For var i := 0 to MaxRows - 1 do begin
      SA1.Clear;
      SA2.Clear;
 
      Dataset.Append;
      asm
        var k = 0;
        for(var propertyName in @v[@i]) {
          @SA1[k] = propertyName;
          @SA2[k] = @v[@i][propertyName];
          k = k + 1;
        }
      end;
      For var j := 0 to SA1.Length -1 do begin
        Dataset.Fields.FieldByName(SA1[j]).AsString := SA2[j];
      end;
      DataSet.Post;
    end;
  end else begin
    ShowMessage('Zero rows');
  end;
 
end;
 
procedure TMySQLDB.FinalizeObject;
begin
  FHttp.Free;
  Fcursor := nil;
  inherited;
end;
 
end.

In real-world applications don't supply your db credentials from the client, but embed them server side.

 

 

 

For node.js there is this post

http://forums.smartmobilestudio.com/index.php?/topic/4258-node-mysql-and-socketio/?hl=node.js

which works ok, although I think I should revisit and upgrade the code

 

Hope this helps

.

Share this post


Link to post
Share on other sites

Nico,

 

Where can I find  dbsmsmysql.php  you reference in your example code? I did a google search and nothing comes up.  

 

Cheers!

 

- Lou

 

p.s. I made donation on your page for SMS HTML components, just waiting for downoad link to arrive :)

Share this post


Link to post
Share on other sites

Hi Dymatrix

 

the php code is along these lines (keeping all credentials server-side :)

 

 

<?php
header("Access-Control-Allow-Origin: *");
$link = mysql_pconnect("domain", "user", "password") or die("Could not connect");
mysql_select_db("database") or die("Could not select database");
 
$sql_statement = $_POST['sql_statement']; 
 
$arr = array();
 
$rs = mysql_query($sql_statement);
 
while($obj = mysql_fetch_object($rs)) {
$arr[] = $obj;
}
echo '{"smsrows":'.json_encode($arr).'}';
 
mysql_close($link);
?>

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×