Not sure if this is the correct location to ask this question, but: How do I Call and get the return values from a Stored Procedure using Prisma. Here is the Stored Procedure Syntax: CALL GetSequenceBlock('ach', 0, @start, @end) From what I can find in the documentation, any Prisma call has to start with a SELECT, but that doesn't seem to be allowed by MySQL. ie: SELECT @start, @end FROM ( CALL GetSequenceBlock('ach', 0, @start, @end) );

The only way could be via one of the raw sql commands

So, I have been trying several methods... The only one that seems close to working is this: result = await prisma.$executeRaw(sql);     console.log('getSequenceBlockPrisma: result:', result);     sql = `SELECT @newSeqStart, @newSeqEnd FROM DUAL`;     result = await prisma.$queryRaw(sql);

That sounds like you are using two connections under the hood.

How would I do that ? For that matter how do I do any Transactions in Prisma ? The only thing I found for doing that was to have nested objects ...

Running this: const getSequenceBlockPrisma = async (sequenceName, addCount = 1, reset = false) => {   let data = false;   try {     const [callGetSequenceBlock, getStartEnd] = await prisma.$transaction([       prisma.$executeRaw`CALL GetSequenceBlock('${sequenceName}', ${addCount}, @start, @end);`,       prisma.$queryRaw`SELECT @start, @end FROM DUAL;`,     ]);     console.log(

getSequenceBlockPrisma: callGetSequenceBlock:

, callGetSequenceBlock);     console.log(

getSequenceBlockPrisma: getStartEnd:

, getStartEnd);   } catch (error) {     throw error;   }   return data; }; I get this: Error: Error in connector: Server terminated the connection.

Shame, does not seem to work then.

Running MySQL 5.7 So, you think that is a Bug, or I am just using it wrong ?

Stored procedures are special, so this is possibly just a missing feature on our side.

I built a "Poor Man's" Sequence feature for MySQL (which doesn't have Sequences). To make the actions "Atomic", they had to be in a Stored Procedure. I can provide the Store Procedure Source, if that is helpful. Also, if the prisma.$queryRaw() would accept a SQL without requiring the SQL to start with a SELECT, it might just work on it's own...

@Edward Baer Did the transaction resolve your issue? I'm running into a similar issue with calling a proc and getting the panic error.

@Melvin Gaye Here is what I am doing to call the Stored Procedure:

const getSequenceBlock = async (sequenceName, addCount = 1) => {
  let data = false;

  try {
    const [callGetSequenceBlock, getStartEnd] = await prisma.$transaction([
      prisma.$executeRaw(`CALL GetSequenceBlock('${sequenceName}', ${addCount}, @start, @end);`),
      // prisma.$queryRaw(`SELECT @start, @end, @newSeqStart, @newSeqEnd FROM DUAL;`),
      prisma.$queryRaw(`SELECT @newSeqStart, @newSeqEnd FROM DUAL;`),
    ]);

    // console.log(`getSequenceBlockPrisma: callGetSequenceBlock:`, callGetSequenceBlock);
    // console.log(`getSequenceBlockPrisma: getStartEnd:`, getStartEnd);

    // Successful result comes back as [ { '@newSeqStart': 146, '@newSeqEnd': 151 } ]
    if (Array.isArray(getStartEnd)) {
      const row = getStartEnd[0];
      if (typeof row === 'object') {
        data = { 
          start: row['@newSeqStart'],
          end: row['@newSeqEnd'],
        };
      }
    }
  } catch (error) {
    throw error;
  }

  return data;
};

And here is the Stored Procedure I am calling:

CREATE DEFINER=`ach`@`%` PROCEDURE `GetSequenceBlock`(
      IN sequenceName VARCHAR(100),
      IN addCount BIGINT,
      OUT start BIGINT,
      OUT end BIGINT
    )
    SQL SECURITY INVOKER
BEGIN
      DECLARE newSeqStart BIGINT;
      DECLARE newSeqEnd BIGINT;
      DECLARE normalSequence BIGINT;
        
      SET @normalSequence = LOWER(sequenceName);

      SELECT currentValue INTO @currentValue FROM Sequence WHERE name = @normalSequence;

      IF @currentValue IS NOT NULL THEN
        UPDATE Sequence 
        SET temp = @newSeqStart := currentValue, currentValue = @newSeqEnd := currentValue + addCount
        WHERE name = @normalSequence;
      ELSE
        SET @newSeqStart = NULL;
        SET @newSeqEnd = NULL;
      END IF;

      SELECT @newSeqStart INTO start;
      SELECT @newSeqEnd INTO end;
      SELECT @newSeqStart, @newSeqEnd;
    END

It is a "Poor Man's" Sequence, because MySQL doesn't have Sequences. This structure seems to work correctly, but you might not need the IN/OUT pieces. I believe the trick is the final SELECT in the Stored Procedure must match what you are looking for in the Result set. ie: @newSeqStart, @newSeqEnd. The result set return structure is rather weird, and you need to console.log or dump it out to figure out where your actual data is. I haven't tried it with multiple row returns, so this may not be your solution. However, I am pretty sure you just make what you want back as the last SELECT in the Stored Procedure. If you have multiple SELECTS, they will each come out, so be sure to put them into something, then SELECT them at the end...

Thank you! Will see if I can adjust for my use case. Really don't want to have to out the whole procedure as a raw query, really lowers readability

Yeah, Prisma still has some growing to do, but I mostly like it. Glad to see they are embracing the Transaction now ...

@Melvin Gaye @Edward Baer 👋 We already have! You can use long running transactions in the latest version 😄 https://github.com/prisma/prisma/releases/tag/2.29.0

@Ryan That was what I was saying...

Yeah I just added the link for a better perspective and that also includes an example link on how to do that.

k, thx ...

@Ryan not sure how I can use this to call a stored procedure. I saw the example https://github.com/prisma/prisma/issues/2930#issuecomment-655156385. But my procedure takes parameters and returns a single row.