Changing directory from which SOURCE reads SQL script files.

[John Connolly]

Hi all:

	Using script files is convenient because it allows storage of 
frequently used queries, and facilitates editing long queries without 
having to retype the whole thing.

	Something that would make script files more convenient is to change 
the directory from which SOURCE reads them.

	My databases (probably everyone's?) are located in the directory:

	/usr/local/mysql/

	It is in this directory that SOURCE looks for script files. Thus the 
command:

	mysql SOURCE home_runs.sql

	looks for the script file:

	/usr/local/mysql/home_runs.sql

	But often script files are located elsewhere, e.g., when downloaded 
from the web, or when created and stored elsewhere for organizational 
purposes. For the SOURCE command to access such scripts, the absolute 
path must be given, e.g.:

	mysql SOURCE /Users/jack/Desktop/Du_Bois_scripts/mortality.sql

	It would be nice to re-set the default directory to 
/Users/jack/Desktop/Du_Bois_scripts, so that one would  have only to 
type:

	mysql SOURCE mortality.sql

	to run the mortality.sql script that is located in 
/Users/jack/Desktop/Du_Bois_scripts.

	Is that somehow possible? Or is there a common workaround?

	I thought of putting a script in the default directory 
(/usr/local/mysql/) that in turn calls the desired script. I.e., put a 
script in /usr/local/mysql that contains only the line:

	SOURCE /Users/jack/Desktop/Du_Bois_scripts/mortality.sql

	But that requires fiddling with two scripts as one goes along, and I'm 
wondering whether there is a more straightforward solution.

	Also tried to use a user variable to supply the re-used portion of the 
directory path, but that just produced an error:

			mysql set @thing=scripts ;

			mysql source concat(@thing,/jacks/hr.sql);
			ERROR:
			Failed to open file 'concat(@thing,/jacks/hr.sql)', error: 2

	Finally, if indeed it is not possible to re-set the default 
directory, I'm wondering why? Is it a security thing? Just curious.

	Thanks all.

	Best,

	Jack Connolly
	Hanover, MA
	
	


	++
	| version()  |
	++
	| 3.23.52-entropy.ch |
	++

	. running under Mac OS 10.2 (Jaguar).

	

	

	
	


-
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

LOAD DATA LOCAL INFILE

[John Connolly]

	Hello all ---

	I posted this yesterday, perhaps before my subscription took effect, 
and so I am not sure that it got distributed. So, forgive the 
repetition in sending, if that's the case. Anyway . . .

	Here's the question, as succinctly as I can formulate it:


		The documentation seems to say that one can load a tab-delimited text 
file located in the current directory of the client host into a 
database by running the client mysql and executing the following 
command:

		mysql LOAD DATA LOCAL INFILE absence.txt into table absence;

	When absence.txt is located in 
/Users/jack/Desktop/SQL_stuff/samp_db/, then the command:

		mysql LOAD DATA LOCAL INFILE /Users/jack/Desktop/SQL_stuff/samp_db/ 
absence.txt into table absence;

	does work.

	But the command:

		mysql LOAD DATA LOCAL INFILE absence.txt into table absence;

	does not work. Rather, it produces the error:

		ERROR:
		File 'absence.txt' not found (Errcode: 2)

	That is, absolute pathnames work, but relative pathnames do not.

	I have been careful to be sure that, when invoking the mysql client, I 
am in the correct directory, and that when I invoke mysql, that I 
include the --local-infile option, as the following printout shows:

		[Jacks-G4:~/Desktop/SQL_stuff/samp_db] jack% mysql --local-infile 
samp_db

		Reading table information for completion of table and column names
		You can turn off this feature to get a quicker startup with -A

		Welcome to the MySQL monitor.  Commands end with ; or \g.
		Your MySQL connection id is 14 to server version: 3.23.52-entropy.ch

		Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

		mysql LOAD DATA LOCAL INFILE absence.txt into table absence;
		ERROR:
		File 'absence.txt' not found (Errcode: 2)

	I have concluded that one cannot use the relative pathname of a file 
in the LOAD DATA LOCAL INFILE command. I hope I am wrong, as it would 
be nice to use the simpler, relative, pathname.

	With thanks for your replies, am I wrong or right? If wrong, what am I 
missing?


	Jack Connolly
	Hanover, MA
	[EMAIL PROTECTED]
	
	Running, on Mac OS 10.2, server and client on same machine:
	++
	| version()  |
	++
	| 3.23.52-entropy.ch |
	++



-
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

LOAD DATA LOCAL INFILE

[John Connolly]

Victoria and Michael:

	Thanks very much.

	Following on your suggestions, I then went ahead and put the text file 
into my directory:

	/usr/local/mysql

	so that the file was then /usr/local/mysql/absence.txt

	and, with that done, the

	LOAD DATA LOCAL INFILE absence.txt into table absence;

	command in the mysql client successfully did load it.

	(It didn't work when put into the /usr/local/mysql/scripts directory 
or in the /usr/local/mysql/data directory.)

	Is there anyway to change the place where the client looks when given 
the relative pathname of a file to be LOADed, or is one stuck with 
having to put all text files in that one immutable location?

	(On the Mac, we aren't allowed to see the contents of 
/usr/local/mysql/ in the GUI. We have to access it through the UNIX 
Terminal program, and then fiddle with permissions, which, I am not 
bashful to say, I am reluctant to start doing without comprehending the 
implications, which, I also am not bashful to say, I don't.)

	Thanks again.

	Best,
	
	Jack Connolly
	Hanover, MA

	P.S. -- Is this how I am supposed to reply to responses, i.e., by 
directing them back to the list, as opposed to the individual?

	Thnx,

	J.

On Thursday, November 28, 2002, at 09:23 AM, Michael Ryan wrote:

The MySQL Manual states the following in the LOAD DATA INFILE section 
:-

If a filename with no leading components is given, the server looks 
for the file in the database directory of the current database.


On Thursday, November 28, 2002, at 10:42 AM, Victoria Reznichenko wrote:


John,

Thursday, November 28, 2002, 2:12:27 AM, you wrote:

JC Here's the question, as succinctly as I can 
formulate it:


JC The documentation seems to say that one can load a 
tab-delimited text
JC file located in the current directory of the client host into a
JC database by running the client mysql and executing the following
JC command:

JC mysql LOAD DATA LOCAL INFILE absence.txt into 
table absence;

JC When absence.txt is located in
JC /Users/jack/Desktop/SQL_stuff/samp_db/, then the command:

JC mysql LOAD DATA LOCAL INFILE 
/Users/jack/Desktop/SQL_stuff/samp_db/
JC absence.txt into table absence;

JC does work.

JC But the command:

JC mysql LOAD DATA LOCAL INFILE absence.txt into 
table absence;

JC does not work. Rather, it produces the error:

JC ERROR:
JC File 'absence.txt' not found (Errcode: 2)

JC That is, absolute pathnames work, but relative pathnames 
do not.

JC I have been careful to be sure that, when invoking the 
mysql client, I
JC am in the correct directory, and that when I invoke mysql, that I
JC include the --local-infile option, as the following printout shows:

JC [Jacks-G4:~/Desktop/SQL_stuff/samp_db] jack% mysql 
--local-infile
JC samp_db

JC Reading table information for completion of table 
and column names
JC You can turn off this feature to get a quicker 
startup with -A

JC Welcome to the MySQL monitor.  Commands end with ; 
or \g.
JC Your MySQL connection id is 14 to server version: 
3.23.52-entropy.ch

JC Type 'help;' or '\h' for help. Type '\c' to clear 
the buffer.

JC mysql LOAD DATA LOCAL INFILE absence.txt into 
table absence;
JC ERROR:
JC File 'absence.txt' not found (Errcode: 2)

JC I have concluded that one cannot use the relative pathname 
of a file
JC in the LOAD DATA LOCAL INFILE command. I hope I am wrong, as it 
would
JC be nice to use the simpler, relative, pathname.

JC Am I wrong or right? If wrong, what am I missing?

If you don't use absolute pathname, your file must be located in the
client program/script directory (in your case mysql client program)



--
For technical support contracts, goto 
https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com





-
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



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

LOAD DATA LOCAL INFILE

[John Connolly]

		Here's the question, as succinctly as I can formulate it:


		The documentation seems to say that one can load a tab-delimited text 
file located in the current directory of the client host into a 
database by running the client mysql and executing the following 
command:

		mysql LOAD DATA LOCAL INFILE absence.txt into table absence;

	When absence.txt is located in 
/Users/jack/Desktop/SQL_stuff/samp_db/, then the command:

		mysql LOAD DATA LOCAL INFILE /Users/jack/Desktop/SQL_stuff/samp_db/ 
absence.txt into table absence;

	does work.

	But the command:

		mysql LOAD DATA LOCAL INFILE absence.txt into table absence;

	does not work. Rather, it produces the error:

		ERROR:
		File 'absence.txt' not found (Errcode: 2)

	That is, absolute pathnames work, but relative pathnames do not.

	I have been careful to be sure that, when invoking the mysql client, I 
am in the correct directory, and that when I invoke mysql, that I 
include the --local-infile option, as the following printout shows:

		[Jacks-G4:~/Desktop/SQL_stuff/samp_db] jack% mysql --local-infile 
samp_db

		Reading table information for completion of table and column names
		You can turn off this feature to get a quicker startup with -A

		Welcome to the MySQL monitor.  Commands end with ; or \g.
		Your MySQL connection id is 14 to server version: 3.23.52-entropy.ch

		Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

		mysql LOAD DATA LOCAL INFILE absence.txt into table absence;
		ERROR:
		File 'absence.txt' not found (Errcode: 2)

	I have concluded that one cannot use the relative pathname of a file 
in the LOAD DATA LOCAL INFILE command. I hope I am wrong, as it would 
be nice to use the simpler, relative, pathname.

	Am I wrong or right? If wrong, what am I missing?


	Jack Connolly
	Hanover, MA
	[EMAIL PROTECTED]


-
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