Re: Going from Oracle to Mysql

From: Ralph Snart <snart_at_nospam.com>
Date: Mon, 26 Aug 2002 20:10:18 GMT
Message-Id: <slrnaml2p7.od.snart@cluttered.com>

>> > Now, in Oracle, I would use a a statement like the following in order
>> > to insert into a table with a sequence attached to it:
>> >
>> > insert into Emp values (Unik.nextval,?,?,?,?,?,?,?)
>> > (used for a preparedStatement in the database)
>> >
>> > What would the corresponding query look like in mysql??
>> >
>> > Also I would like to know what the following query looks like in mysql:
>> >
>> > select AddedEmp.currval from Emp
>> > (when the sequence in Oracle looks like
>> > create sequence AddedEmp increment by 1;)

>> What is going on here? Several questions about MySQL in the last few days.
>> Which part of the fact that the name of these usenet groups is
>> comp.databases.ORACLE is escaping you folks?
>>
>> 1. We don't know.
>> 2. We don't care.
>> 3. You made the choice to use a product lacking in support, lacking in
>> documentation, lacking in usenet groups. No us.

geez, if you don't know the answer, just keep quiet! i would say an oracle newsgroup is a good place to ask about how oracle features are manifested in other systems.

btw, mysql is extremely well supported. i use it and oracle extensively in my job. can't we all just get along?

to answer the OP, the mysql equivalent of oracle sequences:

if you have a mysql table with an AUTO_INCREMENT column, just don't insert into it, or insert null. it will automatically get a value.

to find out what value you got through pure sql do SELECT LAST_INSERT_ID(); there is also an api call for getting it that may be exposed depending on what language you are using. with perl, for example, it's stored in $dbh->{'mysql_insertid'}

mysql> create table test (id integer not null auto_increment primary key,

       name varchar(32));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into test (name) values ('ralph snart'); Query OK, 1 row affected (0.02 sec)

mysql> select last_insert_id();

+------------------+

| last_insert_id() |

+------------------+
|                1 |
+------------------+

1 row in set (0.02 sec)

mysql> insert into test values(null,'bogus'); Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----+-------------+
| id | name |
+----+-------------+
| 1 | ralph snart |
| 2 | bogus |
+----+-------------+
2 rows in set (0.00 sec)

mysql> select last_insert_id();

+------------------+

| last_insert_id() |

+------------------+
|                2 |
+------------------+

1 row in set (0.00 sec)

-rs- Received on Mon Aug 26 2002 - 15:10:18 CDT