[HELP] Connecting to a SQL - Console Application
lazarus / 16-Databases

----------------------------------------------------------------------
nobodyknowsme · 2014-12-16 15:02

I want to connect my console application to my SQL database. I have my
SQL database set up. How would I connect or link my website? In my SQL
database I have a table storing all my users and such.

I've tried to use this code but it isn't recognizing TSQLConnection,
also this is for the offline SQL database. Are there any good ways of
connecting my console application to my database easily?

Website where I got the following code from:
http://wiki.freepascal.org/SqlDBHowto/nl


The code from the website. I basically just want to link my console
application with my SQL database. Maybe link my users so that they can
sign in the application using there website details.

 Code: [Select]Program ConnectDB

 var AConnection : TSQLConnection;

 Procedure CreateConnection;
 begin
   AConnection := TIBConnection.Create(nil);
   AConnection.Hostname := 'localhost';
   AConnection.DatabaseName := '/opt/firebird/examples/employee.fdb';
   AConnection.UserName := 'sysdba';
   AConnection.Password := 'masterkey';
 end;

 begin
   CreateConnection;
   AConnection.Open;
   if Aconnection.Connected then
     writeln('Succesful connect!')
   else
     writeln('This is not possible, because if the connection failed,
an exception should be raised, so this code would not be executed');
   AConnection.Close;
   AConnection.Free;
 end.

----------------------------------------------------------------------
nobodyknowsme · 2014-12-16 15:07

EDIT: I have added sqldb to the uses and it now recognises
TSQLConnection however it will now not recognize
TIBConnection.Create(nil);. Any idea why?

----------------------------------------------------------------------
rvk · 2014-12-16 15:22

Quote from: nobodyknowsme on December 16, 2014, 04:07:33 pmEDIT: I
have added sqldb to the uses and it now recognises TSQLConnection
however it will now not recognize TIBConnection.Create(nil);. Any idea
why?
That's because TIBConnection is in IBConnection (and not sqldb). So
adding IBConnection to your uses will solve that.

You could also define AConnection as TIBConnection (instead of
TSQLConnection).

----------------------------------------------------------------------
nobodyknowsme · 2014-12-17 08:49

I have done that and I have added my websites SQL database and it wont
work now on "AConnection.Open;"


The code is attached below. Any ideas why it breaks at
AConnection.Open? Will this work and link with my online SQL database
on my website?

Code: [Select]program project1;

uses sqldb,IBConnection;

var AConnection : TIBConnection;

Procedure CreateConnection;
begin
  AConnection := TIBConnection.Create(nil);
  AConnection.Hostname := 'mysq16.000webhost.com';
  AConnection.DatabaseName := 'a3762836_issue2';
  AConnection.UserName := 'a3762836_issue2';
  AConnection.Password := '******';
end;

begin
  CreateConnection;
  AConnection.Open;
  if Aconnection.Connected then
    writeln('Successful connect!')
  else
    writeln('This is not possible, because if the connection failed, '
+
            'an exception should be raised, so this code would not ' +
            'be executed');
  AConnection.Close;
  AConnection.Free;
end.

----------------------------------------------------------------------
rvk · 2014-12-17 09:00

Your code works correctly (I just tested it).

Some thoughts as to why it doesn't work for you:
You don't give a complete path to your database a3762836_issue2. You
should always specify the exact path and filename of the database so
the database can connect to it.If mysq16.000webhost.com is another
computer/server are you sure the port for the firebird/interbase
database-engine is open. (firewall?)
Is above points are not your problem maybe you could specify the exact
error you're getting.

----------------------------------------------------------------------
nobodyknowsme · 2014-12-17 11:20

How would I give an exact path? In my SQL database I have a table
called users. How would I then get that exact directory? What hosting
details did you test it with??

Also I am not using firebird. I just want it to link to my SQL
database online.

----------------------------------------------------------------------
rvk · 2014-12-17 11:43

Quote from: nobodyknowsme on December 17, 2014, 12:20:21 pmHow would I
give an exact path? In my SQL database I have a table called users.
How would I then get that exact directory? What hosting details did
you test it with??
Also I am not using firebird. I just want it to link to my SQL
database online.
If you're not using Firebird then why are you using TIBConnection?
This is a Firebird specific connection component.
(I have a local Firebird-database running to which i connected.)

What kind of SQL-database do you have. MS-SQL, MySql or other ??

For example if you use something different from Firebird you could use
the TSQLConnector. This component has a ConnectorType which you could
set to MSSQLServer, Oracle, and all kind of MySQL versions. With MySql
you (normally) wouldn't need to specify a path with your databasename.

Example:

Code: [Select]program project1;

uses
  sqldb,
  mysql55conn;

var
  AConnection: TSQLConnector;

  procedure CreateConnection;
  begin
    AConnection := TSQLConnector.Create(nil);
    AConnection.ConnectorType := 'MySQL 5.5';
    AConnection.Hostname := 'mysq16.000webhost.com';
    AConnection.DatabaseName := 'a3762836_issue2';
    AConnection.UserName := 'a3762836_issue2';
    AConnection.Password := '******';
  end;

begin
  CreateConnection;
  AConnection.Open;
  if Aconnection.Connected then
    writeln('Successful connect!')
  else
    writeln('This is not possible, because if the connection failed, '
+
      'an exception should be raised, so this code would not ' +
      'be executed');
  AConnection.Close;
  AConnection.Free;
  readln;
end.

Note the addition of mysql55conn the your uses-clause. Although
nothing is used in the source it's still needed for the ConnectionType
:= 'MySQL 5.5'; otherwise you get an error that you're using an
unknown driver. You also need the .dll installed in your program
directory (or searchpath). If your using something else than MySQL 5.5
you'll need to adjust the mysql55conn accordingly.

----------------------------------------------------------------------
nobodyknowsme · 2014-12-17 14:06

That sounds really good however the same error is still occurring. It
breaks at AConnection.Open

It wont get passed AConnection.Open at all. Also this is better as I
do not want to use FireBird.

It doesn't break here:

Code: [Select]program project1;

uses sqldb,IBConnection;

var AConnection : TIBConnection;

Procedure CreateConnection;
begin
  AConnection := TIBConnection.Create(nil);
  AConnection.Hostname := 'mysq16.000webhost.com';
  AConnection.DatabaseName := 'a3762836_issue2';
  AConnection.UserName := 'a3762836_issue2';
  AConnection.Password := '******';
end;

begin
  CreateConnection;

  if Aconnection.Connected then
    writeln('Successful connect!')
  else
    writeln('This is not possible, because if the connection failed, '
+
            'an exception should be raised, so this code would not ' +
            'be executed');
  AConnection.Close;
  AConnection.Free;
  readln;
end.

----------------------------------------------------------------------
rvk · 2014-12-17 14:15

Quote from: nobodyknowsme on December 17, 2014, 03:06:14 pmThat sounds
really good however the same error is still occurring. It breaks at
AConnection.Open
It wont get passed AConnection.Open at all. Also this is better as I
do not want to use FireBird.
It doesn't break here:
Code: [Select]  AConnection := TIBConnection.Create(nil);
I'm not sure what you're trying to accomplish with your last code.
You're still using TIBConnection and create a connection-instance with
a Firebird server (TIBConnection is only for Firebird) but you don't
connect it. Of course you don't get an error in that case. If you
don't do a .Open there is nothing to report. But you don't get
connected either.

With the code I showed you... what error do you get on the
AConnection.Open line ??

You also didn't answer amy of my other questions.
What kind of database server is installed on that host?
What version of the database server is installed on that host?

One thing is for sure... If it's not a Firebird database engine that's
installed on that host you can't use TIBConnection.

----------------------------------------------------------------------
rvk · 2014-12-17 14:25

I just checked and webhost mysq16.000webhost.com reports back that it
expects that you use MySQL 4.1.18 drivers.
QuoteTMySQL55Connection can not work with the installed MySQL client
version: Expected (5.5), got (4.1.18).
So you need to specify that you're using MySql 4.1

Code: [Select]program project1;

uses
  sqldb,
  mysql41conn;

var
  AConnection: TSQLConnector;

  procedure CreateConnection;
  begin
    AConnection := TSQLConnector.Create(nil);
    AConnection.ConnectorType := 'MySQL 4.1';
    AConnection.Hostname := 'mysq16.000webhost.com';
    AConnection.DatabaseName := 'a3762836_issue2';
    AConnection.UserName := 'a3762836_issue2';
    AConnection.Password := '******';
  end;

begin
  CreateConnection;
  AConnection.Open;
  if Aconnection.Connected then
    writeln('Successful connect!')
  else
    writeln('This is not possible, because if the connection failed, '
+
      'an exception should be raised, so this code would not ' +
      'be executed');
  AConnection.Close;
  AConnection.Free;
  readln;
end.

B.T.W. did you install the libmysql.dll in your program directory ?

----------------------------------------------------------------------
nobodyknowsme · 2014-12-17 16:22

I haven't installed anything no. Where would I download this file?? Is
it needed?

----------------------------------------------------------------------
nobodyknowsme · 2014-12-17 18:56

That the issue..

http://screenshu.com/static/uploads/temporary/jk/rq/vx/pwbx8r.jpg

I need that file :/

----------------------------------------------------------------------
nobodyknowsme · 2014-12-17 19:05

I have downloaded that file. What do I do with it? Where do I put it?

----------------------------------------------------------------------
rvk · 2014-12-17 19:10

Quote from: nobodyknowsme on December 17, 2014, 08:05:47 pmI have
downloaded that file. What do I do with it? Where do I put it?
You can put in in your program directory. (The one with your
executable)

----------------------------------------------------------------------
nobodyknowsme · 2014-12-17 19:12

Its in the folder with my exe and I run it and it still is giving me
the same error.

http://screenshu.com/static/uploads/temporary/ub/bl/du/e2osm7.jpg

Any ideas?

----------------------------------------------------------------------
nobodyknowsme · 2014-12-17 19:14

http://screenshu.com/static/uploads/temporary/ub/bl/du/e2osm7.jpg

Its in the folder?

----------------------------------------------------------------------
rvk · 2014-12-17 19:34

Then the libmysql.dll is not the correct one (or corrupt).
Where did you get it?

And i stand corrected about the version.
According to this it should be 5.1
http://www.000webhost.com/free-mysql-hosting

So you should use "MySQL 5.1" as ConnectionType and mysql51conn in the
uses-clause. (and the correct libmysql.dll)

----------------------------------------------------------------------
nobodyknowsme · 2014-12-17 19:41

I downloaded it off a random DLL website. I now get this error:

http://screenshu.com/static/uploads/temporary/dx/w4/6f/f2x9qy.jpg

If you can see that. I put the DLL file into system32 and my folder.

----------------------------------------------------------------------
nobodyknowsme · 2014-12-17 19:44

Quote from: rvk on December 17, 2014, 08:34:04 pmThen the libmysql.dll
is not the correct one (or corrupt).
Where did you get it?

And i stand corrected about the version.
According to this it should be 5.1
http://www.000webhost.com/free-mysql-hosting

So you should use "MySQL 5.1" as ConnectionType and mysql51conn in the
uses-clause. (and the correct libmysql.dll)

Also when I put SQL 5.1 is then doesn't work. Its 4.1 thats fine. I
still can't get it to get passed the breaking at Aconnetion.open. When
I install the funny dll file it doesn't give me that error but just
self closes however when I change it to 5.1 it then re gives me the
error.

----------------------------------------------------------------------
rvk · 2014-12-17 20:01

There might also be another problem going on...
I'm trying to get it to work on my end but are also getting errors.

Are you sure you have remote access to that host and database??

Normally internet providers block direct access to MySQL and only
allow their own machines access.
See also http://serverfault.com/a/481616/189940

----------------------------------------------------------------------
nobodyknowsme · 2014-12-17 20:10

So you think its there database? So if I change the databases the
program will work?

----------------------------------------------------------------------
nobodyknowsme · 2014-12-17 20:13

Quote from: rvk on December 17, 2014, 09:01:01 pmThere might also be
another problem going on...
I'm trying to get it to work on my end but are also getting errors.

Are you sure you have remote access to that host and database??

Normally internet providers block direct access to MySQL and only
allow their own machines access.
See also http://serverfault.com/a/481616/189940

Could you please try a random server and see if thats the issue? That
"http://www.freesqldatabase.com/" website doesn't work

----------------------------------------------------------------------
rvk · 2014-12-17 20:18

Well, I'm getting this error if I try to connect using MySQL
Workbench.
http://www.mysql.com/products/workbench/
You can download it yourself (you can skip the registration).

In your setup of the database at 000webhost, do you have options to
allow certain IP-addresses ??

----------------------------------------------------------------------
nobodyknowsme · 2014-12-17 20:22

No. Ill look to how I would make it public now. However will it work
on an open server?

----------------------------------------------------------------------
rvk · 2014-12-17 21:53

I know from my own MySQL server (on an internal Linux-box) that you
need to add 'username'@'hostname' to the user table to allow a
connection from hostname. You could use % as hostname but that opens
up a whole lot of security issues.

I can connect to my own box with MySQL Workbench and finally was able
to connect from Lazarus remotely to that box. The error-messages
Lazarus produces are very unclear. I always got a "Server connect
failed" until I opened up the connection to a wildcard-host (the % I
mentioned above). I should have gotten the same clear message
Workbench gave me. But it works. Changing the % (wildcard) to my
hostname (from where I connected) works too.

