CODEWITHSUNDEEP
mysqluser-defined-functionstyping
Aristide
Aristide

Reputation: 3994

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?

Upvotes: 0

Views: 97

Answers (3)

nbk
nbk

Reputation: 49410

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

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 563011

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.

Upvotes: 2

Aristide
Aristide

Reputation: 3994

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!

Upvotes: 3

Related Questions