Page 1 of 2
DB connection Manager
Posted: 13 Apr 2011, 10:33
by 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
Or is there a way to use the pool connections to do the same?
Thank you.
Posted: 15 Apr 2011, 06:24
by 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
Posted: 15 Apr 2011, 09:40
by Sigtran
Thanks, Lapo
Posted: 24 Jan 2012, 12:17
by 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
Posted: 24 Jan 2012, 12:28
by 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());
}
Posted: 24 Jan 2012, 13:38
by hellopaso
thanks Sigtran!
seems to work fine
Posted: 24 Jan 2012, 14:56
by 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?
Posted: 24 Jan 2012, 15:04
by Sigtran
ehm.. yes, if you do SELECT first?
Posted: 24 Jan 2012, 15:51
by 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.
Posted: 24 Jan 2012, 16:04
by 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());
}
Posted: 24 Jan 2012, 16:30
by hellopaso
no, its not what I wanted
I was just thinking whether its possible to get last id
WITHOUT sending another request.
Posted: 24 Jan 2012, 16:39
by 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
Posted: 24 Jan 2012, 22:17
by 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?
Posted: 24 Jan 2012, 22:52
by 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.
Posted: 24 Jan 2012, 23:05
by 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 ^^