Search code examples
mysqlmysql-workbenchdapper

MySQL + Dapper QueryMultiple: how to execute multiple SELECT queries at once?

I'm having a hard time finding this on the internet...

I need to run a QueryMultiple command in Dapper + MySQL, with a query that would look like this in SQL Server:

DECLARE @clientDomain VARCHAR(64) SET @clientDomain = 'some-domain';
DECLARE @sessionId VARCHAR(64) SET @sessionId = 'd09a8sd09a7g8gh';

SELECT *
FROM sessions.Sessions Sessions
WHERE Sessions.ClientDomain = @clientDomain AND Sessions.SessionId = @sessionId;

SELECT *
FROM sessions.PageVisits PageVisits
WHERE PageVisits.SessionClientDomain = @clientDomain AND PageVisits.SessionId = @sessionId

Of course, @clientDomain and @sessionId are going to be correctly passed in as query parameters in the QueryMultiple method, but I'm writing the query in MySQL Workbench first, so I've declared the variables there for development.

My Problem is:

I can't get it to function in Workbench and I suppose it won't work in Dapper either.

Samples of what I've tried:

enter image description here

And with BEGIN/END:

enter image description here

I've gone through this: https://dev.mysql.com/doc/refman/5.6/en/sql-syntax-compound-statements.html and attempted variations with labels, without labels, etc.

Note: I do not want to write a stored procedure. My desired end result is a .sql file that I'm going to include as an embeded resource in the project, and then load it from code at run time.

I'm using MySQL 5.6, Workbench Community 8.0.15.

What am I missing?

Solution:

After Jane's comment below, I have two working versions:

START TRANSACTION;
    SET @clientDomain = 'some-domain';
    SET @sessionId = 'd09a8sd09a7g8gh';

    SELECT *
    FROM sessions.Sessions Sessions
    WHERE Sessions.ClientDomain = @clientDomain AND Sessions.SessionId = @sessionId;

    SELECT *
    FROM sessions.PageVisits PageVisits
    WHERE PageVisits.SessionClientDomain = @clientDomain AND PageVisits.SessionId = @sessionId;
COMMIT;

And one with BEGIN; but, oddly enough, without END; (if I try it, I get an error telling me to look for documentation on how to properly write the END statement):

BEGIN;
    SET @clientDomain = 'some-domain';
    SET @sessionId = 'd09a8sd09a7g8gh';

    SELECT *
    FROM sessions.Sessions Sessions
    WHERE Sessions.ClientDomain = @clientDomain AND Sessions.SessionId = @sessionId;

    SELECT *
    FROM sessions.PageVisits PageVisits
    WHERE PageVisits.SessionClientDomain = @clientDomain AND PageVisits.SessionId = @sessionId;

I'm going with the first (START TRANSACTION) setting it as READ ONLY.

Solution

  • I believe you are looking for START TRANSACTION

    START TRANSACTION;

DECLARE @clientDomain VARCHAR(64) SET @clientDomain = 'some-domain';
DECLARE @sessionId VARCHAR(64) SET @sessionId = 'd09a8sd09a7g8gh';

SELECT *
FROM sessions.Sessions Sessions
WHERE Sessions.ClientDomain = @clientDomain AND Sessions.SessionId = @sessionId;

SELECT *
FROM sessions.PageVisits PageVisits
WHERE PageVisits.SessionClientDomain = @clientDomain AND PageVisits.SessionId = @sessionId

COMMIT;