Question 358022 in Mysql Question posted in
The official MySQL documentation can be found here.

Writing a MySQL wrapper around CRC32

I need to write some cross-vendor SQL queries, and I would like to hide certain details that are of lesser importance in the context.

Namely, I want to create a wrapper named checksum() around the MySQL function crc32(). It would just passes its argument to crc32() and returns the result of the latter function.

mysql> select crc32('foobar');
+-----------------+
| crc32('foobar') |
+-----------------+
|      2666930069 |
+-----------------+
1 row in set (0.00 sec)

mysql> CREATE FUNCTION checksum(x TEXT) RETURNS BIGINT DETERMINISTIC
    -> RETURN crc32(x);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select checksum('foobar');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('foobar')' at line 1

I am not convinced this is an actual syntax error. I am suspecting a type mismatch, but I have tried with all types I could think of (VARCHAR(255) for x, UNSIGNED INT, LONG, etc. for the return type).

I have no more ideas, and ChatGPT 4 either 😉

What am I missing?

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0 
    mysql> SHOW WARNINGS;
+-------+------+-----------------------------------------------------------------+
| Level | Code | Message                                                         |
+-------+------+-----------------------------------------------------------------+
| Note  | 1585 | This function 'checksum' has the same name as a native function |
+-------+------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

    It works with another name. Forget it!


  2. 0

    MySQL has already the name checksum, so you need to rename your function

    select crc32('foobar');
    crc32(‘foobar’)
    2666930069 
    CREATE FUNCTION checksum_v(x TEXT) 
  RETURNS BIGINT
  DETERMINISTIC
  BEGIN
    RETURN crc32(x);
  END;

    select checksum_v('foobar');
    checksum_v(‘foobar’)
    2666930069

    fiddle

    Login or Signup to reply.
  3. 0

    Works for me. But you have to call the function with back-tick delimiters so it will be interpreted as an identifier instead of a keyword.

    mysql> select `checksum`('foobar');
+----------------------+
| `checksum`('foobar') |
+----------------------+
|           2666930069 |
+----------------------+
1 row in set, 1 warning (0.01 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------------------------+
| Level | Code | Message                                                         |
+-------+------+-----------------------------------------------------------------+
| Note  | 1585 | This function 'checksum' has the same name as a native function |
+-------+------+-----------------------------------------------------------------+

    You can see it still returns a warning, but at least it runs the stored function.

    In general, trying to write cross-vendor SQL queries is full of exception cases like this. In my experience, it’s easier to develop an Adapter Pattern in your application code, so you can run different SQL queries for each back-end database.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search