So... some notes.
You need to add the username@host to your users table in mysql (I'm
not sure if this is possible at 000webhost) or otherwise the provider
should have provided a webinterface to set "allowed hosts" (which very
few do because of security issues).You need the right libmysql.dll (in
combination with the client installed.) You probably can connect with
5.1 to a 5.5 server but if you have a 5.1 library you should use
mysql51conn and "MySQL 5.1" (or go with TMySQL51Connection). The last
example we used was okay.
So, I think the first thing you need to do is check if you can set an
allowed host for that mysql database. If not you're out of luck (at
that provider) and you can only access the mysql from the webpages
hosted at that same provider. If that's the case you could also look
for a provider where you can set access from outside.

----------------------------------------------------------------------
rvk · 2014-12-17 21:55

And here is your answer:
http://www.000webhost.com/faq.php?ID=27

QuoteHow can I connect to MySQL from my computer?
Remote MySQL connections are disabled for security and server
performance reasons.

However remote MySQL connections will be enabled if you upgrade your
account at http://www.000webhost.com/upgrade

----------------------------------------------------------------------
nobodyknowsme · 2014-12-17 22:03

A few questions.
1) When I compile the exe and send it to a friend they wont need the
dll file will they?
2) What hosting are you using for the SQL? I want touse the same as it
sounds easier
3) I have no idea how to add username@myhost and stuff

