#1 Re: mORMot Framework » interface with mysql via php » 2013-12-13 00:25:56

Thanks for that.
Will have a look when I get home.
Tom

#2 Re: mORMot Framework » interface with mysql via php » 2013-12-12 19:37:30

The PHP script is here.

<?

$databasehost = "localhost";
$databasename = "xxxx";
$databaseusername ="xxxx";
$databasepassword = "xxxx";

$con = mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
mysql_select_db($databasename) or die(mysql_error());
mysql_query("SET CHARACTER SET utf8");
$query = file_get_contents("php://input");
$sth = mysql_query($query);

if (mysql_errno()) {
    header("HTTP/1.1 500 Internal Server Error");
    echo $query.'\n';
    echo mysql_error();
}
else
{
    $rows = array();
    while($r = mysql_fetch_assoc($sth)) {
        $rows[] = $r;
    }
    print json_encode($rows);
}
?>

By calling the server and then using your demo I can get the information.
I.e. select id,address from houses.
So this bit works well and I can get this data into my sqlite database.
The sending the data back to the server is the hard part.

Tom

#3 Re: mORMot Framework » interface with mysql via php » 2013-12-12 11:53:54

I do not want to use zeos to connect to MySQL directly since my server limits who has access to the databases.
The http idea is so much easier for clients.
The MySQL is a link to my sqlite database on the PC and the sqlite databases on the android devices.
I will have an update table so any device can check if they have the latest data for any table.

Tom

#4 mORMot Framework » interface with mysql via php » 2013-12-12 11:39:30

Tom Duncan
Replies: 5

Hi,
Last year I was developing software to interface with an Android device with basic4android.
Well this part is going wellWhat I have done and doing is use sqlite on the Android device and Update the files via a mysql database on my server.
This I need because the user will not be in internet availablity some of the time.

I want to use this same framework on my pc side of things as well.
Sofar with your software I can do a sql select statement and get the results onto a dbgrid using a modded version of your demo 17 (I think)
My question is how can I sent inserts onto the sql database.
I have no idea how to setup an insert. I assume the data needs to be entered into a json string then the insert event called.
Have you any demos on this or is it easier to interface with zeos and your components to do the inserting.

Hope this makes sense.

Tom Duncan

#5 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2013-07-14 09:45:23

So...
Is it worth me continuing with this idea.
I do need to be able to send and receive data with SQL.

Tom

#6 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2013-07-14 07:53:12

Will have a look later.
Pity our timezone is so far apart. Just getting ready for dinner in Australia now.
Would this make it difficult to send update information from my android application.
I think it uses an SQL update etc function.

Tom

#7 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2013-07-14 07:26:29

Wonderful Thanks.
Now for my last silly question.

With Say Project 16 how would I modify a value then send it back to the test3.db3?

Tom

#8 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2013-07-14 03:13:23

1. What values do you use with sqlite as the database in Sample 16.

2. Also had a look at Sample17 and cannot find people.json

    Had a look on your web site in the files area as well.

Tom

#9 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2013-07-13 22:55:53

Had a look at the code for converting the old Volga database to a Sqlit3 one.
Used Sql Create table etc.
If the Field was a memo then say Text.
Then in filling the table used the Variant for each field.

Tom

#10 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2013-07-13 22:38:54

Was using that one.
Put in user and password as admin/admin

#11 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2013-07-13 22:09:28

Just testing the Sample 16.
Have Sqlite3 as the engine.
Database as test.db3 (which is in the same16 folder)
server in localhost/root:888 (also tested localhost:888)
user and password blank

Then after Open error in server an error in the Database handle.
Where do I need to have the sqlite database.

Tom

#12 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2013-07-13 20:54:00

Just did a Google on live bindings.
Sounds like a new Delphi thing.
I am still using Delphi 7

Tom

#13 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2013-07-13 20:06:43

But does that mean that I do not use dbcontrols for text input and dbgrid output.
Do I then use edit boxes and string grid.
Now that would be a change.

Tom

#14 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2013-07-13 11:32:31

Will send you a snippet of the code in the morning. Sending this on my tablet.
And will have a look at all the samples again.
It is strange not using Tdatabase  vcl's etc.
When I get this up and going correctly can I still use the data aware components.
With my current system I use a memory dataset then save the record to the database.

Tom

#15 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2013-07-13 00:31:08

The database was created in Delphi using a Sqlite vcl.
Then a batch copy from the Volga database using Variant.
my Sqlite browser picks the Text fields up fine.

