Asked  7 Months ago    Answers:  5   Viewed   39 times

How to declare a variable in mysql, so that my second query can use it?

I would like to write something like:

SET start = 1;
SET finish = 10;

SELECT * FROM places WHERE place BETWEEN start AND finish;

 Answers

72

There are mainly three types of variables in MySQL:

  1. User-defined variables (prefixed with @):

    You can access any user-defined variable without declaring it or initializing it. If you refer to a variable that has not been initialized, it has a value of NULL and a type of string.

    SELECT @var_any_var_name
    

    You can initialize a variable using SET or SELECT statement:

    SET @start = 1, @finish = 10;    
    

    or

    SELECT @start := 1, @finish := 10;
    
    SELECT * FROM places WHERE place BETWEEN @start AND @finish;
    

    User variables can be assigned a value from a limited set of data types: integer, decimal, floating-point, binary or nonbinary string, or NULL value.

    User-defined variables are session-specific. That is, a user variable defined by one client cannot be seen or used by other clients.

    They can be used in SELECT queries using Advanced MySQL user variable techniques.

  2. Local Variables (no prefix) :

    Local variables needs to be declared using DECLARE before accessing it.

    They can be used as local variables and the input parameters inside a stored procedure:

    DELIMITER //
    
    CREATE PROCEDURE sp_test(var1 INT) 
    BEGIN   
        DECLARE start  INT unsigned DEFAULT 1;  
        DECLARE finish INT unsigned DEFAULT 10;
    
        SELECT  var1, start, finish;
    
        SELECT * FROM places WHERE place BETWEEN start AND finish; 
    END; //
    
    DELIMITER ;
    
    CALL sp_test(5);
    

    If the DEFAULT clause is missing, the initial value is NULL.

    The scope of a local variable is the BEGIN ... END block within which it is declared.

  3. Server System Variables (prefixed with @@):

    The MySQL server maintains many system variables configured to a default value. They can be of type GLOBAL, SESSION or BOTH.

    Global variables affect the overall operation of the server whereas session variables affect its operation for individual client connections.

    To see the current values used by a running server, use the SHOW VARIABLES statement or SELECT @@var_name.

    SHOW VARIABLES LIKE '%wait_timeout%';
    
    SELECT @@sort_buffer_size;
    

    They can be set at server startup using options on the command line or in an option file. Most of them can be changed dynamically while the server is running using SET GLOBAL or SET SESSION:

    -- Syntax to Set value to a Global variable:
    SET GLOBAL sort_buffer_size=1000000;
    SET @@global.sort_buffer_size=1000000;
    
    -- Syntax to Set value to a Session variable:
    SET sort_buffer_size=1000000;
    SET SESSION sort_buffer_size=1000000;
    SET @@sort_buffer_size=1000000;
    SET @@local.sort_buffer_size=10000;
    
Tuesday, June 1, 2021
 
Jauco
answered 7 Months ago
74

From the PHP Manual on mysqli_stmt::execute:

mysqli_stmt::execute -- mysqli_stmt_executeExecutes a prepared Query

Returns TRUE on success or FALSE on failure.


if ($stmt->execute()) { // exactly like this!
    $success = true;
}

You're doing it right... What's your dilemma?

Wednesday, March 31, 2021
 
Sauleil
answered 9 Months ago
69

The problem is covered on the github issues thread - https://github.com/flask-admin/flask-admin/issues/1588

Basically, the flask-admin pip package is out of date, in regards the latest sqlalchemy pip package. In that specific area,

cls, key = identity_key(instance=obj)

sqlalchemy is now returning 3 objects, but flask-admin is only expecting 2, hence the error.

The real solution for this is to wait until a new flask-admin version is uploaded to pip, until then, you've a few options.

  1. Manually go in and edit that fields.py file
  2. As detailed in that issues thread, limit sqlalchemy to version 1.2.0b3. You can do this in your requirements.txt file, or manually with a pip upgrade install, pip install --upgrade sqlalchemy==1.2.0b3
  3. As the fix is in flask-admin's master branch in their github repository, install that version of flask-admin with the pip location of git+https://github.com/flask-admin/flask-admin. Again, you do this in your requirements.txt file, or with a pip upgrade install, pip install --upgrade git+https://github.com/flask-admin/flask-admin.

My personal preference, and what I've done, is option 3. If you look through the code itself, it's a line the maintainers want to remove, anyway, and how they're dealing with it is better, and in general with these things, I prefer to go forward (latest version of flask-admin) rather than holding things back (rollback sqlalchemy to a previous version), and certainly better than manually editing the raw code.

Tuesday, August 3, 2021
 
Viralk
answered 4 Months ago
61

Unfortunately, there are no procedural SQL extensions in Netezza that allow you to employ variables like this as part of the SQL language itself. Purely SQL solutions would involve kludges such as joining to a CTE returning that one value. However, the NZSQL CLI does allow the use of session variables, as does Aginity Workbench.

An example using NZSQL. Note the escape of the inner single quotes to use the variable as a literal.

TESTDB.ADMIN(ADMIN)=> set TVAR ''foo''
TESTDB.ADMIN(ADMIN)=> select :TVAR;
 ?COLUMN?
----------
 foo
(1 row)
TESTDB.ADMIN(ADMIN)=> create table test_table (col1 bigint);
CREATE TABLE
TESTDB.ADMIN(ADMIN)=> insert into test_table values (123);
INSERT 0 1
TESTDB.ADMIN(ADMIN)=> set TCOL 'COL1'
TESTDB.ADMIN(ADMIN)=> select :TCOL from test_table;
 COL1
------
  123
(1 row)

Aginity will auto-prompt for a values when it see $var_name, but there's no functionality to hard-code that variable definition, at least as far as I know.

Sunday, August 15, 2021
 
EurekA
answered 4 Months ago
79

I can see the following issues with that SP, which may or may not relate to your problem:

  • You have an extraneous ) after @BrandName in your SELECT (at the end)
  • You're not setting @CategoryID or @BrandName to anything anywhere (they're local variables, but you don't assign values to them)

In a comment you've said that after fixing the ) you get the error:

Procedure AddBrand has no parameters and arguments were supplied.

That's telling you that you haven't declared any parameters for the SP, but you called it with parameters. Based on your reply about @CategoryID, I'm guessing you wanted it to be a parameter rather than a local variable. Try this:

CREATE PROCEDURE AddBrand
   @BrandName nvarchar(50), -- These are the
   @CategoryID int          -- parameter declarations
AS
BEGIN
   DECLARE @BrandID int

   SELECT @BrandID = BrandID FROM tblBrand WHERE BrandName = @BrandName

   INSERT INTO tblBrandinCategory (CategoryID, BrandID) VALUES (@CategoryID, @BrandID)
END

You would then call this like this:

EXEC AddBrand 'Gucci', 23

or this:

EXEC AddBrand @BrandName = 'Gucci', @CategoryID = 23

...assuming the brand name was 'Gucci' and category ID was 23.

Friday, September 10, 2021
 
TheCarver
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share