May I have advice about client-server database development?

[Tybek]

Hello,

I was considering using Revolution for a project I am working on. I have gone through the Filemaker database tutorial in the newsletters and used the Database Query Builder in Rev. I would like to know more about the possibilities of Revolution. Could someone answer my questions that follow?

Are client-server applications possible where the database IP may not be known at design time?

If I try to connect using an IP for Host in Database Query Builder it returns "Access Denied for root@serverdomain". Yet the root has complete priveleges to the database.

Is socket programming possible with Rev so that one can create servers that connect to embedded databases with clients that connect to the servers?

I tried connecting via ODBC 5.1 MySQL connector and received characters inside fields that were not meant to be there. Also using ODBC I can not set the textbox to fields in the database as with a direct connection. Is this a problem with using ODBC?

I tried ODBC because I could not find a way to connect to the database from the network, only same machine database was on.

[bangkok]

-you're talking about "root", it means you wish to use MySQL ?

-if yes, there are a few traps : the port issue (check firewall, port 3306) and also the fact that there is an option with mysql to allow "remote" (or not) access via "root".
"check the box marked "Root May Only Connect from Localhost"
http://dev.mysql.com/tech-resources/art ... ndows.html

-with MySQL better go direct. No need to pass through ODBC

-if it's Filemaker, then yes the ODBC will work only on the same machine (local). You would need FileMaker Server to allow access via network.

-regarding the (unknown) IP address of the server, could you just ask the user for the address, within the application ? Or you might create a function that "pulls" the right IP address from a dedicated web page, hosted somewhere.

-I don't understand your question about "socket programming".
You would like to create a server with Rev, that server would connect to a MySQL database, and then the clients Rev would connect to that Rev server to retrieve some datas ?
I think there is no problem to do such scheme.
Check the chat client-server application
http://bjoernke.com/chatrev/

-I would recommend you to use direct scripting instead of using the Database Query Builder. You would learn faster. And you have more control.

-for that matter, check the very good stack "MySQLtests.rev"
http://www.troz.net/Rev/tutorials.php
A great interface to connect, edit tables, make a query etc.

-last point, don't forget the public MySQL server from Revolution, great to make tests (with : "MySQLtests.rev" for instance)
database name : "test"
login : "example"
pwd : "example"
address : "runrev.com"

-I'm a newbie, but since I've discovered the couple Revolution + MySQL (or SQLite) i'm having a blast. It's really a perfect mixing.

[Tybek]

Hi bangkok,

Thank you for the information.

I have not decided on the database yet. When I searched for database tutorials I found the Filemaker newsletters.

The problem with MySQL access turned out to be too many different privileges for root. I also did not "flush privileges;".

The "MySQLtests.rev" is helpful.

I could not work out how to determine a port different from the default using scripting.

If a chat client/server is possible than I think that client/server database solution with an embedded database may also be possible.

As I have only been using Revolution a few days my skills with scripting are only developing. I did notice a bug or functionality lacking for scripting. I typed some script that turned to refer when closing a database using a databaseID that Revolution thought was non existent.

Code: Select all

 if dbResult is not empty then
      revCloseDatabase dbResult
 end if

I think this was because I opened more than one connection to the database. The result was similar to an infinite loop and I had to shutdown Revolution completely.

I used a loop to close all database connections. That worked.

Thank you for the help.[/code]

[bangkok]

The problem with MySQL access turned out to be too many different privileges for root. I also did not "flush privileges;".

Too many privileges for root ... ? In this case, why don't you create other accounts with lesser privileges ?

I used a loop to close all database connections. That worked.

Indeed.

Code: Select all

put revOpenDatabases() into connectList
  repeat for each item c in connectList
    revCloseDatabase c
  end repeat

[Tybek]

I think there were conflicts for privileges.

eg.

root@192.168.1.%/255.255.255.0 - No privileges
root@192.168.1.% - Yes privileges

though I think the flush privileges command may have made the difference. I did not test after making each change so I am not sure what worked and what did not.


Do you have an example of how to specify the port, I tried the following but got an error. Using ':' is not allowed but it is show in documentation. It must mean I am not understanding it correctly.

Code: Select all

 put field "FldPort" into dbPort
  
  put revOpenDatabase("MySQL",dbAddr:dbPort,dbName,dbUser,dbPass) into dbResult
 

[SparkOut]

Hi Tybek

The parameters each need to be one string - which can be a literal string as in the "MySQL" parameter, or resolved from a variable as in the other cases.

To make the second parameter just one string, you can either concatenate them before passing the variable to the parameter list:

Code: Select all

put field "FldDBAddr" into dbAddr
put ":" after dbAddr
put field "FldPort" after dbAddr
--now you have built up the complete string into one variable to use in the correct place
put revOpenDatabase("MySQL",dbAddr,dbName,dbUser,dbPass) into dbResult

or you can concatenate it in the parameter list directly

Code: Select all

put revOpenDatabase("MySQL",(dbAddr & ":" & dbPort),dbName,dbUser,dbPass) into dbResult

[bangkok]

root@192.168.1.%/255.255.255.0 - No privileges
root@192.168.1.% - Yes privileges

you should try "root@localhost".

In any case, maybe you don't have a proper visual administration tool for MySQL.
Try Navicat 8 Lite (free)
http://www.navicat.com/download.html

Very good. And easy to manage MySQL accounts.

Do you have an example of how to specify the port, I tried the following but got an error. Using ':' is not allowed but it is show in documentation. It must mean I am not understanding it correctly.

Code: Select all

 put field "FldPort" into dbPort
  
  put revOpenDatabase("MySQL",dbAddr:dbPort,dbName,dbUser,dbPass) into dbResult
 

try :

Code: Select all

dbAddr&":"&dbPort

[Tybek]

Hi Sparkout and bangkok,

Thanks for explanation.

I am going to go through the tutorials from the beginning and develop the foundation I require.

I am enjoying the language and tools so far.