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.
>
>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.
---------------------------------------------------------------------
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
