DB connection Manager

[Sigtran]

Hi, a quick question:
Does embedded connection manager (IDBManager) has an SQL wrapper (e.g. START TRANSACTION; COMMIT; / etc?), or do I need to write all my MySQL statements properly (and use java Connector instead of IDBManager), to make sure that there no inconsistencies?

EDIT:
Ill clarify further: I have an ingame system, where a user is buying items from the shop. In this system I need to do at least two updates: take the money off the user and add the item to user's inventory. To do it safely I need to use transactions, but the default JDBC config is to commit after each statement. This is not acceptable, as if one of the statements fails, then either the user wont have his item and loose his money, or other way around (whichever one of the statements fails). Am I better off using the default Connection where I can

Code: Select all

setAutoCommit(false)

Or is there a way to use the pool connections to do the same?

Thank you.

[Lapo]

Am I better off using the default Connection where I can

Yes use the connection or in alternative use a higher level tool such as Hibernate

[Sigtran]

Thanks, Lapo

[hellopaso]

hello,

I have the same problem, I have to use transactions
to connect to mysql

would you be so kind and let me know where I can find some information
about sfs & transactions?


regards
g

[Sigtran]

hellopaso,

There no transactions with sfs, use some 3ed party, if you want the connection pool, or use the connector if you can afford to recreate the connection... generally you only need it for updates / bulk insert, so shouldnt be used too often imho.

Im just using this:

Code: Select all

try
{
   connection = dbManager.getConnection();
   connection.setAutoCommit(false);
   st = connection.createStatement();
   
   st = connection.createStatement();
   String sql = "UPDATE lalala;";
   st.executeUpdate(sql);
   st.close();
   
   
   st = connection.createStatement();
   sql = "UPDATE tralala;";
   st.executeUpdate(sql);
   st.close();//
   
   connection.commit();
   connection.close();
}
catch (SQLException e)
{
   try
   {
      connection.rollback();
      connection.close();
   }
   catch (Exception ex)
   {
      //trace(ExtensionLogLevel.WARN, "SQL Failed: " + e.toString());
   }
   trace(ExtensionLogLevel.WARN, "SQL Failed: " + e.toString());
}

[hellopaso]

thanks Sigtran!

seems to work fine

[hellopaso]

but one simple question,

my request to DB is:

Code: Select all

st = connection.createStatement();
sql = "INSERT INTO myTable (id, plot_id) VALUES (LAST_INSERT_ID(), 1)";
         
st.executeUpdate(sql);
st.close();
         
connection.commit();
 connection.close();
 

everything workds fine, but as you see, I do not know what ID will be attached to that record
(cause it will be taken from LAST_INSERT_ID())


is its possible that my connection could return that ID?

[Sigtran]

ehm.. yes, if you do SELECT first?

[hellopaso]

sorry but I have no experience with mysql, what SELECT?


let say I have 10 records (their ID`s are from 1 to 10)

so when I add another record (using Connection) it will get next available ID - this ID will be 11.

but after request is done, I want to be informed about this ID.

[Sigtran]

Ok, here is an example of what you should be doing and how to send it back to the client:

Code: Select all

String sql = "SELECT ID FROM MyTable ORDER BY ID DESC LIMIT 1";

try
{
   // Obtain a resultset
   ISFSArray result = dbManager.executeQuery(sql);
   
   // Populate the response parameters
   ISFSObject response = new SFSObject();
   response.putSFSArray("ID", result);
   
   // Send back to requester
   send("getQuest", response, theUser);
}
catch (SQLException e)
{
   trace(ExtensionLogLevel.WARN, "SQL Failed: " + e.toString());
}

[hellopaso]

no, its not what I wanted

I was just thinking whether its possible to get last id
WITHOUT sending another request.

[Sigtran]

no, more then likely its not possible, as your id is set to autoincrement, so you are just sending an sql query to add another record, thus while sql knows what it needs to add, your java server doesnt know what LAST_INSERT_ID() is... if that makes sense... and unless you know what LAST_INSERT_ID() is, you cant return it

[hellopaso]

you know, the problem is, that immediately after request to mysql is done,
I wanna create some Object with that ID.

and sending another request (SELECT) just to find out what is the last ID is little bit pointless


sorry for dummy question, cause as I said I have no knowladge about MYSQL, but is it really not possible that MYSQL could send me back some info?

[rjgtav]

Hi. Instead of calling all the time the database for getting the id, if you're editing the database only with the extensions, you could store the id in a static var (when the extension inited) and then you could use that value instead of always retrieving the last id.

[Sigtran]

Update doesnt return anything from sql, so in short, no, you cant have the object that was just created in the database and sfs has no knowledge of, but as rjgtav suggested - you should probably redesign the way you are storing / distributing user data (i.e. if its not persistent data, you should probably just store it in memory, if its data that need be collected at login time, then it should only be read once, then when the change occurs, it should be written to the db once, and the same new data should overwrite the memory of the older data, i.e. you have users' money read from the db once at logon, then user's money count changes, you have to decide whether you want to write it at change time, or do you just want to sync the value in the db and memory once in a while, BUT you should not read the money value from the DB again. SFS2x database connector tutorial & whole game tutorial are the way to go. check them out ^^