5

I have the following script:


use my_db;

if (2 < 3) then
    select 1;
end if;

When I execute this with command:

mysql --user=myuser --password=mypassword < script.sql

I get the following error:

ERROR 1064 (42000) at line 3: 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 'if (2 < 3) then select 1' at line 1

Can anybody explain me why this? From mysql docs found here I think it should be working fine.

artaxerxe
  • 6,281
  • 21
  • 68
  • 106
  • 2
    The `if` statement is only allowed in programming blocks, such as stored procedures, user-defined functions, and triggers. Put the conditional logic in a stored procedure and call the stored procedure from the script. – Gordon Linoff Mar 25 '17 at 13:17

2 Answers2

2

If you can change your statement, I would recommend it doing it this way:

select if(2<3, 'true','false') as amount

Or wrap your code in a procedure:

create procedure my_procedure() 
begin
  if (2 < 3) then
      select 1;
  end if;
end;

-- Execute the procedure
call my_procedure();

-- Drop the procedure
drop procedure my_procedure;
Wadih M.
  • 12,810
  • 7
  • 47
  • 57
1

Still don't understand... :). How to check if IF isn't permitted?

https://dev.mysql.com/doc/refman/5.7/en/flow-control-statements.html says:

MySQL supports the IF, CASE, ITERATE, LEAVE LOOP, WHILE, and REPEAT constructs for flow control within stored programs. It also supports RETURN within stored functions.

(emphasis mine)

I wouldn't bother with writing stored routines in MySQL. If you need to do conditional SQL queries, I'd recommend learning a scripting language. Python is a good choice.

#!/bin/env python

import MySQLdb

db = MySQLdb.connect()

if 2 < 3:
    cur = db.cursor()
    cur.query('select 1')
    print cur.fetchall()
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 1
    @Ako since I wrote this answer in 2017, we've seen the release of [MySQL Shell](https://dev.mysql.com/doc/mysql-shell/8.0/en/), which allows developers to do logic statements in Python or Javascript. – Bill Karwin Jun 12 '20 at 04:10
  • Thanks for the tip. Just to elaborate, using an external language to handle SQL logic is bad solution and will probably never be accepted as a standard. SQL is a standalone scripting language, why the extra complication. Py and JS require a VM, where the trust issue becomes the tide turner. – Ako Jun 12 '20 at 21:25
  • 1
    I don't think that's correct. From its earliest days, SQL was intended to be a domain-specific language, to be used as a complement to another programming language. Part 3 of the ANSI/ISO SQL standard describes the Call Level Interface (CLI) for using SQL from another language (the standard describes using SQL with Ada, C/C++, COBOL, Fortran, MUMPS, Pascal, PL/I). Part 10 of the standard describes the Object Language Bindings (OLB) for using SQL embedded in Java. – Bill Karwin Jun 12 '20 at 21:39
  • Well if you log in to database engines CLI, we do not use these programming languages, do we? There are two different use cases, external and internal domain. If engine requires external dependency (like Py VM), it's security is compromised and most companies stop there. Main reason why CLI scripts for SQL were standardized. – Ako Jun 12 '20 at 22:51
  • 1
    The term CLI in the SQL standard does not refer to a command-line interface tool. It refers to the call interface. In today's parlance, it's more like the API. How to programmatically open connections, start transactions and cursors, fetch results, that sort of thing. – Bill Karwin Jun 12 '20 at 23:11
  • External domains Call Level Interfaces are abstraction from the engine, why would we need a abstracted interface in the local domain if we already have scripts which engine can interpret? It's like clicking a mouse on local machine, but the click goes through a server before any action will take place. – Ako Jun 12 '20 at 23:41
  • 1
    Sorry, you lost me. Have a great day! – Bill Karwin Jun 12 '20 at 23:55