MySQL ODBC Connector - confused...

Author

confused...

Patrick Duda

2006-02-25, 9:43 am

Why, when I create a table as follows:

mysql> create table requestid ( request_id int not null default
1, constraint requestid_innodb_pk_
cons primary key(request_id) )
ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

Do I get the following?

mysql> select request_id from requestid;
Empty set (0.01 sec)

When I do a show create table I see:

mysql> show create table requestid;
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create
Table
|
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------+
| requestid | CREATE TABLE `requestid` (
`request_id` int(11) NOT NULL default '1',
PRIMARY KEY (`request_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Shouldn't I be getting back a '1' when I do my select??? Why am I getting
an empty set? What am I not understanding? How do I create a table with a
starting value of '1' or '0' for an int???

Thanks

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Hugh Sasse

2006-02-25, 9:43 am

On Tue, 21 Feb 2006, Patrick Duda wrote:

> Why, when I create a table as follows:
>
> mysql> create table requestid ( request_id int not null default 1,
> constraint requestid_innodb_pk_
cons primary key(request_id) ) ENGINE=InnoDB;
> Query OK, 0 rows affected (0.02 sec)

Defines the properties of an empty table....

The request id field for an inserted object will default to one if not
supplied. But the object must be supplied.

Hugh

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

SGreen@unimin.com

2006-02-25, 9:43 am

--=_alternative 006C7DCE8525711C_=
Content-Type: text/plain; charset="US-ASCII"

Patrick Duda <pduda@ncsa.uiuc.edu> wrote on 02/21/2006 02:39:47 PM:

> Why, when I create a table as follows:
>
> mysql> create table requestid ( request_id int not null default
> 1, constraint requestid_innodb_pk_
cons primary key(request_id) )
> ENGINE=InnoDB;
> Query OK, 0 rows affected (0.02 sec)
>
>
> Do I get the following?
>
> mysql> select request_id from requestid;
> Empty set (0.01 sec)
>
> When I do a show create table I see:
>
> mysql> show create table requestid;
> +-----------
>
+----------------------------------------------------------------------------------------------------------------------------------------------
> +
> | Table | Create
> Table
> |
> +-----------
>
+----------------------------------------------------------------------------------------------------------------------------------------------
> +
> | requestid | CREATE TABLE `requestid` (
> `request_id` int(11) NOT NULL default '1',
> PRIMARY KEY (`request_id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
> +-----------
>
+----------------------------------------------------------------------------------------------------------------------------------------------
> +
> 1 row in set (0.00 sec)
>
> Shouldn't I be getting back a '1' when I do my select??? Why am I
getting
> an empty set? What am I not understanding? How do I create a table
with a
> starting value of '1' or '0' for an int???
>
> Thanks
>

You haven't created any rows yet. That's why you get nothing back from
your SELECT query. With a single-column table like this, it will be
impossible to add a row to the table without providing a value for ID
(because it's the only column). You will never see the default value
because you must always supply one.

The term "starting value" in your original post implies that you intended
some sort of sequence. Did you want the server to automatically increment
the request_id value for you each time you add a record to this table? If
so, you have to do two things:

1) add more columns to this table
2) change the definition of your ID column to be an auto_increment column.

Here is an example of what your `request` table may look like

CREATE TABLE `request` (
id int not null auto_increment,
details varchar(50) not null,
tsModified timestamp,
PRIMARY KEY(id)
);

and you could add reqests to it like this:

INSERT `request`(`details`)
VALUES ('details of your first
request'),('details of a second request'), ('details of a third request');

Is it making any better sense?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

--=_alternative 006C7DCE8525711C_=--

Peter Brawley

2006-02-25, 9:43 am

Patrick,

>Shouldn't I be getting back a '1' when I do my select???
>Why am I getting an empty set?

First, creating a table doesn't add any rows. Show Create Table ...
returns a row of data definition info, not a row of table data.

Second, Defining the column as NOT NULL will require numeric input for
the column, so DEFAULT 1 will have no effect. What are you trying to
accomplish with DEFAULT 1?

PB

-----

Patrick Duda wrote:
> Why, when I create a table as follows:
>
> mysql> create table requestid ( request_id int not null default
> 1, constraint requestid_innodb_pk_
cons primary key(request_id) )
> ENGINE=InnoDB;
> Query OK, 0 rows affected (0.02 sec)
>
>
> Do I get the following?
>
> mysql> select request_id from requestid;
> Empty set (0.01 sec)
>
> When I do a show create table I see:
>
> mysql> show create table requestid;
> +-----------+----------------------------------------------------------------------------------------------------------------------------------------------+
>
> | Table | Create Table |
> +-----------+----------------------------------------------------------------------------------------------------------------------------------------------+
>
> | requestid | CREATE TABLE `requestid` (
> `request_id` int(11) NOT NULL default '1',
> PRIMARY KEY (`request_id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
> +-----------+----------------------------------------------------------------------------------------------------------------------------------------------+
>
> 1 row in set (0.00 sec)
>
> Shouldn't I be getting back a '1' when I do my select??? Why am I
> getting an empty set? What am I not understanding? How do I create a
> table with a starting value of '1' or '0' for an int???
>
> Thanks
>
>

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.11/264 - Release Date: 2/17/2006

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links