Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Scripts - Does the equivalent of a #define exist?

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?

like image 305
Ed Heal Avatar asked Feb 11 '12 08:02

Ed Heal


People also ask

What does SQL script do?

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.

What means '%' in SQL?

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.

What are SQL scripts called?

Structured query language (SQL) commands, or scripts, are used to complete otherwise repetitive tasks that a computer can easily accomplish en masse.

What is a SQL script file?

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.


1 Answers

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:

  • you can use anything.
  • Having a different file extension helps with makefiles, etc, and prevents confusion.

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.

like image 136
Marc Alff Avatar answered Oct 16 '22 12:10

Marc Alff