Sorry.

----------------------------------------------------------------------
nobodyknowsme · 2014-12-17 22:05

So all in all I need to get the username@domain and the dll file and
it should work?

----------------------------------------------------------------------
rvk · 2014-12-17 22:28

Quote from: nobodyknowsme on December 17, 2014, 11:05:31 pmSo all in
all I need to get the username@domain and the dll file and it should
work?
You would need to add 'a3762836'@'you_own_hostname' to the user table
in the mysql installation and grant the necessary access. You can do
that with your own installation of MySQL but you can't do this at a
MySQL-provider. They just don't allow that. And 000webhost completely
disables remote access for their free accounts.

Quote from: nobodyknowsme on December 17, 2014, 11:03:45 pmA few
questions.
1) When I compile the exe and send it to a friend they wont need the
dll file will they?
2) What hosting are you using for the SQL? I want touse the same as it
sounds easier
3) I have no idea how to add username@myhost and stuff

Sorry.
Yes, he does need the libmysql.dll too. So you need to send both
files.I don't have any open MySQL-connections to the internet. I have
my own server for my internal network. My internal webpages (which a
can host to the internet if i wanted to) access the locally installed
MySQL-installation. My professional website is hosted by an internet
provider. The MySQL-access there is also blocked and only accessible
from their server (i.e. my professional website)You can only add the
user@host with your own MySQL-installation. Others will never let you
have access to it because it would be a security breach.(no need to be
sorry, i'm happy to explain it)

So... you have 4 options:
Depending on what the program needs to do... you could create this in
PHP (instead of Lazarus) and let the users go to your webpages at
000webhost where they interact with those pages.You can install your
own MySQL-server and allow port 3306 for MySQL through your router to
your PC. Your PC would need to be on for others to access your MySQL-
server. You could use Xamp or Wamp for a complete and easy to install
solution for a web and MySQL-installationLike option 1... you could
create webpages (instead of a Pascal-program) and host them yourself
on your own computer. For this options your computer also needs to be
on when they want to access the pages and you can use the same Xamp or
Wamp installation as option 2. In this case you wouldn't forward port
3306 but port 80 to your own computer.
You can look for another MySQL-provider which allows external access.
There are a few but I'm not sure what their quality is.
O... yeah... option 5.... pay for access to a MySQL-server   In that
case they usually have an option for remote access. But always check
beforehand. And if your program runs at multiple locations you need to
make sure you have access from all those (or all) locations.

Your choice would all depend on what kind of program it is and who
uses it.

----------------------------------------------------------------------
nobodyknowsme · 2014-12-17 22:47

Thank you. It's a Lazuras game. They use there login from the website
on there console appication. It needs to be a console application. I
will have to buy a SQL database that allows outside access to everyone
as the program will be available to everyone. It will have to be
option 4.

----------------------------------------------------------------------
nobodyknowsme · 2014-12-17 22:48

Also, I won't be able to host this on my PC as I want it dedicated. So
I'll have to buy a SQL server

----------------------------------------------------------------------
nobodyknowsme · 2014-12-17 23:00

Quote from: rvk on December 17, 2014, 11:28:56 pmQuote from:
nobodyknowsme on December 17, 2014, 11:05:31 pmSo all in all I need to
get the username@domain and the dll file and it should work?
You would need to add 'a3762836'@'you_own_hostname' to the user table
in the mysql installation and grant the necessary access. You can do
that with your own installation of MySQL but you can't do this at a
MySQL-provider. They just don't allow that. And 000webhost completely
disables remote access for their free accounts.

Quote from: nobodyknowsme on December 17, 2014, 11:03:45 pmA few
questions.
1) When I compile the exe and send it to a friend they wont need the
dll file will they?
2) What hosting are you using for the SQL? I want touse the same as it
sounds easier
3) I have no idea how to add username@myhost and stuff