As a thought have you any real-world examples using a database and using Json to send and receive data from a Server Application?
i.e. Have a Grid of data then able to add/delete/modify data and then display the new data.

Tom

#16 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2013-07-11 20:15:39

I was using one of my sqlite databases.
The fields are marked as text.
But when viewed in syn explorer the field is blank.
Also did a save of that table as CSV and got blank fields.
Will do some digging on batch.
Are their any demos which show this being done.
I imagined out tell the server that a json file is on the way etc.

Tom

#17 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2013-07-11 13:27:56

Just one more question.
How do I save data back to the sqlite.
Is this done via Jason again.
Sorry to be so basic about this.
Tom

#18 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2013-07-11 13:02:51

Cannot find it either.
Will have another look in the morning.

Just one thing. The memo fields that I have saved as a Text field in SQlite don't show up in SynExplorer program.
Is this normal?
I could send a demo version to you if you like.

Tom

#19 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2013-07-11 12:13:05

Will have a look.
Volga is a simple and quick database that I purchased 10 years ago.
Now open source and still good for every-day work.
Would like to create a server using the databases since they are used by all the clients.
Then have the tablet up and going with Android.

Tom

#20 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2013-07-11 11:28:50

Just made an inbound rule for 888.
Works like a charm thanks

Now will have a look at Datasets.

Tom

#21 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2013-07-11 08:32:57

Have the latest version.
Did the same setup and had the same problem
Have had a look at the firewall. and the port has been selected under TCP.
Will have another look later.
Also can I use another Dataset i.e. VolgaDB
It has the normal Dataset and SQL

Tom

#22 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2013-07-11 05:30:41

Well sorry for the huge delay.
I have been busy on other projects.
Tested your new sample 13 and as local host it works fine.
But I then set the   Http := OpenHttp('192.168.1.139','888');
Which is my machine address.
On this machine all is fine, BUT on a remote machine it does not work.
I assume this is a firewall issue, so I gave outgoing rules for 888, still nogo.
Also ran the TestSQL3Register.dpr - still no go.
Ran both as Administrator.

Running out of ideas.
And no for the connection to Android as well.

Any thoughts.

Tom

#23 mORMot Framework » Is it possible to use the Json part of mORMot with an external Query » 2012-03-16 00:23:08

Tom Duncan
Replies: 1

Hi All,
I am using a Tquery and I need to use Json to send/receive data.
Can the Json part be used for this query.
Tom

#24 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2012-02-16 08:54:49

These fields were created as Text fields, and show up in the database such.
I can put a copy of part of the database on my webpage if you like.
As I said it looks fine using a Sqlite Database manager.

Tom

Btw this same database displays all information correctly on a sqlite based application. (Not client/server)

#25 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2012-02-16 07:01:27

Just before I throw my computer over a bridge,
I have changed my mind just this once.
Have it sort up and going.
BTW your demo Client/server give the same displayed results.
Only problem is Text fields. VarChar fields are fine.
Here is the Json Dump using memo1.Lines.Add(UTF8ToString(fJSONBuffer));

[{"auto":1,"AutoNbr":"JAF12gjX","Pcode":1,"Pract":1,"Bill_To":22,"BillTo":"JAwmlWAL","BillName":"IERlcHQgb2YgVmV0ZXJhbnMgYWZmZmFpcnMg","Sex":"True","Dob":"1934-05-20","Title":"Mr","Name1":"QWxsYW4=","Name2":"","Sname":"Middleton","DspName":"Middleton, Mr Allan","Address":"MyBPcmlnaW4gQ2xvc2UNCkJyaXNiYW5lDQpRTEQgLCA0MDAxDQo=","Add1":"MyBPcmlnaW4gQ2xvc2U=","Add2":"QnJpc2JhbmU=","Add3":"UUxE","PostCode":"4001","Comment":"c2Rm","Hphone":"07 12345678","Wphone":null,"Fax":null,"Mphone":null,"Email":"tom@ctd.com.au","Mcare":null,"Dva":null,"Fund":null,"Abill_To":"False","Remind":"True","RemWhen":"2010-04-19","Notes":"PFRhYj4yOC8wMy8yMDA5Lg0KR1ROQ0QgLCBDYWxsb3VzIHJlbW92ZWQgZnJvbSBQTUEgIGFuZCBQQ0EgDQpoYWxsIG5haWxzIGNsZWFyZWQgDQo=","Doctor":null,"Ref_Doctor":"","Ref_Prov":"","Ref_Date":null,"History":"","RemSent":null,"SetArchive":null,"ShowBusy":null,"modded":null,"modded_when":null}]

