Firebird connectivity using TODBCConnectionProperties

sms · 2014-07-08 07:11:08

Hi AB,

I am using below code to connect to a firebird db. Following code compiles and server runs without any error.

aProps := TODBCConnectionProperties.Create('','DRIVER=Firebird/InterBase(r) driver;UID=SYSDBA;PWD=JOCKEY;DBNAME=C:\Synopse\Samples\Sinu\SINU.fdb;','SYSDBA','JOCKEY');
    aModel := DataModel;
    DataModel := GetDataModel;
    VirtualTableExternalRegisterAll(DataModel,aProps);
    aRestServer := TSQLRestServerDB.Create(DataModel,':memory:',false); // authentication=false
    aHttpServer := TSQLHttpServer.Create(SERVER_PORT,[aRestServer],'+',useHttpApiRegisteringURI);

When I try to add a record in one of the table , Client.Add returns 0. It doesn't add any record in table. But both server and client runs without any error. I try to debug and it fails inside EngineAdd in the below line

if URI(url,'POST',nil,@Head,@SentData).Lo<>HTML_CREATED then

When I debug , my url is root/Customer where Customer is my table name.

But URI(url,'POST',nil,@Head,@SentData).Lo is 501 which is <> HTML_CREATED and exits from the procedure. I can see Head is set to

'HTTP/1.1 400 Bad Request'#$D#$A'Date: Tue, 08 Jul 2014 07:06:25 GMT'#$D#$A'Content-Length: 49'#$D#$A'Content-Type: application/json; charset=UTF-8'#$D#$A'Server: Microsoft-HTTPAPI/2.0'#$D#$A'X-Powered-By: Synopse mORMot 1.18 http://synopse.info'#$D#$A'Server-InternalState: 3'#$D#$A'Accept-Encoding: synlz,deflate'#$D#$A#$D#$A

Am I doing something wrong here ? I took code from 28 - Simple RESTful ORM Server and slightly modified to access FB and my table. That's it.

Any help is much appreciated

Thanks
Sinu

ab · 2014-07-08 09:31:57

501 stands for HTML_NOTIMPLEMENTED, which is there if the client was not able to reach the server.
I suppose your client is not rightly defined.
How are you creating the Client instance?

Or you do not have the rights to perform the action.
Could you enable logs on the server side?
I suspect it would be easier to track what's happening (e.g. track SQL statements, and so on).

Or run the server in the debugger, and find out what happens in TSQLRestServer.URI() method.
In particular, step into this TSQLRestServerURIContext.ExecuteORMWrite method:

TableID := TableEngine.EngineAdd(TableIndex,Call.InBody);

sms · 2014-07-08 09:59:48

I use below statement to create client

    aClient := TSQLHttpClientWinHTTP.Create('localhost',SERVER_PORT,aModel);

Will do a debug and come back

sms · 2014-07-08 22:39:36

Hi ab, Thanks for directing me to check the log.

Here is the log from client

20140709 10303358  +    TSQLHttpClientWinHTTP(02A45720).TimeStamp
20140709 10303358  +    	TSQLHttpClientWinHTTP(02A45720).00694A61 
20140709 10303362 clnt  		TSQLHttpClientWinHTTP(02A45720) GET root/TimeStamp status=200 state=1
20140709 10303362  -    	00.061.398
20140709 10303362 ret   	135183427489
20140709 10303362  -    00.061.426
20140709 10303362  +    TSQLHttpClientWinHTTP(02A45720).00694A61 
20140709 10303362 clnt  	TSQLHttpClientWinHTTP(02A45720) POST root/Customer status=500 state=2
20140709 10303362  -    00.001.516
20140709 10303362 ERROR TSQLHttpClientWinHTTP(02A45720) POST root/Customer returned 500 Internal Server Error with message  {  "ErrorCode":500,  "ErrorText":"Exception EAccessViolation: Access violation at address 00616283 in module 'License.exe'. Read of address 00000008"  } stack trace API 0061DB2E 0066B310 0066B86E 00666867 0067992D 00695C87 005CBA03 005CB607 005CB5B8 005D6471 0069F8EE 7739919F 777CA8CB 777CA8A1

