[HELP] Connecting to a SQL Database tables?- Console Application

[nobodyknowsme]

When I click continue inside the IDE it breaks and when I clock continue it works. Thanks. However I don't want that to break inside the IDE. Thank you

[rvk]

When I click continue inside the IDE it breaks and when I clock continue it works. Thanks. However I don't want that to break inside the IDE. Thank you

In that case you need to click the "Ignore this exception type" in the exception-dialog you get. After that all the ESQLDatabase errors are automatically ignored in the IDE. (You still need to handle the exceptions in your code correctly though).

[nobodyknowsme]

Two things.

1) SetCurrentDir(GetCurrentDir + '\lib');  <-- Where do I put the directory? The folder with the dll files folder is called "dll" would I put SetCurrentDir('dll' + '\lib'); ??

2) Also I have not encrypted the database passwords. Will this cause the console application to now not work with the login?

[rvk]

1) SetCurrentDir(GetCurrentDir + '\lib');  <-- Where do I put the directory? The folder with the dll files folder is called "dll" would I put SetCurrentDir('dll' + '\lib'); ??
2) Also I have not encrypted the database passwords. Will this cause the console application to now not work with the login?

• No. If the directory is called dll and is in the program directory you would use SetCurrentDir(GetCurrentDir + '\dll');
• Not sure what you mean. If you didn't encrypt the passwords in your tables your application will just work if you check the password as plain text (which this code does). If you did encrypt the passwords you should also check for encrypted passwords in your program. But I don't think it would matter much because the administration password of your MySQL is not even encrypted (during login of your application) so security is poor either way. What would you gain by encrypting your password table. If somebody gains access to those tables he/she can do anything with them anyway.

[nobodyknowsme]

One more question. With the SQL database Ive got a table called premium and its a boolean. If they purchase the game it will go to a 1. How will I get it so there account has to be premium in the code? Basically if the SQL table premium is true they can play the game and continue however if its false they have to pay.

[rvk]

One more question. With the SQL database Ive got a table called premium and its a boolean. If they purchase the game it will go to a 1. How will I get it so there account has to be premium in the code? Basically if the SQL table premium is true they can play the game and continue however if its false they have to pay.

Instead of a table Premium you could also add a field "premium" to your users table. After executing the select for username/password you can read out the field "premium" and use it for premium content of the game. (You would also need to add "registration date" and maybe "premium date" and anything else you can think of.)

In your GetValidUsername you could add the following lines (replace everything from Query.Open to Query.Close with these lines):

Code: [Select]

    Query.Open;
    Result := Query.RecordCount > 0;
    Premium := Result and (Query.Fieldbyname('premium').AsInteger = 1);
    Query.Close;
And put a global variable "Premium: Boolean;" below the "User: String;" definition.

In the PHP version you could echo "Premium" back.
So:
Premium (for premium users)
Normal (for normal users who still need to buy the premium content)
Invalid (for unknown users)

Please note that if money is involved I would choose the more secure PHP-option over the insecure direct MySQL-calls from the game itself.
(Unless you are expecting not more than 100 users or so)

B.T.W. Upside of the PHP-solution is you don't need the MySQL-dll anymore. Also... the game might also work on college because the calls go over port 80 which probably isn't blocked.

[rvk]

An extra note about the limitations of freemysqlhosting.net:

You'll need to click a link on a weekly basis on their website to keep the MySQL online:

To keep your free database account live we ask you to follow the link below on a weekly basis, this allows us to provide the very best service by dropping those databases which are no longer required.

and the free version of the database is limited to 5MB.

