How do I store the output of a sql query into a variable in a batch script?
I am working on a script package.
I want to store the number of lines in a variable.
as
set var = mysql -uroot -proot -e "select count (*) from table";
I also tried to do it differently like
set var= mysql -uroot -proot -e "select count(*) from table into outfile 'F:\count.txt'";
for /f %%a in ("F:\count.txt") do (
set output = %%a
echo %output%
pause
)
In the above code, the variable "output" does not show anything (empty).
please help me.
source to share
I see at least two problems in your script:
-
The double-quoted string is internally
IN( )
treated as a literal and not a file path / name, unless you specify an optionusebackq
that applies a different semantics where either a double-quoted string or a non-quoted one is treated as a filename. -
You are storing
<space>%%a
in a variableoutput<space>
, not%%a
inoutput
.
Once you've fixed those two, there will be one (maybe only one) problem. You assign a value to a variable and then evaluate the variable in the same parenthesized block (which is your loop body) using immediate variable expansion ( %var%
). This may not work as expected. The fact is that a block enclosed in a square is disassembled completely as a whole, that is, all its commands are analyzed before the first is executed. As you might guess, your expression %output%
will evaluate to nothing in this case, because output
no value has been assigned yet during parsing. (And when a value is assigned to it, it won't change anything, because the previous (empty) value has already replaced the expression.)
You can solve this by using delayed variable expansion, which, as you might guess, uses a different time to evaluate. First, you must enable slow expansion by issuing a command SETLOCAL EnableDelayedExpansion
, then use a slightly different syntax: !var!
instead of %var%
.
So, if we look at all the problems mentioned above, the loop might look like this:
…
SETLOCAL EnableDelayedExpansion
FOR /F "usebackq" IN ("F:\count.txt") DO (
SET output=%%a
ECHO !output!
)
source to share
You just define var
with content mysql -uroot ...
, but you don't!
Therefore, there should be no file F:\count.txt
.
Your syntax is not valid, remove the spaces (from set output = %%a
), otherwise you will create a variable output<space>
insteadoutput
Your code might look like
mysql -uroot -proot -e "select count(*) from table into outfile 'F:\count.txt'";
setlocal EnableDelayedExpansion
for /f "delims=" %%a in (F:\count.txt) do (
set "output=%%a"
echo !output%!
)
source to share
You are on the right track, but the FOR / F command you are showing is not the correct syntax - it will give an error (I ignore the IN () clause because I know it is intentionally incomplete).
The parameter "tokens="
is incomplete and must be followed by at least one number or asterisk - see the documentation for more information (enter HELP FOR
from the command line). In your case, you don't need the token parameter "delims="
- that's all you need.
There must be at least one space between DO
and (
.
If you make these fixes and complete the IN () clause, it should work. If it isn't, then there might be something wrong with your IN () clause. You should post the entire team if you need help diagnosing a problem.
source to share