-1

I'm trying to get the equivalent for this code on MySQL

try(Connection c = DriverManager.getConnection("jdbc:oracle:thin:@myhost:1521:"+ vardbserver, vardbuser, vardbpassword);
      PreparedStatement stmts = c.prepareStatement("SELECT * FROM "+ vardbname +" where ROWNUM > ? and ROWNUM < ? ");
      PreparedStatement max = c.prepareStatement("select max(ROWNUM) as txid from "+ vardbname)
      )

I've looking for the sample but i got a dead end.

NWD
  • 77
  • 2
  • 10
  • What exactly are you looking for? – beastlyCoder May 08 '17 at 03:01
  • I believe that this question has already been answered here: http://stackoverflow.com/questions/2728413/equivalent-of-oracle-s-rowid-in-mysql – Edmon May 08 '17 at 03:04
  • @Aaron im trying to get the data inside a column, but it'll continuously getting the data when the database updated... – NWD May 08 '17 at 03:06

1 Answers1

1
SELECT * FROM tablename where ROWNUM > ? and ROWNUM < ?

ROWNUM is used to select a subset of rows, i.e. rows between the two values (exclusive). First row of the query is numbered 1, second row is 2, and so on.

MySQL supports the LIMIT syntax, where you instead specify how many rows you want, and which row to get first:

SELECT * FROM tablename LIMIT offset, row_count

or

SELECT * FROM tablename LIMIT row_count OFFSET offset

An offset of 0 returns the first row.

So, you can calculate the two values for MySQL from the two values you had in Oracle. I'll leave that calculation up to you.

select max(ROWNUM) as txid from tablename

Well, I've never seen this one before, but if the query returned 10 rows, the max ROWNUM would be 10, so it is essentially the same as:

select count(*) as txid from tablename
Andreas
  • 154,647
  • 11
  • 152
  • 247
  • basically im trying to get the data inside a column, but it'll continuously getting the data when the database updated... i got the PostgreQSL code, and trying to get it on Oracle and MySQL... Here's the link https://gist.github.com/kejiro/bb8c7da5ee36a8bc447c0104208f5902#file-producerclear2-java-L66 – NWD May 08 '17 at 03:15
  • WOAH!!! That's a totally different thing. `xmin` (PostgreSQL) and `rownum` (Oracle) have nothing in common. Your question is not even about PostgreSQL. If you have a question about performing those PostgreSQL queries on Oracle and MySQL, you should ask *that* (as a new question), though I suspect that it cannot be done. I have answered *this* question. – Andreas May 08 '17 at 03:21
  • WEW... I've questioned here http://stackoverflow.com/questions/43535659/continuously-getting-content-from-table-database and didn't got the answer, so i tried to get the oracle's and asking the mysql's... :| – NWD May 08 '17 at 08:07