Sorry.
Yes, he does need the libmysql.dll too. So you need to send both
files.I don't have any open MySQL-connections to the internet. I have
my own server for my internal network. My internal webpages (which a
can host to the internet if i wanted to) access the locally installed
MySQL-installation. My professional website is hosted by an internet
provider. The MySQL-access there is also blocked and only accessible
from their server (i.e. my professional website)You can only add the
user@host with your own MySQL-installation. Others will never let you
have access to it because it would be a security breach.(no need to be
sorry, i'm happy to explain it)

So... you have 4 options:
Depending on what the program needs to do... you could create this in
PHP (instead of Lazarus) and let the users go to your webpages at
000webhost where they interact with those pages.You can install your
own MySQL-server and allow port 3306 for MySQL through your router to
your PC. Your PC would need to be on for others to access your MySQL-
server. You could use Xamp or Wamp for a complete and easy to install
solution for a web and MySQL-installationLike option 1... you could
create webpages (instead of a Pascal-program) and host them yourself
on your own computer. For this options your computer also needs to be
on when they want to access the pages and you can use the same Xamp or
Wamp installation as option 2. In this case you wouldn't forward port
3306 but port 80 to your own computer.
You can look for another MySQL-provider which allows external access.
There are a few but I'm not sure what their quality is.
O... yeah... option 5.... pay for access to a MySQL-server   In that
case they usually have an option for remote access. But always check
beforehand. And if your program runs at multiple locations you need to
make sure you have access from all those (or all) locations.

Your choice would all depend on what kind of program it is and who
uses it.

Could I use this??

http://support.hostgator.com/articles/cpanel/how-to-connect-to-the-
mysql-database-remotely

Host gator says you can access it remotely. Does this mean this SQL
database will work? It says about just adding IPs. I want it accessed
to everyone. Or should I upgrasd my 000webhosting account?

----------------------------------------------------------------------
rvk · 2014-12-17 23:08

Yes, if it is a game for which the MySQL connection always need to be
present hosting it yourself would not be practical (unless you have an
old PC and a dedicated internet connection).

If the game is still in development you could try some of the free
MySQL-providers and see how their quality are.
(search for free mysql hosting with external access)

If in the end there is a lot of money involved and the game needs to
"grow" it's best to choose for a professional (paid) option. Just make
sure the database is accessible from everywhere and not that you need
to add a host to the settings each time for it to be allowed.

B.T.W. if it's only for login and/or small communication packages
(i.e. scoring etc.) you could also create a small interface in PHP on
any given website. That PHP would have local access to the MySQL-
database because it is hosted on the same machine. (every website
provider with a MySQL-database option will do in that case)


Quote from: nobodyknowsme on December 18, 2014, 12:00:42 amHost gator
says you can access it remotely. Does this mean this SQL database will
work? It says about just adding IPs. I want it accessed to everyone.
Or should I upgrasd my 000webhosting account?
Adding IP manually wouldn't be practical in your case. If that's the
only access they allow it won't work. I'm not sure if 000webhosting
does the same. (That's why I said above you needed to make sure they
allow "wildcard"-hostname access, I'm not even sure there are any with
that kind of access)

----------------------------------------------------------------------
nobodyknowsme · 2014-12-17 23:14

Wait so my pho script could store the logins and could access it from
there? That sounds like a good idea. How would I go about doing that?
Or shall I just use hosting with external SQL. I've looked at some
websites and not to be lazy however could you look and find a website
that allows the SQL external database? If you see an okay one link me?
Thank you so much. I would be so stuck without you.

----------------------------------------------------------------------
rvk · 2014-12-17 23:28

Quote from: nobodyknowsme on December 18, 2014, 12:14:08 amWait so my
pho script could store the logins and could access it from there? That
sounds like a good idea. How would I go about doing that? Or shall I
just use hosting with external SQL. I've looked at some websites and
not to be lazy however could you look and find a website that allows
the SQL external database? If you see an okay one link me? Thank you
so much. I would be so stuck without you.
When looking for MySQL-providers you need to add the term "wildcard"
to your search. This is one I found quite easily:
http://www.inmotionhosting.com/support/website/databases/setting-up-a-
remote-mysql-connection-in-cpanel#wildcard
But now I'm sure there are more.
(Can't speak about the reliability of that provider as they are
unknown to me)

Hey... I even found hostgator does accept wildcards for MySQL
http://support.hostgator.com/articles/cpanel/how-to-whitelist-your-ip-
in-cpanel-for-remote-mysql-access

QuoteNote: If you're connecting from a dynamic IP address, you can use
the "%" wildcard value instead of an IP address.  However, since this
will grant access from anywhere, it is generally considered a security
risk and therefore not recommended.

And for the PHP script: The PHP-script itself wouldn't even need to
store it but interact with the local MySQL database. Simple example of
a call to a PHP for adding a user:
http://www.yourdomain.com/game.api/add_user.php?user=me&password=secre
t

Checking for a user:
http://www.yourdomain.com/game.api/check_user.php?user=me&password=sec
ret
This php-script could search in the MySQL-database is user me exists
and if password is secret. It could echo correct or incorrect back.

This is just a very simple example and easily hackable so there would
need to be some security build in that you are sure the answer is from
your domain.

----------------------------------------------------------------------
nobodyknowsme · 2014-12-18 07:52

What do you mean wild card? Also for security issues I won't use the
php script.thank you!

----------------------------------------------------------------------
minesadorada · 2014-12-18 09:20

I have a Lazarus app that connects to freemysqlhosting.net
successfully.

For the free account, you have to verify it each week, but they also
offer paid hosting.

----------------------------------------------------------------------
nobodyknowsme · 2014-12-18 09:26

Okay, does it allow wild cards? And will it allow my to create a
username@database?

----------------------------------------------------------------------
nobodyknowsme · 2014-12-18 09:28

Quote from: nobodyknowsme on December 18, 2014, 10:26:10 amOkay, does
it allow wild cards? And will it allow my to create a
username@database?

Will it connect though a table so that it can match someones username
and password in theory?

----------------------------------------------------------------------
nobodyknowsme · 2014-12-18 09:36

Quote from: minesadorada on December 18, 2014, 10:20:21 amI have a
Lazarus app that connects to freemysqlhosting.net successfully.

For the free account, you have to verify it each week, but they also
offer paid hosting.

minesadorada or rvk

It still wont work though it? As its open and free I have left the
password in this one.
It breaks at AConnection.Open again. Any ideas why?

Code: [Select]program project1;

uses
  sqldb, mysql41conn;

var
  AConnection: TSQLConnector;

  procedure CreateConnection;
  begin
    AConnection := TSQLConnector.Create(nil);
    //AConnection.ConnectorType := 'MySQL 4.1';
    AConnection.Hostname := 'sql5.freemysqlhosting.net';
    AConnection.DatabaseName := 'sql561707';
    AConnection.UserName := 'sql561707';
    AConnection.Password := 'dX3%eP3!';
  end;

begin
  CreateConnection;
  AConnection.Open;
  if Aconnection.Connected then
    begin
    writeln('Successful connect!');
    readln
    end

  else
    writeln('This is not possible, because if the connection failed,
an exception should be raised, so this code would not be executed');
  AConnection.Close;
  AConnection.Free;

  readln;

end.

----------------------------------------------------------------------
minesadorada · 2014-12-18 09:41

Quote from: nobodyknowsme on December 18, 2014, 10:26:10 amOkay, does
it allow wild cards? And will it allow my to create a
username@database?

It´s a standard MySQL v5.6 server.  You get one database, with as many
tables as you like.  There is one master login, and a CPanel to manage
your database.  Check out their website to see if it has restrictions
that would be a problem for your app.

My app uses mysql56conn and a TMySQL56Connection object to connect to
it

http://www.freemysqlhosting.net/

----------------------------------------------------------------------
nobodyknowsme · 2014-12-18 09:50

COuld I have your code as an example at all? At the moment this
doesn't work. ANy ideas why? I created a random table called users.

Code: [Select]program project1;

uses
  sqldb, mysql41conn;

var
  AConnection: TSQLConnector;

  procedure CreateConnection;
  begin
    AConnection := TSQLConnector.Create(nil);
    AConnection.ConnectorType := 'MySQL 5.6';
    AConnection.Hostname := 'sql5.freemysqlhosting.net';
    AConnection.DatabaseName := 'sql561707';
    AConnection.UserName := 'sql561707';
    AConnection.Password := 'dX3%eP3!';
  end;

begin
  CreateConnection;
  AConnection.Open;
  if Aconnection.Connected then
    begin
    writeln('Successful connect!');
    readln
    end

  else
    writeln('This is not possible, because if the connection failed,
an exception should be raised, so this code would not be executed');
  AConnection.Close;
  AConnection.Free;

  readln;

end.

----------------------------------------------------------------------
minesadorada · 2014-12-18 09:59

Quote from: nobodyknowsme on December 18, 2014, 10:50:33 amCOuld I
have your code as an example at all? At the moment this doesn't work.
ANy ideas why? I created a random table called users.

Code: [Select]program project1;

uses
  mysql56conn;

var
  Aconnection: TMySQL56Connection;

  procedure CreateConnection;
  begin
    Aconnection:=TMySQL56Connection.Create(Nil);
    AConnection.Port:=3306;
    AConnection.Role:='User';
    AConnection.Hostname := 'sql5.freemysqlhosting.net';
    AConnection.DatabaseName := 'sql561707';
    AConnection.UserName := 'sql561707';
    AConnection.Password := 'dX3%eP3!';
  end;

begin
  CreateConnection;
  AConnection.Open;
  if Aconnection.Connected then
    begin
    writeln('Successful connect!');
    readln
    end

  else
    writeln('This is not possible, because if the connection failed,
an exception should be raised, so this code would not be executed');
  AConnection.Close;
  AConnection.Free;

  readln;

end.
Here are some changes that may help  (code is untested)

----------------------------------------------------------------------
nobodyknowsme · 2014-12-18 10:05

It still breaks at  AConnection.Open. When this happened last time it
meant it wasn't connecting properly. I've entered all the details
correctly.

----------------------------------------------------------------------
minesadorada · 2014-12-18 10:08

Does your router have the port 3306 open to the internet?  Is an AV
interfering with the port access?

What is the exception raised?  There is no code to trap it in your
example.

----------------------------------------------------------------------
nobodyknowsme · 2014-12-18 10:12

It opens and closes straight away like last time now. Its like it
doesn't have access. Its connecting I think without any errors but now
just selfcloses

----------------------------------------------------------------------
minesadorada · 2014-12-18 10:14

Quote from: nobodyknowsme on December 18, 2014, 11:12:16 am
It opens and closes straight away like last time now. Its like it
doesn't have access. Its connecting I think without any errors but now
just selfcloses
What operating system are you using?  I might be able to do and attach
a working example for you.

----------------------------------------------------------------------
nobodyknowsme · 2014-12-18 10:23

Im using windows 7. AS the console application closes so quickly I wil
try and get a photo.

----------------------------------------------------------------------
rvk · 2014-12-18 10:24

It shouldn't be necessary to open 3306 on your router as it is
outgoing.

This works perfectly for me:
But you need the libmysql.dll from 5.6 in your .exe directory.
You can take the one from the latest MySQL Workbench installation.

Code: [Select]program upload2;

uses
  sqldb, mysql56conn;

var
  AConnection: TSQLConnector;

  procedure CreateConnection;
  begin
    AConnection := TSQLConnector.Create(nil);
    AConnection.ConnectorType := 'MySQL 5.6';
    AConnection.Hostname := 'sql5.freemysqlhosting.net';
    AConnection.DatabaseName := 'sql561707';
    AConnection.UserName := 'sql561707';
    AConnection.Password := 'dX3%eP3!';
  end;

begin
  CreateConnection;
  AConnection.Open;
  if Aconnection.Connected then
    begin
    writeln('Successful connect!');
    readln
    end

  else
    writeln('This is not possible, because if the connection failed,
an exception should be raised, so this code would not be executed');
  AConnection.Close;
  AConnection.Free;

  readln;

end.
(Result is 'Successful connect!')

----------------------------------------------------------------------
nobodyknowsme · 2014-12-18 10:35

Okay, It took me a few tries but I got it. Its the libmysql.dll file.
I have it installed in the same folder. Do I have a bad dll file or
something?

----------------------------------------------------------------------
rvk · 2014-12-18 10:37

If you right-click the libmysql.dll and choose properties and look in
details...
what version do you see for that file?

(The one from the latest MySQL Workbench is 5.6.17.0 and works for me)

----------------------------------------------------------------------
minesadorada · 2014-12-18 10:41

Attached: working project (without libmysql.dll)

Put libmysql.dll in the same folder as the project and it should
connect.  Tested on Win 7

----------------------------------------------------------------------
nobodyknowsme · 2014-12-18 10:41

Could you please send me your file as mine doesn't display the version
type. Could you please send me your dll file?

----------------------------------------------------------------------
minesadorada · 2014-12-18 10:44

Quote from: nobodyknowsme on December 18, 2014, 11:41:32 amCould you
please send me your file as mine doesn't display the version type.
Could you please send me your dll file?
https://sourceforge.net/projects/healthdailymonitor/  It's included in
the Installer

----------------------------------------------------------------------
nobodyknowsme · 2014-12-18 10:49

healthdailymonitor? Im sorry whats that? Slightly confused.

----------------------------------------------------------------------
rvk · 2014-12-18 10:51

healthdailymonitor is a program which includes libmysql.dll in its
installation,.
So does MySQL Workbench (which is downloadable as a zip so you won't
need installation).

But I already PM'd you a link to my version of libmysql.dll 5.6.17.0.

----------------------------------------------------------------------
nobodyknowsme · 2014-12-18 10:52

okay thank you. I can't download that at the moment as my colleges
server wont allow me to access it.

----------------------------------------------------------------------
nobodyknowsme · 2014-12-18 10:55

Quote from: rvk on December 18, 2014, 11:51:06 amhealthdailymonitor is
a program which includes libmysql.dll in its installation,.
So does MySQL Workbench (which is downloadable as a zip so you won't
need installation).

But I already PM'd you a link to my version of libmysql.dll 5.6.17.0.

I have downloaded your DLL file and put it in the directory. I don't
get that error anymore however it now breaks at AConnection.Open;

Here is my code, and a screen shot showing the latest dll file. Thank
you so much for help BTW.

 Also would it matter the type of OS? I am running windows 7. I have
your DLL file and your code. WHy doesn't it work. DO I need to do
something with the file or something?! Could you dropbox me your whole
folder with this in at all?

Thanks again

----------------------------------------------------------------------
rvk · 2014-12-18 11:01

Quote from: nobodyknowsme on December 18, 2014, 11:52:01 amokay thank
you. I can't download that at the moment as my colleges server wont
allow me to access it.
I'm beginning to think your college blocks direct access to the
internet (as minesadorada already asked) and only allows port 80
activity.
So maybe port 3306 is blocked by the Firewall of your college.

Could you download MySQL Workbench and install it. It's a MySQL
manager local on your machine which can connect to a remote MySQL-
database. You can create and fill in tables etc.

http://dev.mysql.com/downloads/workbench/
Choose download (Windows (x86, 32-bit), ZIP Archive). (After unzipping
you can run it directly)
You don't have to register to download... In the next screen you can
just choose "No thanks, just start my download." at the bottom.

----------------------------------------------------------------------
minesadorada · 2014-12-18 11:23

Quote from: nobodyknowsme on December 18, 2014, 11:55:24 amI have
downloaded your DLL file and put it in the directory. I don't get that
error anymore however it now breaks at AConnection.Open;
Download and compile and run the example app I posted. Make sure the
downloaded mysqllib.dll is in the same folder as the compiled app.

If it doesn't connect successfully on your system, then you have
either a port problem (as rvk said) or an AV app that is blocking it.

----------------------------------------------------------------------
nobodyknowsme · 2014-12-18 13:51

Quote from: rvk on December 18, 2014, 12:01:17 pmQuote from:
nobodyknowsme on December 18, 2014, 11:52:01 amokay thank you. I can't
download that at the moment as my colleges server wont allow me to
access it.
I'm beginning to think your college blocks direct access to the
internet (as minesadorada already asked) and only allows port 80
activity.
So maybe port 3306 is blocked by the Firewall of your college.

Could you download MySQL Workbench and install it. It's a MySQL
manager local on your machine which can connect to a remote MySQL-
database. You can create and fill in tables etc.

http://dev.mysql.com/downloads/workbench/
Choose download (Windows (x86, 32-bit), ZIP Archive). (After unzipping
you can run it directly)
You don't have to register to download... In the next screen you can
just choose "No thanks, just start my download." at the bottom.

I downloaded the SQL workbench and it wont even open on the colleges
PC. When I get home I will have a look to see.  think it mayu be the
collegs PC>

----------------------------------------------------------------------
rvk · 2014-12-18 13:55

Quote from: nobodyknowsme on December 18, 2014, 02:51:28 pmI
downloaded the SQL workbench and it wont even open on the colleges PC.
When I get home I will have a look to see.  think it mayu be the
collegs PC>
Are you anticipating that the game will  be played much on colleges?
If so... maybe you need to find another solution like the php-scripts
or hosting MySQL yourself and using port 80 for it. You should only
consider that if you think a lot of people will use the game behind
proxies and firewalls.

----------------------------------------------------------------------
nobodyknowsme · 2014-12-18 15:24

See the thing is. A lot of people have already tried accessing/hacking
my SQL database. I want it as secure as possible.

----------------------------------------------------------------------
nobodyknowsme · 2014-12-18 15:36

Quote from: minesadorada on December 18, 2014, 11:41:03 amAttached:
working project (without libmysql.dll)

Put libmysql.dll in the same folder as the project and it should
connect.  Tested on Win 7

Even with your version it still gets stuck on Aconnection.Open;

----------------------------------------------------------------------
nobodyknowsme · 2014-12-18 16:02

Another thing, I want my SQL database to be as secure as possible as
people are already trying to get access to it. Any ideas?

----------------------------------------------------------------------
rvk · 2014-12-18 16:36

Secure from what? Random people trying to login or from hacking the
game?

As far as I can see freemysqlhosting doesn't offer SSL access to their
MySQL-databases so the only thing you can do is use a very good
password (if you can change it, I couldn't find it). That will keep
the random hackers away. (Unfortunately you can't even change the port
to something random at freemysqlhosting). But securing it from the
people who have the game....? They can always trace what you're
sending to the MySQL servers (via the game and catching all internet
traffic) so they could potentially get access if they see your
username and password in that traffic. Only thing I can think of is
using the MySQL-database only for read-access in the game and when you
need to change or add something in the database you do that with an
webpage-call (maybe PHP) where you can hide the master-password for
writing. But again... as far as I can see there is only 1 user and 1
password setup at freemysqlhosting.

----------------------------------------------------------------------
nobodyknowsme · 2014-12-18 17:33

I have a table called users and in that table are usernames and
passwords in my SQL database. How would I get it to work so if they
type in the user name and password it will show them a message if they
get there details correct. Basically how can I get it so if they type
in there username and password it will do something?

Thanks, here is an example program of how it connects to my SQL
database. I am fully aware about the username and password being
shown. This is a test database for testing. Anyway any ideas?

Code: [Select]program project1;

uses
  sqldb, mysql56conn;

var
  AConnection: TSQLConnector;

  procedure CreateConnection;
  begin
    AConnection := TSQLConnector.Create(nil);
    AConnection.ConnectorType := 'MySQL 5.6';
    AConnection.Hostname := 'sql5.freemysqlhosting.net';
    AConnection.DatabaseName := 'sql561707';
    AConnection.UserName := 'sql561707';
    AConnection.Password := 'dX3%eP3!';
  end;

begin
  CreateConnection;
  AConnection.Open;
  if Aconnection.Connected then
    begin
    writeln('Successful connect!');
    readln
    end

  else
    writeln('This is not possible, because if the connection failed,
an exception should be raised, so this code would not be executed');
  AConnection.Close;
  AConnection.Free;

  readln;

end.