(Just something to keep in mind if you're keeping with the "open" MySQL-variant)

[nobodyknowsme]

One more question. With the SQL database Ive got a table called premium and its a boolean. If they purchase the game it will go to a 1. How will I get it so there account has to be premium in the code? Basically if the SQL table premium is true they can play the game and continue however if its false they have to pay.

Instead of a table Premium you could also add a field "premium" to your users table. After executing the select for username/password you can read out the field "premium" and use it for premium content of the game. (You would also need to add "registration date" and maybe "premium date" and anything else you can think of.)

In your GetValidUsername you could add the following lines (replace everything from Query.Open to Query.Close with these lines):

Code: [Select]

    Query.Open;
    Result := Query.RecordCount > 0;
    Premium := Result and (Query.Fieldbyname('premium').AsInteger = 1);
    Query.Close;
And put a global variable "Premium: Boolean;" below the "User: String;" definition.

In the PHP version you could echo "Premium" back.
So:
Premium (for premium users)
Normal (for normal users who still need to buy the premium content)
Invalid (for unknown users)

Please note that if money is involved I would choose the more secure PHP-option over the insecure direct MySQL-calls from the game itself.
(Unless you are expecting not more than 100 users or so)

B.T.W. Upside of the PHP-solution is you don't need the MySQL-dll anymore. Also... the game might also work on college because the calls go over port 80 which probably isn't blocked.

that sounds really complicated. I've made a premium field and just want to make the console application proceed if they're premium. I am going to make it when they buy the game they will be sent a code and that code will allow them to be premium.

[nobodyknowsme]

One more question. With the SQL database Ive got a table called premium and its a boolean. If they purchase the game it will go to a 1. How will I get it so there account has to be premium in the code? Basically if the SQL table premium is true they can play the game and continue however if its false they have to pay.

Instead of a table Premium you could also add a field "premium" to your users table. After executing the select for username/password you can read out the field "premium" and use it for premium content of the game. (You would also need to add "registration date" and maybe "premium date" and anything else you can think of.)

In your GetValidUsername you could add the following lines (replace everything from Query.Open to Query.Close with these lines):

Code: [Select]

    Query.Open;
    Result := Query.RecordCount > 0;
    Premium := Result and (Query.Fieldbyname('premium').AsInteger = 1);
    Query.Close;
And put a global variable "Premium: Boolean;" below the "User: String;" definition.

In the PHP version you could echo "Premium" back.
So:
Premium (for premium users)
Normal (for normal users who still need to buy the premium content)
Invalid (for unknown users)

Please note that if money is involved I would choose the more secure PHP-option over the insecure direct MySQL-calls from the game itself.
(Unless you are expecting not more than 100 users or so)

B.T.W. Upside of the PHP-solution is you don't need the MySQL-dll anymore. Also... the game might also work on college because the calls go over port 80 which probably isn't blocked.

I've made everything closed and added this but it wont work. Sorry to be a pain. Could you possibly add it in for me? And I like that idea with the premium date. Probably in the future. The table is called premium and is a boolean.

Code: [Select]

function GetValidUsername: boolean;
var
  Query: TSQLQuery;
  Password: string;
begin
  Result := False;
  Write('Username: ');
  ReadLn(User);
  Write('Password: ');
  ReadLn(Password);
  Query := TSQLQuery.Create(nil);
  Query.Database := AConnection;
  Query.Transaction := TSQLTransaction.Create(Query);
  Query.Transaction.Database := AConnection;
  Query.SQL.Text := 'select * from users where username=:username and password=:password';
  Query.ParamByName('username').AsString := User;
  Query.ParamByName('password').AsString := Password;
  Query.Open;
  Result := Query.RecordCount > 0;           
  Query.Close;                               
  Query.Free;
end;   


[rvk]

that sounds really complicated.

It's really not that complicated.

I've made a premium field and just want to make the console application proceed if they're premium. I am going to make it when they buy the game they will be sent a code and that code will allow them to be premium.

So... let me get this straight... You have a username and password and a premium toggle? But the users can't get into the game without buying premium? Why not just add the username and password when they buy the game then? Why the term premium? Just add the user when they bought the game.

I thought you would made a difference between normal usage and some extra premium content in the game. But if that's not the case then you'll only need to add the username when you want them to be able to play the game. A code is possible too but could be shared. Username and password could also be shared but if the game checks online for this you could track the usage and block it when it's being misused. With a activation code (not tied to their machine) you have no control over who gets that code.

[rvk]

I've made everything closed and added this but it wont work.

What do you mean "you've made everything closed" ???
If you have a closed off MySQL-installation you can't use that function anymore.
Please specify exactly what you want.

Do you want the public/open MySQL-variant or the PHP variant?
Are you only adding the users to the table if they have paid? If so there is no need for "premium" because everybody you add is premium.
If not... what do you want the users to see if they are present as user but aren't premium?

[nobodyknowsme]

I've made everything closed and added this but it wont work.

What do you mean "you've made everything closed" ???
If you have a closed off MySQL-installation you can't use that function anymore.
Please specify exactly what you want.

Do you want the public/open MySQL-variant or the PHP variant?
Are you only adding the users to the table if they have paid? If so there is no need for "premium" because everybody you add is premium.
If not... what do you want the users to see if they are present as user but aren't premium?

I want them to just see "Please purchase the game" I can edit the rest I don't want it witha closed SQL yet. I will do that at a later date. COuld you please give me the updated GetValidUsername? You said about making it .closed?

[rvk]

I want them to just see "Please purchase the game" I can edit the rest I don't want it witha closed SQL yet. I will do that at a later date. COuld you please give me the updated GetValidUsername? You said about making it .closed?

I already gave you the updated GetValidUsername. (at least the lines you needed to change in the last given version).
Here it is again:

Code: [Select]

program game1;

uses
  sqldb,
  mysql56conn,
  SysUtils;

var
  AConnection: TSQLConnector;
  User: string;
  Premium: Boolean;

  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;

  function GetValidUsername: boolean;
  var
    Query: TSQLQuery;
    Password: string;
  begin
    Result := False;
    repeat
      Write('Username: ');
      ReadLn(User);
    until User <> '';
    repeat
      Write('Password: ');
      ReadLn(Password);
    until Password <> '';
    Query := TSQLQuery.Create(nil);
    Query.Database := AConnection;
    Query.Transaction := TSQLTransaction.Create(Query);
    Query.Transaction.Database := AConnection;
    Query.SQL.Text := 'select * from users where username=:username and password=:password';
    Query.ParamByName('username').AsString := User;
    Query.ParamByName('password').AsString := Password;
    Query.Open;
    Result := Query.RecordCount > 0;
    Premium := Result and (Query.FieldByName('premium').AsInteger = 1);
    Query.Close;
    Query.Free;
  end;

begin
  SetCurrentDir(GetCurrentDir + '\lib');
  try
    CreateConnection;
    AConnection.Open;
    if Aconnection.Connected then
    begin
      writeln('Successful connect!');
      if GetValidUsername then
      begin
        if Premium then
          //GotoTheGame(User) // YOUR GAME PROCEDURE
        else
          Writeln('Please purchase the game');
      end
      else
        writeln('Wrong username or password!');
    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;
  except
    on E: Exception do
      writeln('Error: ' + E.Message);
  end;
  AConnection.Free;
  writeln('End program!');
  readln;
end.


[nobodyknowsme]

I'll add that in later. One last thing. How would j make it so we could have a message in our SQL database and make it appear in the console? So we can remotely speak to people who have our game? So a message in te SQL database and it appears on the console application

[rvk]

You could create an extra field "message" to the users table. When the user logs in you could retrieve it with the same select as you do for checking the user (the same way as the premium status). After showing the message your game could set the message to blank so it doesn't display again the next time the user logs in.

This will only the display at login. If you need to display the message during the game you would need to periodically check the database for the message.