Recent

Author Topic: MySQL 5.5 simple questions  (Read 6384 times)

User137

  • Hero Member
  • *****
  • Posts: 1791
    • Nxpascal home
MySQL 5.5 simple questions
« on: October 15, 2012, 05:26:55 pm »
1) Is MySQL 5.5 fully supported in SVN yet, or should i be using 5.1 instead? I have a TMySQL55Connection created on TForm with varying success in its use.

About the "varying success", i can connect to my MySQL 5.5 database and get some data from it. But since i am a newbie with delphi/lazarus database classes, i'd like some verification on how code should be written. I don't use any visual components, just manually accessing the database with commands.
Code: [Select]
  try
    query.SQL.Text:=
      'SELECT id FROM player WHERE active=0 LIMIT 0, 1';
    query.Open;
    if not query.EOF then begin // Is there better way to ask if result is not null?
      showmessage(query.Fields[0].AsString);
      game.myid:=query.Fields[0].AsInteger; // Crash here
    end;
  finally
    query.Close; // Do i have to close the query every time?
  end;
2) Above code crashes on .AsInteger. That would, i think, mean that SQL 5.5 implementation is incomplete, and i would propably have to use AsString to extract all data. (I mean AsInteger is virtual function with raise exception in the default behavior. I have no idea where the MySQL implementation of it would be.) However i did a similar test to other field with float value and that worked. In the database ID has type INT(4), (all names in lowercase).

3) Making update to database is unclear to me too.
Code: [Select]
  query.SQL.Text:=format(
    'UPDATE player SET active=1 WHERE id=%d', [game.myid]);
  //query.Open;
  query.ExecSQL;
  //query.Close;
Just what of them should i call, and how do i know it was correctly updated? I can issue SQL commands with MySQL Workbench just fine, it seems that i'm much less limited with syntax with the Lazarus. The above select commands work fine with ' ' and without with field names or values.
« Last Edit: October 15, 2012, 05:31:17 pm by User137 »

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: MySQL 5.5 simple questions
« Reply #1 on: October 15, 2012, 06:13:39 pm »
1. 5.5 support: I suppose so; haven't heard negative reports compared to 5.1 in any case.
If you want to, you can run the dbtestframework.pas test suite on both connectors and see if they both pass the same number of tests.

2. Finding out whether there are results: you can use .EOF, you can also use .RecordCount
3. Regarding crash with asinteger: yes, it seems that way. You could compare with 5.1 and/or raise a bug (or wait for others to comment first)
4. Closing query: yes, after opening it, you have to close it AFAIU
5. Instead of the update sql you're using in general I'd suggest using parametrized queries (see the wiki) to avoid quoting/formatting issues with test/dates. An integer field si relatively harmless though
6. Query.open is for returning data/recordsets/datasets. Query.Execute is for executing SQL when you don't care about the results. When submitting UPDATE you therefore would use Execute; when you use e.g. Firebird's INSERT...RETURNING you would use Open

Have a look at the wiki pages on working with tsqlquery; other pages may be relevant as well.
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

User137

  • Hero Member
  • *****
  • Posts: 1791
    • Nxpascal home
Re: MySQL 5.5 simple questions
« Reply #2 on: October 15, 2012, 11:44:31 pm »
I did a full fpc and lazarus SVN updates, and turned out that issue wasn't in SQL itself, but my code  :-[  game object was created after the SQL queries. So .AsInteger works. But trying to call "UPDATE" does not do anything.

Also i managed to get this error:
Quote
EDatabaseError... Operation not allowed, dataset "query" is not in an edit or insert state.
.. after finding more alternatives to try (i found there is UpdateSQL in addition to SQL):
Code: [Select]
    query.UpdateSQL.Text:=format(
      'UPDATE player SET active=1 WHERE id=%d', [game.myid]);
    //query.UpdateMode:=upWhereAll;
    query.UpdateRecord; // exception raised here, and again not sure i should be calling this
    //query.ExecSQL;
    //query.Params.AddParam();
I have read millions of webpages already... Like this explains something about dataset.Edit, to set the mode. But i don't even have a dataset, should i?
I'm also using new non-root user for the connection, and it's also tested to have listing and update rights.

taazz

  • Hero Member
  • *****
  • Posts: 5365
Re: MySQL 5.5 simple questions
« Reply #3 on: October 16, 2012, 06:01:38 am »
Query is a dataset descendant so yes you do have one. just try it

query.edit;
query.fieldbyname('Myfield').asinteger := myNewValue;
query.post;
query.applyupdates;
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: MySQL 5.5 simple questions
« Reply #4 on: October 16, 2012, 06:15:32 am »
You're making progress ;)

@taazz: looking at the code, he may not have his game data in datasets. Below, I'm assuming he hasn't..
Also i managed to get this error:
Quote
EDatabaseError... Operation not allowed, dataset "query" is not in an edit or insert state.
You probably tried something like Taazz is suggesting there.

.. after finding more alternatives to try (i found there is UpdateSQL in addition to SQL):
Code: [Select]
    query.UpdateSQL.Text:=format(
      'UPDATE player SET active=1 WHERE id=%d', [game.myid]);
    //query.UpdateMode:=upWhereAll;
    query.UpdateRecord; // exception raised here, and again not sure i should be calling this
    //query.ExecSQL;
    //query.Params.AddParam();
