Paul DuBois wrote:
>
> At 15:23 -0400 5/13/02, Amer Neely wrote:
> > > Amer,
> >> Monday, May 13, 2002, 2:03:28 AM, you wrote:
> >>
> >> AN> Win/98
> >> AN> MySQL 3.23.46
> >>
> >> AN> I'm trying to use AUTO_INCREMENT=1000 to specify my staring value in an
> >> AN> ID column in batch mode, but it doesn't want to work. I can get it to
> >> AN> work in interactive mode though.
> >>
> >> AN> In a file (create_tables.sql) I have:
> >>
> >> AN> CREATE TABLE Respondents (UserID SMALLINT UNSIGNED AUTO_INCREMENT NOT
> >> AN> NULL PRIMARY KEY,
> >> AN> UserLastName VARCHAR(25) NOT NULL,
> >> AN> UserFirstName VARCHAR(20) NOT NULL,
> >> AN> UserEmail VARCHAR(60) NOT NULL,
> >> AN> UserPhoneAC CHAR(3) NOT NULL,
> >> AN> UserPhoneNum VARCHAR(8) NOT NULL,
> >> AN> UserPhoneExt VARCHAR(5),
> >> AN> UserLevel VARCHAR(20) NOT NULL,
> >> AN> UserFoundBy VARCHAR(40) NOT NULL,
> >> AN> KeyDM ENUM('Y','N') NOT NULL,
> >> AN> DMPositionTitle VARCHAR(30) NOT NULL,
> >> AN> IPAddr VARCHAR(15) NOT NULL,
> >> AN> IPName VARCHAR(100))
> >> AN> AUTO_INCREMENT=1000;
> >>
> >> AN> which I am then loading into mysql like this:
> >>
> >> AN> c:\mysql\data\mydb mysql mydb < create_tables.sql
> >>
> >> AN> No errors, but when I populate the table with values (also from a file),
> >> AN> the values for UserID start at 1, not 1000.
> >>
> >> I guess that your UserID in the file begins from 1, not from 1000. You
> >> can get values that are starting from 1000, if you insert into column
> >> NULL or 0 or if you insert values from 1000 manually :)
> >
> >Yes, I understand that, but I'm trying to create the table and
> >initialize UserID by redirecting a .sql file into mysql (batch mode). I
> >don't want to insert a 'dummy' record starting at 1000, I want MySQL to
> >do that, like it says in "MySQL" by Paul DuBois [p.94, chap.2.]. Again,
> >this works in interactive mode, but NOT in batch mode. Can you or
> >someone explain how this can be done in batch mode?
> >
> >> AN> The values I'm inserting for
> >> AN> UserID are all 'null' (without quotes).
> >>
> >> How did you inserted values? Manually or from a file?
> >
> >As I indicated above, from a file.
>
> Let's see a sample of the file. Without that, we're just guessing.
> mysql won't interpret INSERT statements differently in batch mode
> than in interactive mode.
Hmm. I'm not using INSERT, but LOAD DATA. Maybe that's the culprit?
Contents of 'create_tables.sql':
CREATE TABLE Respondents (UserID SMALLINT UNSIGNED NOT NULL
AUTO_INCREMENT PRIMARY KEY,
UserLastName VARCHAR(25) NOT NULL,
UserFirstName VARCHAR(20) NOT NULL,
UserEmail VARCHAR(60) NOT NULL,
UserPhoneAC CHAR(3) NOT NULL,
UserPhoneNum VARCHAR(8) NOT NULL,
UserPhoneExt VARCHAR(5),
UserLevel VARCHAR(20) NOT NULL,
UserFoundBy VARCHAR(40) NOT NULL,
KeyDM ENUM('Y','N') NOT NULL,
DMPositionTitle VARCHAR(30) NOT NULL,
IPAddr VARCHAR(15) NOT NULL,
IPName VARCHAR(100)
)
AUTO_INCREMENT=1000;
---------------------------------------------------------
Contents of 'populate.sql':
DELETE FROM Respondents;
LOAD DATA LOCAL INFILE "respondents.txt" INTO TABLE Respondents;
--------------------------------------------------------------
Contents of respondents.txt:
NULL Neely Amer [EMAIL PROTECTED] 519 438.5887 Owner
Business card
Y Owner 127.0.0.1 localhost
NULL Silver John [EMAIL PROTECTED] 000 000-0000 Owner
Referral Y CEO
209.84.23.19 somewhere.ca
NULL Day Dennis [EMAIL PROTECTED] 123 987-6543
President Search
engine N 200.100.50.25 somewhereelse.com
NULL Marlatt Ed [EMAIL PROTECTED] 789 123-0789 Mid-Level
Search engine N
209.187.29.12 here.com
NULL Curiale L. [EMAIL PROTECTED] 987 456-3210 CEO
Promotional
Material N 189.2.100.1 there.org
NULL Butler Robert S. [EMAIL PROTECTED] 456 987-0123
Board
Referral Y President 209.87.34.1 everywhere.ca
NULL Hobbes Calvin [EMAIL PROTECTED] 654 951-7562 CHO
Referral Y
Manager 209.43.89.100 hobbes.com
---------------------------------------------------
NB: respondents.txt file is tab-delimited, ending with CRLF
I load them thus:
c:\mysql\data\mydb mysql mydb < create_tables.sql
c:\mysql\data\mydb mysql mydb < populate.sql
When I do a 'select userid from respondents' I get 1..7, not 1000..1006
Eventually I need to have this generated by a Perl script.
--
/* All outgoing email scanned by Norton Antivirus 2002 */
Amer Neely, Softouch Information Services
W: www.softouch.on.ca
E: [EMAIL PROTECTED]
V: 519.438.5887
Perl | PHP | MySQL | CGI programming for shopping carts, data entry
forms.
"We make web sites work!"
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
