I have a script that I use to construct both the tables and stored procedures. For example I have a column of type varchar
. varchar
requires a size parameter, that size I also use as parameters in stored procedures and within those procedures.
is it possible to have thequivalentnt of a #define
for its size, so I can easily adjust the size without the necessity of having to change ithroughht the whole of the script?
I am using MySql workbench.
EDIT
I have tried SET
and DECLARE
I have a script - this is (abridged)
CREATE TABLE `locations`
(
`location` VARCHAR(25) NOT NULL
);
...
CREATE PROCEDURE AddLocation (IN location VARCHAR(25)
BEGIN
...
END$$
What I am trying to achieve is replace the values 25 in the script with a constant - similar to a #define
at the top of the script that creates the table and stored procedures, so I am able to easily change the 25 to another number.
Anybody has found a solution to this problem?
A SQL script is a set of SQL commands saved as a file in SQL Scripts. A SQL script can contain one or more SQL statements or PL/SQL blocks. You can use SQL Scripts to create, edit, view, run, and delete script files.
The SQL LIKE Operator There are two wildcards often used in conjunction with the LIKE operator: The percent sign (%) represents zero, one, or multiple characters. The underscore sign (_) represents one, single character.
Structured query language (SQL) commands, or scripts, are used to complete otherwise repetitive tasks that a computer can easily accomplish en masse.
The SQL script file is a container for SQL statements or commands. When you run SQL statements from a client such as JSqsh, the script file is a convenient way of manipulating large numbers of statements.
The C Pre Processor (cpp) is historically associated with C (hence the name), but it really is a generic text processor that can be used (or abused) for something else.
Consider this file, named location.src (more on that later).
// C++ style comments works here
/* C style works also */
-- plain old SQL comments also work,
-- but you should avoid using '#' style of comments,
-- this will confuse the C pre-processor ...
#define LOCATION_LEN 25
/* Debug helper macro */
#include "debug.src"
DROP TABLE IF EXISTS test.locations;
CREATE TABLE test.locations
(
`location` VARCHAR(LOCATION_LEN) NOT NULL
);
DROP PROCEDURE IF EXISTS test.AddLocation;
delimiter $$
CREATE PROCEDURE test.AddLocation (IN location VARCHAR(LOCATION_LEN))
BEGIN
-- example of macro
ASSERT(length(location) > 0, "lost or something ?");
-- do something
select "Hi there.";
END
$$
delimiter ;
and file debug.src, which is included:
#ifdef HAVE_DEBUG
#define ASSERT(C, T) \
begin \
if (not (C)) then \
begin \
declare my_msg varchar(1000); \
set my_msg = concat("Assert failed, file:", __FILE__, \
", line: ", __LINE__, \
", condition ", #C, \
", text: ", T); \
signal sqlstate "HY000" set message_text = my_msg; \
end; \
end if; \
end
#else
#define ASSERT(C, T) begin end
#endif
When compiled with:
cpp -E location.src -o location.sql
you get the code you are looking for, with cpp expanding #define values.
When compiled with:
cpp -E -DHAVE_DEBUG location.src -o location.sql
you get the same, plus the ASSERT macro (posted as a bonus, to show what could be done).
Assuming a build with HAVE_DEBUG deployed in a testing environment (in 5.5 or later since SIGNAL is used), the result looks like this:
mysql> call AddLocation("Here");
+-----------+
| Hi there. |
+-----------+
| Hi there. |
+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> call AddLocation("");
ERROR 1644 (HY000): Assert failed, file:location.src, line: 24, condition length(location) > 0, text: lost or something ?
Note how the file name, line number, and condition points right at the place in the source code in location.src where the assert is raised, thanks again to the C pre processor.
Now, about the ".src" file extension:
EDIT: Originally posted as .xql, renamed to .src for clarity. Nothing related to xml queries here.
As with any tools, using cpp can lead to good things, and the use case for maintaining LOCATION_LEN in a portable way looks very reasonable. It can also lead to bad things, with too many #include, nested #ifdef hell, macros, etc that at the end obfuscate the code, so your mileage may vary.
With this answer, you get the whole thing (#define
, #include
, #ifdef
, __FILE__
, __LINE__
, #C
, command line options to build), so I hope it should cover it all.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With