Declare Variable in Sqlite and Use It

Declare variable in SQLite and use it

SQLite doesn't support native variable syntax, but you can achieve virtually the same using an in-memory temp table.

I've used the below approach for large projects and works like a charm.

    /* Create in-memory temp table for variables */
    BEGIN;

    PRAGMA temp_store = 2; /* 2 means use in-memory */
    CREATE TEMP TABLE _Variables(Name TEXT PRIMARY KEY, RealValue REAL, IntegerValue INTEGER, BlobValue BLOB, TextValue TEXT);

    /* Declaring a variable */
    INSERT INTO _Variables (Name) VALUES ('VariableName');

    /* Assigning a variable (pick the right storage class) */
    UPDATE _Variables SET IntegerValue = ... WHERE Name = 'VariableName';

    /* Getting variable value (use within expression) */
    ... (SELECT coalesce(RealValue, IntegerValue, BlobValue, TextValue) FROM _Variables WHERE Name = 'VariableName' LIMIT 1) ...

    DROP TABLE _Variables;
    END;

How to use variables for values in SQLite?

You should be able to select the variables from the table you set up:

SELECT
    LastName,
    FirstName,
    BirthYear,
    (strftime('%Y', date('now')) - BirthYear) AS Age,
    LocalExp,TotalExp,TotSalary,TotFringe,WorkLocationName,SchoolName
FROM DPISTAFF2008_2009
WHERE
    DPISTAFF2008_2009.FirstName =
        (SELECT Value FROM _Variables WHERE Name = 'VarFirstName') AND
    DPISTAFF2008_2009.LastName =
        (SELECT Value FROM _Variables WHERE Name = 'VarLastName');

Declaring SQLite Variables

Take a look at this one: Declare variable in sqlite and use it :

Try using Binding Values. You cannot use variables as you do in T-SQL but you can use "parameters". I hope the following link is usefull.Binding Values

sqlite variables

As far as I know SQLite doesn't support anything like that.

The syntax is standard for libraries that implement bound parameters (and prepared statements that use them), but you would need to do that in a programming language that queries the database, and not in the database itself.

The specifics, of course, depend on the programming language and the library.

In Perl, for example you could:

my $sth = $dbh->prepare("Select * from t2 where value=?");
foreach my $value (@values) {
    $sth->execute($value);
    $row = $sth->fetchrow_hashref;
    [...]
}

Bobby Tables has some more examples in a variety of languages.

How to make a select query using a local variable in SQLite on Android?

try this

Cursor cursor = db.rawQuery("SELECT ? FROM table", new String[] { local });

? will be replaced by local.

From TCL use a variable in sqlite IN statement

If you are needing to move a number of values into SQLite from Tcl in order to do this sort of thing, put them into a temporary table in the :memory: database first, then use SQL operations to pull them from there.

DB eval {
    ATTACH DATABASE ':memory:' AS memdb;
    CREATE TEMP TABLE memdb.values (val TEXT);
}
foreach val {1 3 4} {
    DB eval {
        INSERT INTO memdb.values (val) VALUES (:val)
    }
}
set LCs [DB eval {
    SELECT DISTINCT LC FROM MyTable WHERE LC IN memdb.values.val
}]

Python SQLite printing from a table where ID is equal set variable

You may use the following single query:

SELECT f.title, f.release_year
FROM film f
INNER JOIN film_list fl ON fl.fid = f.film_id
WHERE fl.category = ?

Your updated Python code:

sql = '''SELECT f.title, f.release_year
FROM film f
INNER JOIN film_list fl ON fl.fid = f.film_id
WHERE fl.category = ?'''

kategorija = input("Enter the category: ")
result = []
c.execute(sql, (kategorija,))
result.append(c.fetchAll())
print(result)

---

Related Topics