Jump to content


Photo

Connecting to database


  • Please log in to reply
5 replies to this topic

#1 dymatrix

dymatrix
  • Members
  • 6 posts

Posted 01 April 2017 - 06:09 AM

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


  • pagwag likes this

#2 ielite

ielite
  • Members
  • 674 posts

Posted 01 April 2017 - 04:16 PM

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



#3 Nico Wouterse

Nico Wouterse
  • Moderators
  • 212 posts
  • LocationAustralia

Posted 02 April 2017 - 02:36 AM

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.smartm...tio/?hl=node.js

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

 

Hope this helps

.


  • ielite and dymatrix like this
Nico Wouterse

#4 dymatrix

dymatrix
  • Members
  • 6 posts

Posted 04 April 2017 - 07:32 AM

Nico,

 

Thanks for sharing your example, I will check it out and see how far I get.   This is along the lines of what I was hoping to find.



#5 dymatrix

dymatrix
  • Members
  • 6 posts

Posted 05 April 2017 - 09:28 PM

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 :)



#6 Nico Wouterse

Nico Wouterse
  • Moderators
  • 212 posts
  • LocationAustralia

Posted 06 April 2017 - 12:30 AM

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);
?>

  • ielite likes this
Nico Wouterse




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

IPB Skin By Virteq