When does grant statement take into effect

In both Sql Server and Oracle, permission changes to a user take into effect right away, even when said user is connected at the time you made the change.

In MySql, it is a little different, depending on how the permissions are given. If you use the GRANT statement, then it takes into effect right away. However, if you create user and give it permissions by manipulating the user table in the mysql system database directly, that is, using Sql statements, then you need to issue:

flush privileges

for those changes to be picked up.

Executing sql scripts using command line tools

SQL Server 2005 has a command line tool named sqlcmd. MySQL has a command line tool named mysql. Oracle has a command line tool called sqlplus. They can all be used for interactive query processing and batch scripts processing. They do similar things, albeit in different ways. They are functionally equivalent.

For SQL Server 2005, when in interactive mode of sqlcmd, use

:r c:\MyFolder\MyScript.sql

to read and execute a script file. You may have to type


afterwards, if the last line of the script file does not end with the word go.

To use sqlcmd in batch mode, that is, to run a sql script and then get out, use:

sqlcmd -i c:\MyFolder\MyScript.sql -S MyServerName -E

Replace -E with -U LoginName if you use Sql authentication

For MySQL, while in interactive mode of mysql, use

\. c:\MyFolder\MyScript.sql (on Windows)

Note there should be a backward slash right before the dot. In fact, please pay attention to all backward slashes in Windows directory names. Or

source ~/MyFolder/MyScript.sql (on *nix)

to run a script file.
If you want to save a query result from mysql command line to a file, use

SELECT columnName FROM tableName INTO OUTFILE '/tmp/somefile.txt'

mysql -h MyHost -u MyUser -t -p < ~/MyFolder/MyScript.sql The -t is not necessary here, but it presents the results in table/grid format, therefore more readable. You may need to add the -D (or –database) parameter. In Oracle sqlplus, while in interactive mode, use

start c:\MyFolder\MyScript.sql (on Windows)


@ ~./MyFolder/MyScript.sql (on *nix)

Note that if the script file name has the .sql extension at the end, it is not necessary to type .sql. sqlplus assumes the .sql extension.

For batch mode, make sure you have exit as the last line of the script file, and run this

sqlplus MyUser@OracleSID @c:\MyFolder\MyScript.sql