And log from server is below

20140709 10330501  +    TSQLRestServerDB(02BE5890).root/Customer
20140709 10330501 SQL   	TSQLRestServerDB(02BE5890) INSERT INTO Customer (FName,MName,SName,Address1,Address2,Suburb,City,State,Country,PostCode,PhoneNo,MobileNo,Fax,Email,WWW,SerialKey) VALUES (:('qeqwe'):,:('M wwww'):,:('S 333333'):,:(''):,:(''):,:(''):,:(''):,:(''):,:(''):,:(''):,:(''):,:(''):,:(''):,:(''):,:(''):,:(''):); prepared with 16 params
20140709 10330501 EXC   	ESQLite3Exception ("no such table: Customer") at 006B8E73  stack trace API 00626970 0040A2F0 
20140709 10330501 ERROR 	TSQLRestServerDB(02BE5890) {"ESQLite3Exception":"no such table: Customer"}{"ESQLite3Exception(02CD8EB8)":[20140709 10330501 EXCOS 	EAccessViolation (C0000005) at 00616283  stack trace API 00626970 0040A2F0 
 stack trace API 00627C5E 006BB19E 006BA454 00693CC6 006908CB 0068F16F 00692793 00733458 005E211D 005E7E65 004C976C 0040AC0E 7739919F 777CA8CB 777CA8A1 
20140709 10330501 srvr  	POST root/Customer ERROR=500 (Exception EAccessViolation: Access violation at address 00616283 in module 'License.exe'. Read of address 00000008)

Looks like it's trying to add the record in sqlite. I want to add it in my FB db. Do I need to setup SQLite even if I use FB for my purpose ?

Thanks
sinu

ab · 2014-07-09 05:25:45

You need to call CreateMissingTables just after server initialization.
Please check the doc and samples.

sms · 2014-07-09 05:47:42

Thanks Arnaub. It worked :-)

sms · 2014-07-10 05:01:32

Hi Arnaub,

I am considering few ways to create a mobile client that can work with mORMot server.

1. Delphi Firemonkey mobile app with indy component
2. Delphi Firemonkey mobile app with TRestClient component
3. Smart Mobile Studio to create HTML 5 app and then use Phone Gap to create mobile app.
4. Rad studio HTML Builder to build HTML 5 app and use Phone gap to create mobile app.

I tried the second option, but couldn't make it work. I got a sample app developed in SMS which I need to go thru in detail. Do you think SMS professional is enough for that ?

What's your opinion on 1st,2nd and 4th options ? I never see anybody talks about HTML Builder here

If I create a mORMot REST Server, will that work perfectly with XE6 and TRESTClient ? Or do I need to create a server with interface to work with that?

ab · 2014-07-10 08:35:15

Forget about TRESTClient, or manual Indy process.

You should better use the units available in the CrossPlatform sub folder of the repository.
See http://synopse.info/fossil/dir?ci=tip&n … ssPlatform

They are designed to work with Delphi FMX and Smart Mobile Studio (+PhoneGap).

It is still a work in progress, not fully tested yet (I do not have the license, nor interrest for FMX Mobile apps), so your feedback is welcome!

sms · 2014-07-10 12:03:44

Thank you Arnaud. I will try it in both clients.
I have downloaded the sample code for SMS and will try a simple client app in XE6 tomorrow.

I have seen different samples to create ORM server and interfaced servers. All working fine in XE6 . I have small app in my mind to learn all these technologies.
It's a database app, so I can create a ORM server . So client can call all CRUD functions. But in case if I need to call some general server functions, is that possible in
ORM server ? Or do I need to have 2 separate servers for respective features ?

Thanks,
Sinu

ab · 2014-07-10 13:47:16

The same mORMot server (i.e. a single TSQLRestServer instance) can serve as remote ORM and/or to publish SOA services.

sms · 2014-07-10 21:56:12

Hi Arnaud , I try to use SynCrossPlatformREST in one application and it throws lots of errors. First it threw it couldn't find TSHA256. I could find it in Crypto file. But it was declared inside implementation section. I moved out of implementation section which fixed that. Then I got another undeclared identifier crc32ascii . I couldn't find it anywhere in the folder. Am I using an old version or something ?

ab · 2014-07-11 07:01:26

@sms On which compiler?
Sounds like if your SynCrossPlatformCrypto unit is a deprecated one.

For Delphi, ensure the CrossPlatform folder is in the project or IDE path.

For SmartMobileStudio, ensure you copied the files into the shared "Librairies" folder.
Run CopySynCrossPlatformUnits.bat (as in "SQLite3\Samples\29 - SmartMobileStudio Client") as stated by the ReadMe.md file.

sms · 2014-07-14 21:35:10

Thanks Arnaud , I'll check that and come back later

sms · 2014-07-16 04:03:47

Hi Arnaud , I have downloaded 1.18 version of mormot. But I couldn't find 29 - smartmobilestudio client . I searched for CopySynCrossPlatformUnits.bat which is also missing. I have samples upto 28 - Simple RESTful ORM Server. Can I download the "29 - smartmobilestudio client" alone ?

Thanks

ab · 2014-07-16 07:56:25

Please download latest http://synopse.info/files/mORMotNightlyBuild.zip
There is all you need AFAIK.

Antonio · 2014-07-19 15:08:46

Hi,

This routine does not work with delphi xe5 + Android. I had to change.

begin
  if C=nil then
    result := '' else begin
    result := C.ClassName;
    if IdemPropName(copy(result,1,4),'TSQL') then
      if IdemPropName(copy(result,5,6),'Record') then
        delete(result,1,10) else
        delete(result,1,4) else
      if result[1]<>'T' then
        delete(result,1,1);
  end;
end;
---------------------- NEW ------------------
function GetDisplayNameFromClass(C: TClass): string;
begin
  if C=nil then
    result := '' else begin
    result := C.ClassName;
    if IdemPropName(copy(result,1,4),'TSQL') then
      if IdemPropName(copy(result,5,6),'Record') then
        delete(result,1,10) else
        delete(result,1,4) else
      if result[1]='T' then
        delete(result,1,1);
  end;
end;

ab · 2014-07-20 04:31:12

In fact, it would'nt work on any target, not even Android!

See http://synopse.info/fossil/info/a353b4c … 7b99c04ae6

Thanks for the feedback!

sms · 2014-07-21 09:02:32

Thank you Antonio and Arnaud. I was getting bad request error when I try from xe6 firemonkey mobile app. The above fix fixed that issue.

Thanks again

ab · 2014-07-21 15:04:51

we do not use fmx on our side.

that's why your feedback is so important for us.

sms · 2014-07-26 01:55:56

Hi Arnaud,

I have created a server app using below code which connects to an external FB database.
Bth client and server are developed in XE6 as FMX apps. Server runs perfectly. If the client runs in Windows platform, it runs perfectly. But if I run it in IOS simulator , it doesn't communicate to server. I can't find anything in log file. If I debug it goes TIndyHttpConnectionClass.URI in SynCrossPlatformSpecific.pas and calls POST method . But after that it goes to assembly code. Finally I am getting socket time out error. I have another app which uses direct indy client component to communicate to a delphi server app and it works fine. So indy should work fine in fmx.

I can give teamviewer access if you like to have a look.

Server code

sConnectionStr := 'DRIVER=Firebird/InterBase(r) driver;UID='+ Settings.DBUser + ';PWD=' + Settings.DBPassword;
sConnectionStr := sConnectionStr + ';DBNAME=' + Settings.DBAlias ;
aProps := TODBCConnectionProperties.Create('',sConnectionStr,Settings.DBUser,Settings.DBPassword);
DataModel := GetDataModel;
VirtualTableExternalRegisterAll(DataModel,aProps);
aRestServerDB := TSQLRestServerDB.Create(DataModel,':memory:',false);
aRestServerDB.CreateMissingTables(0);
aHttpServer := TSQLHttpServer.Create(SERVER_PORT,[aRestServerDB],'+',useHttpApiRegisteringURI);
aHttpServer.AccessControlAllowOrigin := '*';

Client code

aModel := GetDataModel;
aClient := TSQLRestClientHTTP.Create('**.***.**.*,StrToInt(SERVER_PORT),aModel);
memo1.Lines.Add('Add a new TPerson');
aPerson := TSchooldata.Create;
try
Randomize;
aPerson.ID := 2;
aPerson.Name := 'qeqwe'+ FormatdateTime('ddmmyyyyhhnnss',now);
aPerson.Address1 := 'S 333333';
aID := aClient.Add(aPerson,true);
finally
aPerson.Free;
end;

ab · 2014-07-26 08:16:39

Are you sure you do not have a firewall configuration issue?

Can you access the server from the browser?

sms · 2014-07-26 10:23:27

I have a mac machine with windows parallels (windows 8) installed. I use windows parallels for the development. My server runs inside parallels. I can access the server from the browser in the windows parallels. But If I run it from the mac browser, I am not able to access it. I am new to mac machine. Might need to check the firewall in mac ?

ab · 2014-07-26 11:13:09

I suspect this is the firewall on the PC/server side which should be set.

sms · 2014-07-27 05:21:47

Yes arnaud . It's working when i turn off firewall . Thanks. I am trying Intel's XDK also now so that I can get rid of FMX

ab · 2014-07-27 07:26:50

On our side, we prefer and support Smart Mobile Studio.

sms · 2014-07-27 10:30:43

I have downloaded Smart Mobile Studio which gives lots of access violations in the ide. So I left that. Might have a look again .
I am trying out different options for mobile development.

ab · 2014-07-27 11:13:03

The upcoming 2.1 beta 3 is stable enough not work with.
We did not have any blocking issue when developing and debugging our cross platform units.
You have a working set of units to create RESTful clients, including enhanced features like strong authentication and BATCH process.
So you won't need to reinvent the wheel!

big.rid · 2014-08-12 12:48:51

Does anyone have an example of connecting to firebird using SynDBFirebird?

ab · 2014-08-12 14:30:06

SynDbFirebird is not finished - so won't work and is not to be used.

Use SynDbZeos or SynDBODBC instead.
Those two work pretty well.

Insertion speed
ODBC Firebird	   1044	    15707	11283	15034
ZEOS Firebird	   10595	10523	22629	25195
FireDAC Firebird     19864	 49319	19783	49746

Read speed

ODBC Firebird	   1999	 55310	80732
ZEOS Firebird	   21410  73815	109318
FireDAC Firebird   2222	 52675	67108

big.rid · 2014-08-14 12:46:51

procedure TForm5.BitBtn2Click(Sender: TObject);
var
  pr : TSQLDBZEOSConnectionProperties;
  cn : TSQLDBZEOSConnection;
begin
  pr := TSQLDBZEOSConnectionProperties.Create(
  TSQLDBZEOSConnectionProperties.URI(
  dFirebird,
  'localhost/3055',
  'c:\Firebird_2_5\bin\fbclient.dll',
  false), '3camadas', 'sysdba', 'masterkey');
  cn := TSQLDBZEOSConnection.Create(pr);
  cn.Connect; //Error[1]
end;

[1] EZSQLException with message 'SQL Error: connection rejected by remote interface. Error code: -923. Connection not established'.

ab · 2014-08-14 13:09:53

As stated by the documentation:

the optional server name can contain a port number, specified after ':'

So 'localhost:3055' instead of 'localhost/3055' could make sense IMHO.

big.rid · 2014-08-14 13:12:17

Worked, thanks.

big.rid · 2014-08-14 21:26:25

  aProps := TSQLDBZEOSConnectionProperties.Create(
            TSQLDBZEOSConnectionProperties.URI(
            dFirebird,
            'localhost:3055',
            'C:\fbclient.dll',
            False), '3camadas', 'SYSDBA', 'masterkey');
  aModel_Usuarios      := TSQLModel.Create([TUsuarios], 'usuarios');
  aRestServer_Usuarios := TSQLRestServer.Create(aModel_Usuarios, False);
  aHttpServer          := TSQLHttpServer.Create('8080',[aRestServer_Usuarios], '+', useHttpApiRegisteringURI);
  VirtualTableExternalRegisterAll(aModel_Usuarios, aProps);

is correct ?

http://localhost:8080/usuarios -> in chrome

{
"ErrorCode":400,
"ErrorText":"Bad Request"
}

ab · 2014-08-15 07:29:23

http://localhost:8080/usuarios/usuarios

Please ensure you did at least make a quick search in the first part of the SAD 1.18 pdf document, using the table of content and the keyword index.
The RESTful URI scheme is explained there.

mORMot Open Source

#1 2014-07-08 07:11:08

Firebird connectivity using TODBCConnectionProperties

#2 2014-07-08 09:31:57

Re: Firebird connectivity using TODBCConnectionProperties

#3 2014-07-08 09:59:48

Re: Firebird connectivity using TODBCConnectionProperties

#4 2014-07-08 22:39:36

Re: Firebird connectivity using TODBCConnectionProperties

#5 2014-07-09 05:25:45

Re: Firebird connectivity using TODBCConnectionProperties

#6 2014-07-09 05:47:42

Re: Firebird connectivity using TODBCConnectionProperties

#7 2014-07-10 05:01:32

Re: Firebird connectivity using TODBCConnectionProperties

#8 2014-07-10 08:35:15

Re: Firebird connectivity using TODBCConnectionProperties

#9 2014-07-10 12:03:44

Re: Firebird connectivity using TODBCConnectionProperties

#10 2014-07-10 13:47:16

Re: Firebird connectivity using TODBCConnectionProperties

#11 2014-07-10 21:56:12

Re: Firebird connectivity using TODBCConnectionProperties

#12 2014-07-11 07:01:26

Re: Firebird connectivity using TODBCConnectionProperties

#13 2014-07-14 21:35:10

Re: Firebird connectivity using TODBCConnectionProperties

#14 2014-07-16 04:03:47

Re: Firebird connectivity using TODBCConnectionProperties

#15 2014-07-16 07:56:25

Re: Firebird connectivity using TODBCConnectionProperties

#16 2014-07-19 15:08:46

Re: Firebird connectivity using TODBCConnectionProperties

#17 2014-07-20 04:31:12

Re: Firebird connectivity using TODBCConnectionProperties

#18 2014-07-21 09:02:32

Re: Firebird connectivity using TODBCConnectionProperties

#19 2014-07-21 15:04:51

Re: Firebird connectivity using TODBCConnectionProperties

#20 2014-07-26 01:55:56

Re: Firebird connectivity using TODBCConnectionProperties

#21 2014-07-26 08:16:39

Re: Firebird connectivity using TODBCConnectionProperties

#22 2014-07-26 10:23:27

Re: Firebird connectivity using TODBCConnectionProperties

#23 2014-07-26 11:13:09

Re: Firebird connectivity using TODBCConnectionProperties

#24 2014-07-27 05:21:47

Re: Firebird connectivity using TODBCConnectionProperties

#25 2014-07-27 07:26:50

Re: Firebird connectivity using TODBCConnectionProperties

#26 2014-07-27 10:30:43

Re: Firebird connectivity using TODBCConnectionProperties

#27 2014-07-27 11:13:03

Re: Firebird connectivity using TODBCConnectionProperties

#28 2014-08-12 12:48:51

Re: Firebird connectivity using TODBCConnectionProperties

#29 2014-08-12 14:30:06

Re: Firebird connectivity using TODBCConnectionProperties

#30 2014-08-14 12:46:51

Re: Firebird connectivity using TODBCConnectionProperties

#31 2014-08-14 13:09:53

Re: Firebird connectivity using TODBCConnectionProperties

#32 2014-08-14 13:12:17

Re: Firebird connectivity using TODBCConnectionProperties

#33 2014-08-14 21:26:25

Re: Firebird connectivity using TODBCConnectionProperties

#34 2014-08-15 07:29:23

Re: Firebird connectivity using TODBCConnectionProperties

Board footer