Pass parameter from Batch file to MYSQL script

1
votes

I'm having a really hard time believing this question has never been asked before, it MUST be! I'm working on a batch file that needs to run some sql commands. All tutorials explaining this DO NOT WORK (referring to this link:Pass parameters to sql script that someone will undoubtedly mention)! I've tried other posts on this site verbatim and still nothing is working.

The way I see it, there are two ways I can approach this: 1. Either figure out how to call my basic MYSQL script and specify a parameter or.. 2. Find an equivalent "USE ;" command that works in batch

My Batch file so far:

:START
@ECHO off

:Set_User
set usrCode = 0

mysql -u root SET @usrCode = '0'; \. caller.sql

Simply put, I want to pass 'usrCode' to my MYSQL script 'caller.sql' which looks like this:

USE `my_db`;
CALL collect_mismatch(@usrCode);

I know that procedures are a whole other topic to get into, but assume that the procedure is working just fine. I just can't get my parameter from Batch to MYSQL.

Ideally I would like to have the 'USE' & 'CALL' commands in my Batch file, but I can't find anything that let's me select a database in Batch before CALLing my procedure. That's when I tried the above link which boasts a simple command line entry and you're off to the races, but it isn't the case.

Any help would be greatly appreciated.

1
mysqlwindowsbatch-fileparameters
Remove spaces in your SET Batch command: set usrCode=0 – Aacini
Haha, your comment ended up helping me out in the end. I tried everything in the answer below and totally forgot about the Batch variable rules. Thank you! – Marc Bosse

1 Answers

3
votes

This will work;

echo SET @usrCode = '0'; > params.sql
type params.sql caller.sql | mysql -u root dbname