and then using this to get fields...  UTF8ToString(Table1.FieldValue(Table1.GetString(0,i) ,1))

I get this for the Address field.

MyBPcmlnaW4gQ2xvc2UNCkJyaXNiYW5lDQpRTEQgLCA0MDAxDQo=

It is this..
3 Origin Close
Brisbane
QLD , 4001

I assume this is in UTF8 type format.
How do I get back to normal Ascii?


Also opening the same Sqlite database in DBexplorer I get the same results for any text fields.
However is Sqlite Database Browser v2 all is fine.


Tom

#26 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2012-02-16 03:41:19

thank you for that help.
If I don't figure it out this afternoon then I will try another company.
You wont hear from me again.
Tom

#27 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2012-02-15 20:29:13

and now for the silly newbie question. How do I pass the json data into a table.

Tom

#28 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2012-02-15 14:05:17

As a thought. Has anyone developed a data set vcl component from you software?
If that were the case then I could pass the json data jinto the data set then use db aware controls
to input data.
That would save heaps of work.

Oh well, wishful thinking.

Tom

#29 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2012-02-15 12:53:37

Could you give me a clue.  I assume I need to create a database then pass the json data.
Is that correct.

#30 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2012-02-15 11:09:06

will do a check on the Android and see what the results are from my xaamp server.
Also would I be correct in thinking that I can pass the json file and then get the results in an array.
I am just reading the pdf now.
Have made a new version which shows result in a memo, so will be easy to get the results, say into a grid to shaow all patients.
Then get the info for that patient after that.

Tom

#31 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2012-02-15 09:46:11

Hey it works, thank you for that.
Now all I need to figure out (which I will. lol) is the Json part.
I assume this uses the Get with the json to get all the fields.
Thanks so much for that.

Tom

#32 mORMot Framework » General application design » 2012-02-15 05:30:28

Tom Duncan
Replies: 5

Hi All,
I am going to use mORMot for my design of a Patient database.
It is a new version of one that I have designed.
With my progran now I use a database and db aware components etc.

What is the general way of using mORMot?
My thoughts are still use the database structure and create say a single record.
Then after that record is posted then send an Update to the server.
Patients names in a grid would come via a select Name from Payient etc.

Is this the easiest way of doing things.
Sorry but I am new to non database aware routines.

Tom

#33 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2012-02-14 23:39:30

Did some more tests this morning on the Android.
http://127.0.0.1:888/root
http://127.0.0.1:8080/root
http://127.0.0.1:3506/root
http://localhost:888/root
Also changed the server port number to match.
Still no go.
Is their any chance please of getting a Delphi client going with the new Json server.
Then at least I can get the PC side of the software going.
This is just so frustrating for me. (and I bet for you)
I really am experienced in programming but not using your very good code.
Sorry to be a pain in the a... still.

Also the huge Timezone difference makes things very hard as well.
I am in Sunny Australia.

Tom

#34 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2012-02-14 14:02:09

Was ok with my local server and the php file.
I also did a test with 8080 as well.
Can I test it out on any client demos at all. (Pc based)
tom

#35 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2012-02-14 10:18:35

Tested that but got an error. Connection refused, bugger.
Should one of the demo clients be able to be used with the server.

Tom

#36 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2012-02-13 23:29:42

also can I use one of the clients to test this.
Will try the http client as an idea.

#37 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2012-02-13 23:28:21

I feel like a right royal dummy now.
Thanks again for your help but still having problems.
Tested your server by pointing to my demo database.
Then via Android tried to connect. (connection refused)
Then thought I would try a web browser http://localhost:888/root select * from Patient
"the request source not found"
What am I doing wrong.
Sorry to be such a pain.

Tom

#38 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2012-02-13 02:28:06

Have had a good look at the Http Server.
How can I hook another database to it, not the default one you create.
I assume this uses the Props to get the Database structure.
Once connected do I then do a sql call requesting info from the server, much like the DBexplorer demo.
I will need to fill a grid with clients names. (something line select FirstName, LastName from Patient)
Then use the returned Json to fill the grid.
Then select a field from the grid to display all the details for that patient.
I assume you use edit boxes like your demo,
Then on posting any changes fill out the json string back to the server.
Is this idea OK (ish)
I will keep searching today (your sleep time) and see what I can come up with.

Tom

#39 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2012-02-12 08:47:42

at the moment all server uses will be on my local server. Will have a look tonight. See if I can get the basics to work.
Feel like a dummy at the moment. Most of my delphi and database stuff has been with vcl's. But this I am sure is the better way for me to go in the long run.
Just have to get my old head around it.
Tom

