Using a variable db name in a RawSQL function

Using Django Using the ORM
1

Hi,

For the purpose of my application, I would like to annotate a queryset with a value I have to fetch from an other database. To do that, I tried the following code :

def get_queryset(self):
  return self.queryset.annotate(
      value=RawSQL(
          """
          DECLARE @database NVARCHAR(MAX) = %s;
          DECLARE @sql NVARCHAR(MAX) = N'
              SELECT c.value
              FROM [' + @database + N'].[dbo].[table] t
              WHERE t.vendor = vendor_ref';
          EXEC sp_executesql @sql;
          """,
          (settings.DATABASE),
      ),
  )

In this snippet, vendor_ref is a field of the Django model I am currently using and settings.DATABASE is the name of the database I want to use for this query.

But the problem is I am getting a 404 - Not found error when running this code.

When I try to put the db name directly in the query without using the variable it works fine. Have you any idea why this is not working?

Thank you in advance!
Lyzo

2

How are you getting a 404 as a result of this? (Or is that just your way of saying that the database is throwing a “Not Found” error regarding the database?)

What’s the purpose of the leading plus sign in the string in the brackets in the FROM statement?

Which database engine is being used here?

3

404 is the error Django is throwing in response to my request but it has to be linked to my SQL query.

To be honest, I didn’t ask myself why the variable is included in the query like that. But I know that it is working fine when I am doing it this way using the django connection.cursor().

I am using a MySQL database.

4

It may or may not. There’s insufficient evidence at this point to support that assertion.

Please post the url being requested that is generating the 404 and the view related to that url.

5

I am requesting this url: http://127.0.0.1:8003/my-api/MyModel/object_reference/

And here is the view:

class MyViewSet(ModelViewSet):
    model = MyModel
    queryset = MyModel.objects.all()

    lookup_field = "reference"

    def get_queryset(self):
      return self.queryset.annotate(
          value=RawSQL(
              """
              DECLARE @database NVARCHAR(MAX) = %s;
              DECLARE @sql NVARCHAR(MAX) = N'
                  SELECT c.value
                  FROM [' + @database + N'].[dbo].[table] t
                  WHERE t.vendor = vendor_ref';
              EXEC sp_executesql @sql;
              """,
              (settings.DATABASE),
          ),
      )
6

Ahh, I see that DRF is involved here. Hopefully someone familiar with DRF on the forum can jump in here.