SQL Scripts - is there an equivalent to #define?

Question

I have a script that I use to build tables and stored procedures. For example, I have a column of type varchar . varchar requires a size parameter, I also use this size as parameters in stored procedures and as part of these procedures.

is it possible to have a #define sign for its size, so I can easily adjust the size without having to change through the whole script?

I am using Workbench MySql.

EDIT

I tried SET and DECLARE

I have a script - this is (shortened)

 CREATE TABLE `locations` ( `location` VARCHAR(25) NOT NULL ); ... CREATE PROCEDURE AddLocation (IN location VARCHAR(25) BEGIN ... END$$ 

What I'm trying to achieve is replacing the 25 values ​​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 can easily change 25 to a different number.

Has anyone found a solution to this problem?

Answer 1

The C Pre processor (cpp) has historically been linked to C (hence the name), but it is really a generic word processor that can be used (or abused) for something else.

Consider this file named location.src (more on this 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 the debug.src file, 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 compiling with:

 cpp -E location.src -o location.sql 

you will get the code you are looking for with cpp extending #define values.

When compiling 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 you can do).

Assuming that the assembly with HAVE_DEBUG is deployed in a test environment (version 5.5 or later since using SIGNAL), the result is as follows:

 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 conditions are displayed right in the source code location in location.src where the statement is executed, thanks again to processor C.

Now about the .src file extension:

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

EDIT: Originally published as .xql, renamed to .src for clarity. Nothing related to xml requests here.

As with any tool, using cpp can lead to good things, and the use case for supporting LOCATION_LEN in portable mode seems very reasonable. It can also lead to bad things, with too many #include, #ifdef nested admins, macros, etc. that obfuscate the code at the end, so your mileage may vary.

With this answer you will get everything ( #define , #include , #ifdef , __FILE__ , __LINE__ , #C , command line options for assembly), so I hope he owes it all.

Answer 2

Have you tried SET?

here is an example:

 SET @var_name = expr 

more examples here: http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

Answer 3

It looks like you are looking for custom data types. Unfortunately, for us all, mySQL does not yet support user-defined data types, such as SQL Server, Oracle, and others.

Here is a list of supported data types: http://dev.mysql.com/doc/refman/5.0/en/data-types.html

Answer 4

For those interested:

I ended up writing a PHP script because:

a) The machine that has access to the database does not belong to me, and I cannot access the C preprocessor b) The second answer does not work. c) It seemed the simplest solution

Here is a script for those who may find this useful. I use it to define a column of width tables, and then use the same values ​​in stored procedures. This is because column widths have not yet been fully resolved for production.

I also created that you can define strings that lasted several lines. This has the advantage that I can obey the width of the column 80 (so the print looks readable).

Here is the script

 <?php if (1==count($argv)) { ?> Processing #defines from stdin and send to SQL server: This script will remove 1. #define <name> <integer> 2. #define <name> '<string>' 3. #define <name> '<string>' \ '<continuation of string>' and replace the occurances of name with the #define value as specified <name> is upper case alpha numberics or underscores, not starting with a digit. The arguments of this script is passed to the mysql executable. <?php exit(1); } function replace(&$newValues, $a, $b, $c) { return $a . (array_key_exists($b, $newValues) ? $newValues[$b] : $b) . $c; } // The patterns to be used $numberPattern='/^#define[ \t]+([A-Z_][A-Z0-9_]*)[ \t]+(0|([1-9][0-9]*))'. '[ \t]*[\r\n]+$/'; $stringPattern= '/^#define[ \t]+([A-Z_][A-Z0-9_]*)[ \t]+\''. '((\\\'|[^\'\r\n])*)\'[ \t]*(\\\\{0,1})[\n\r]+$/'; $continuationPattern='/^[ \t]*\'((\\\'|[^\'\r\n])*)\'[ \t]*'. '(\\\\{0,1})[\n\r]+$/'; // String to be evaluated to replace define values with a new value $evalStr='replace($newValues, \'\1\', \'\2\', \'\3\');'; array_splice($argv, 0, 1); // Open up the process $mysql=popen("mysql ".implode(' ', $argv), 'w'); $newValues=array(); // Stores the defines new values // Variables to control the replacement process $define=false; $continuation=false; $name=''; $value=''; while ($line=fgets(STDIN)) { $matches=array(); // #define numbers if (!$define && 1 == preg_match($numberPattern, $line, $matches)) { $define = true; $continuation = false; $name = $matches[1]; $value = $matches[2]; } // #define strings if (!$define && 1 == preg_match($stringPattern, $line, $matches)) { $define = true; $continuation = ('\\' == $matches[4]); $name = $matches[1]; $value = $matches[2]; } // For #define strings that continue over more than one line if ($continuation && 1 == preg_match($continuationPattern, $line, $matches)) { $value .= $matches[1]; $continuation = ('\\' == $matches[3]); } // Have a complete #define, add to the array if ($define && !$continuation) { $define = $continuation = false; $newValues[$name]=$value; } elseif (!$define) { // Do any replacements $line = preg_replace('/(^| |\()([A-Z_][A-Z0-9_]*)(\)| |$)/e', $evalStr, $line); echo $line; // In case we need to have pure SQL. // Send it to be processed. fwrite($mysql, $line) or die("MySql has failed!"); } } pclose($mysql); ?>