Hot answers tagged jdbc
4
Use a subquery (as displayed) or CTE for that purpose:
SELECT *
FROM (
SELECT qid, gid
FROM table1
ORDER BY date DESC
LIMIT 10
OFFSET ?
) q
JOIN table2 a USING (qid, gid)
USING (qid, gid) is just a shortcut for ON q.qid = a.qid AND q.gid = a.gid with the side effect that the two columns are only included once in the result.
3
From the Connection.setAutoCommit docs:
NOTE: If this method is called during a transaction and the auto-commit mode is changed, the transaction is committed. If setAutoCommit is called and the auto-commit mode is not changed, the call is a no-op.
But I don't think it's very readable/obvious in your code. You should probably simply commit before ...
3
Looks like we found the culprit. Following the recent app server upgrade, we inadvertently included both ojdbc14 and ojdbc6 jars into our deployment, and evidently, the jvm picked up ojdbc14 for its Oracle DB driver. Since we removed ojdbc14 manually, this problem hasn't come up again in the past 24 hours. I assume ojdbc14 is no longer officially supported, ...
2
Consider a parent/child table such as client/order.
You can't delete a client that has an order. Say client 123 has an order A123. Fred does a delete for that order but does not commit. Then "Jane" tries to delete client 123.
Since Fred's statement can potentially rollback, the client can't be deleted because it isn't allowed to the leave the order ...
2
Delete is a DML command and stores the data in redo log till the delete operation is committed.
This means that if data to be removed by delete is slightly large[even though search time is less] it will take longer time as it will move data to redo log.
So may be the instance when your operation took longer large no. of rows were being deleted to many ...
2
For Oracle, this seems like a good sneaky way of catching COMMITs:
http://stackoverflow.com/a/6463800/790702
What he doesn't mention is that you should be able to catch the constraint violation in your code too, to stop the 2nd situation occurring.
2
The parentheses form a row-constructor, so your query returns a single column row literal, essentially an anonymous composite type.
Compare:
regress=> SELECT (1,2);
row
-------
(1,2)
(1 row)
regress=> SELECT 1, 2;
?column? | ?column?
----------+----------
1 | 2
(1 row)
You would've quickly realised this if you'd run the query ...
2
You can get most of those messages, but unfortunately not all. See my question on Stackoverflow regarding that.
In general those messages (e.g. messages from a PRINT statement) are returned as warnings on the Statement object by the JDBC driver. To retrieve them use Statement.getWarnings() in a loop:
Statement stmt = ...;
stmt.execute("some sql");
...
1
You should be just fine extending wait_timeout
Notice the maximum value for wait_timeout for MySQL 5.0, 5.1, 5.5, 5.6
Linux : 31536000 seconds (365 days, one year)
Windows : 2147483 seconds (2^31 milliseconds, 24 days 20 hours 31 min 23 seconds)
These maximums would not exist of mysqld could not handle them.
Connection pooling only saves on overhead in ...
1
This could happen if:
- Your table MYTABLE has a unique column UNIQCOL
- This MYTABLE.UNIQCOL is referenced by some column in another table, say MYTABLE2.UNIQCOL_REF
- This MYTABLE2.UNIQCOL_REF is not indexed.
Adding a (non-unique) index to MYTABLE2.UNIQCOL_REF could then solve the problem.
(You said all FKs in MYTABLE are indexed, but you didn't say ...
1
Looking at the Stored Procedure, I see something rather unnatural.
DELIMITER $$
DROP PROCEDURE IF EXISTS `adam_matan`.`AddPixel` $$
CREATE PROCEDURE `adam_matan`.`AddPixel`
(
GivenType VARCHAR(20),
GivenPixelData BLOB
)
TheStoredProcedure:BEGIN
DECLARE KeepPixels,DeleteLimit,MaxID INT;
SET KeepPixels = 5;
SET DeleteLimit = 100;
INSERT ...
Only top voted, non community-wiki answers of a minimum length are eligible