#40 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2012-02-12 00:58:21

I cannot see how I can in server mode.
Attach an existing Sqlite database.
Then have it send out the json response from the query.
then on client, see how to send request via an sql call.
The display of the result i can figure out.
Then work out what other clients are modifying the database, stop conflicts etc.

Tom

#41 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2012-02-11 12:01:04

I am still finding it hard to get my head around the code.
I might have to wait till someone comes up with demo code that I can see what is going on.
Have checked the DBExplorer area., but I still a way for the client to send the SQL code to the server.

Will keep reading and see if I can see what is happening.

Tom

#42 Re: mORMot Framework » addition to MainDemo » 2012-02-09 08:43:30

Thanks will wait with fingers crossed.
Would be nice to see a real world example, maybe some way of updating the of clients when a change etc has been made.
(just me seeing how to do it)
When I have may patient side of my program up and running I can post it for others to check it out as well.

Tom

#43 mORMot Framework » addition to MainDemo » 2012-02-09 08:26:42

Tom Duncan
Replies: 2

Hi all,
As a newbee to mORMot I had a thought.
I have read many post re client/server applications.
How about a new version of the main demo with a server so that other machines can use it.
This would show me and others I hope how to build and use the server and see how the client interacts with it.
Just a thought.

Tom

#44 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2012-02-08 20:29:17

Hi,
with a SQlite3 server and Json i/o could I replicate what my Mysql server gives me with my basic4Android program.
The php script on the server is..

<?

$databasehost = "localhost";
$databasename = "****";
$databaseusername ="****";
$databasepassword = "*****";

$con = mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
mysql_select_db($databasename) or die(mysql_error());
$query = file_get_contents("php://input");
$sth = mysql_query($query);

if (mysql_errno()) {
    header("HTTP/1.1 500 Internal Server Error");
    echo $query.'\n';
    echo mysql_error();
}
else
{
    $rows = array();
    while($r = mysql_fetch_assoc($sth)) {
        $rows[] = $r;
    }
    print json_encode($rows);
}
?>

and on the Android I call like this..

Sub ExecuteRemoteQuery(Query As String, TaskId As Int)
    Dim req As HttpRequest
    req.InitializePost2("http://192.168.1.5/rsl/reget.php", Query.GetBytes("UTF8"))
    hc.Execute(req, TaskId)
End Sub

    ExecuteRemoteQuery("SELECT person FROM countries WHERE id='" & tl.First & "'", COUNTRY_POPULATION)


Is this possible with Json using your software

Then on the response from Json..

use the Get(i) function to populate my list for people etc
Also using the same idea to get/send the edit boxes for any changes?

Sorry if I am using Android routines but this because part of the project is to inerface with the Android, the main part is a PC program.

Tom

#45 Re: mORMot Framework » Use Sqlite in a client/server concurrent mode » 2012-02-08 07:20:51

Thanks so much for that.
Will have a look now.
See if I can get my head around json.
The Android demo uses a MySQL call to a php file which returns the database from the query.
Will stay tuned on what you come up with and also what I can do.

Tom

#46 Re: mORMot Framework » JSON RESTful Services, How to implement server method with obj param » 2012-02-08 00:57:33

Also.
I am also writing this application for the android as well.
It uses json to get info from my server.
Could this also act as a json server for the android part of the program?

Tom

#47 Re: mORMot Framework » JSON RESTful Services, How to implement server method with obj param » 2012-02-08 00:52:53

Any ideas whena demo using json will be available.
I need to impliment a client/server application with sqlite.
would json act as a passthrough from the application to the server.

Tom

#48 Re: Source Code repository » SQLite3 MainDemo source code uploaded to the repository » 2012-02-07 23:38:08

Is it possible to use this idea in a multi-machine idea.
i.e. to have a commeon database for any user of synFile.
if so can any machine be notified of changes to the memo's sent etc.

Tom

#49 mORMot Framework » Use Sqlite in a client/server concurrent mode » 2012-02-07 22:31:24

Tom Duncan
Replies: 62

Hi All.
This is what I need to do with my current application.

1. Connect multiple machines to the same sqlite3 database
2. Just using a mapped network drive
3. Have an easy way to update the other machines when an update is done.
4. be nice and quick. 10 tables with up to 10,000 records on a couple of the tables.
5. Easy for me to do. lol

And has anyone done this as a demo so I can see how it is done.

Tom

Board footer

Powered by FluxBB