You shouldn't be using UpdateSQL here. It's used for sending update statements back to the database when you have data in your dataset/query which you have edited (i.e. taazz' example). AFAIU, you don't have data in your query, you just want to execute a statement.

I have read millions of webpages already... Like this explains something about dataset.Edit, to set the mode. But i don't even have a dataset, should i?
I actually meant the Lazarus wiki pages, not pages meant for a competing product ;) though those can often help too. As I said the using tsqlquery article and the links it contains should clear things up.

I'm also using new non-root user for the connection, and it's also tested to have listing and update rights.
Ok.

why not do something like this - air code, written in this post, untested, but hope you get the idea:
Code: [Select]
try
  query.sql.txt:='update player set active=1 where id=:playerid '; //using parametrized queries
  query.sql.txt.params.parambyname('playerid'):=game.myid; //assign your variable to the query parameter called playerid which we defined in the previous line
  query.execute; //now just run it
  writeln('query '+query.sql.txt+' executed ok.'); //assuming console mode program so you can use writeln
except
  on E: EMySQLDBError do //no idea what the identifier for the mysql specific error object is. Please look it up or use the generic one (EDatabaseError, by heart)
  begin
    writeln('query '+query.sql.txt+' failed. Error code '+EMySQLDBerror.message); //emysqldberror.message if it exists or whatever other properties that can be handy
  end;
end;

We haven't yet touched on transaction management; I suspect the mysql sqldb driver does autocommits so this subject can be left for now. The code above (once corrected) will at least show you what goes wrong exactly.
If things do go wrong, try running the query (with the parameter filled in) in a mysql query tool. Looking at it, there doesn't seem anything wrong with it at all though.
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

User137

  • Hero Member
  • *****
  • Posts: 1791
    • Nxpascal home
Re: MySQL 5.5 simple questions
« Reply #5 on: October 16, 2012, 10:23:50 am »
Code: [Select]
  //query.sql.txt.params.parambyname('playerid'):=game.myid;
  // Syntax was a little off, but close :D
  query.params.FindParam('playerid').Text:=inttostr(game.myid);

  //query.execute; //now just run it
  // There is also ExecuteAction which takes TBasicAction as parameter, but no Execute
  query.ExecSQL;

Long story short, it works now after i did
Code: [Select]
dbtransaction.Commit;Found it from there: http://wiki.freepascal.org/Working_With_TSQLQuery

All attempts we did before were propably right, but it only did the changes to some sort of local instance of the database. Like a cache? Because if i did a select query after the update, i got the modified value. Yet that change wasn't in the real database.

taazz

  • Hero Member
  • *****
  • Posts: 5365
Re: MySQL 5.5 simple questions
« Reply #6 on: October 16, 2012, 10:57:42 am »
Actually if you run a select and returns the new value then it is in the real database.

there is no local cache besides the query which is flashed to the database when you call applyupdates.
BUT, not committing a transactions means that when that transaction is  closed eg destroyed/freed it automatically rolls back all the changes you have made if you don't commit it your self.

That's the default behavior as far as I can see and I do not know if it can be changed.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

User137

  • Hero Member
  • *****
  • Posts: 1791
    • Nxpascal home
Re: MySQL 5.5 simple questions
« Reply #7 on: October 16, 2012, 01:33:06 pm »
As i was typing more rants on things not working, i managed to completely get them work  :D
I had uploaded the main program code here already (it's not enough to compile, and you would need SVN version of lazarus and fpc 2.7.1, but all database related code is there):
http://pastebin.com/W7WXa2Cn
You can see in line 132 onwards, i needed to use query.RecordCount instead.

Then changed MySQL engine type to "memory", so it should keep that small needed data in RAM. The purpose was to try replace normal TCP/IP communication, test if it's possible to do a game with just database. So far on my own screen i can see 4 game windows with 4 ships on each screen, movements perfectly in sync... Have to try that through internet. Added bonus is that whatever client, written with whatever language, that knows how to connect to the MySQL database, can join the game.

But it brings another problem for sequrity things, would be nice for user to not have access to edit other rows than his own. Maybe it needs new "admin software" and another table that acts as receiving data. Admin-software would read the updates and add them to players values. Users would insert new rows to the updates table only, and admin deletes them after reading.

In other words, real TCP/IP-communication without database would be more efficient, but not as reliable. I already have 1 socket version of it, but full of bugs. I was able to connect it with my US friend, to EU, but it only works sometimes... go figure, i guess bad weather, or there was a whale too near the Atlantic cable! (there is cable right?)

taazz

  • Hero Member
  • *****
  • Posts: 5365
Re: MySQL 5.5 simple questions
« Reply #8 on: October 16, 2012, 02:20:42 pm »
Wow a bunch of stuff to comment on, let me try the most interesting for now. Security.

This needs a bit of work from your part but you can have a perfectly secure database using only sql features. This requires that each user of your program must be a user of the sql server as well.
That is not enough though so in order to make sure that every user can see what ever he is allowed to you restrict access to the tables only to objects inside the database eg view and stored procedures,
no one except the administrator has direct access to the tables everything goes through the views and procedures. Each view has read only access to the tables it requires to function properly and each stored procedure has only write access to the tables it requires.
you can have hidden field named accesslvl for example and a users table that has each users access level and use that as a measure of what or what is not allowed. taking as an example the *nix access levels root has an access level of 0 (zero) and each user gets a level higher than that starting from 100 or something. This way you can add more restricted levels as your application grows and you will never run out of levels.

after everything is in place will have a very strict and secure database.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

 

TinyPortal © 2005-2018