Paul DuBois wrote:
>
> At 17:41 -0400 5/13/02, Amer Neely wrote:
> >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?
>
> Maybe. In data files loaded with LOAD DATA, NULL values should be specified
> as \N, not as the word NULL.
> I'm not sure that that will make a difference, though. MySQL will see NULL,
> perform a string-to-number conversion and end up with a value of zero.
> And inserting 0 into an AUTO_INCREMENT column should be the same as inserting
> NULL. Still, it'd be worth a try to convert NULL to \N and see what happens.
>
> This might be interacting with another problem, which is that if your
> data file likes are CRLF terminated, *you need to say so*. The default
> is LF-terminated, so your LOAD DATA statement needs to have a
>
> LINES TERMINATED BY '\r\n'
>
> clause at the end.
Tried both suggestions - still getting 1..7 as UserID.
>
> >
> >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
