# Custom Database interface: overwrite query class?

**URL:** <https://forum.djangoproject.com/t/custom-database-interface-overwrite-query-class/39356>\
**Category:** Using the ORM\
**Created:** [March 7, 2025, 12:50pm UTC](https://forum.djangoproject.com/t/custom-database-interface-overwrite-query-class/39356 "2025-03-07T12:50:15Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![fvollmer](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/fvollmer/32/27326_2.png) [@fvollmer](https://forum.djangoproject.com/u/fvollmer)\
**Post date:** [March 7, 2025, 12:50pm UTC](https://forum.djangoproject.com/t/custom-database-interface-overwrite-query-class/39356/1 "2025-03-07T12:50:15Z")

</div>

I have to connect to a legacy system with a legacy database. The database uses an advantage database server. I’ve forked [adsdb-django](https://code.google.com/archive/p/adsdb-django/) and modified it to work with python 3 and modern django versions.

I’m now experience the following incompatibility:  
the ads db fails with “2154 The data type of a parameter cannot be determined” for queries like e.g.  
`SELECT ? as "a" in ....` with a parameter `1`  
According to the database documentation these queries are not supported:

> Problem: A parameter by itself in the SELECT list or ORDER BY clause has an ambiguous data type. For example, given the statements “SELECT ? FROM mytable” or “SELECT \* FROM myTable ORDER BY ?”, the data type of the parameter in either statement cannot be determined.
> 
> Solution: Remove the ambiguous parameter from the statement. The use of the parameter in the SELECT list or ORDER BY clause is generally not meaningful.

The offending queries are generated by e.g. `Query.exists()`, which calls [`q.add_annotation(Value(1), "a")`](https://github.com/django/django/blob/647dca413262d8778471479e789f96099044af39/django/db/models/sql/query.py#L668). As a workaround I’ve implemented a filter in the SQLCompiler like this:

```auto
class SQLCompiler(compiler.SQLCompiler):

    def filter_query(self):
        annotations_to_readd = {}
        
        # find offending annotations
        for key, annotation in self.query.annotations.items():
            if isinstance(annotation, Value) and isinstance(annotation.value, int):
                annotations_to_readd[key] = annotation.value

        # remove annotations
        for key in annotations_to_readd.keys():
            self.query.annotations.pop(key, None)

        # Re-add the annotations
        for key, value in annotations_to_readd.items():
            self.query.add_extra({key: value}, None, None, None, None, None)
        self.query.set_extra_mask(list(annotations_to_readd.keys()))

    def as_sql(self, with_limits=True, with_col_aliases=True):
        self.filter_query()

        query, params = super(SQLCompiler, self).as_sql(...)

```

Is there a better way to achieve my goal?  
I would think overriding `Query.exists()` would be better, but can I somehow have a custom query class for my database interface?

---

<div class="post-metadata">

**Author:** ![charettes](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/charettes/32/27_2.png) [@charettes](https://forum.djangoproject.com/u/charettes)\
**Post date:** [March 7, 2025, 7:03pm UTC](https://forum.djangoproject.com/t/custom-database-interface-overwrite-query-class/39356/2 "2025-03-07T19:03:38Z")

</div>

I haven’t tried it out myself, and I’m not familiar with _advantage_, but could you also use a `CAST` to make sure the proper type is inferred?

In all cases case there are two approaches you can use to generate per-backend specific SQL.

The first one is providing your own `SQLCompiler` subclass like you did. The only change I’d make is to avoid using _extra_ interfaces as they will eventually be deprecated. I’d replace the annotations with `RawSQL` instance instead.

```python
def filter_query(self):
    for key, annotation in self.query.annotations.items():
        if (
            isinstance(annotation, Value)
            and isinstance(annotation.value, int)
        ):
            expr = RawSQL(
                str(annotation.value),
                (),
                output_field=annotation.output_field(),
            )
            self.query.annotations[key] = expr

```

A less invasive solution [would be to attach](https://docs.djangoproject.com/en/5.1/ref/models/expressions/#django.db.models.Func.as_sql) a `as_<vendor_name>` (I assume `as_advantage`) method on `Value`

```auto
def value_as_advantage(self, compiler, connection):
    if isinstance(self.value, int):
        return str(self.value), ()
    return self.as_sql(compiler, connection)

from django.db.models import Value

Value.as_advantage = value_as_advantage

```

---

<div class="post-metadata">

**Author:** ![fvollmer](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/fvollmer/32/27326_2.png) [@fvollmer](https://forum.djangoproject.com/u/fvollmer)\
**Post date:** [March 15, 2025, 12:12am UTC](https://forum.djangoproject.com/t/custom-database-interface-overwrite-query-class/39356/3 "2025-03-15T00:12:07Z")

</div>

Thanks, your answer was super helpful! The cast idea also works, so I decided to monkey patch the Value class by adding

```auto
from django.db.models import Value

def value_as_adsdb(self, compiler, connection):
    if isinstance(self.value, int):
        return "CAST(%s as SQL_INTEGER)", (self.value,)
    return self.as_sql(compiler, connection)

Value.as_adsdb = value_as_adsdb

```

to my `compiler.py`. I guess this is the safest and least invasive way to do this.

---

<div class="post-metadata">

**Author:** ![fvollmer](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/fvollmer/32/27326_2.png) [@fvollmer](https://forum.djangoproject.com/u/fvollmer)\
**Post date:** [March 15, 2025, 12:43pm UTC](https://forum.djangoproject.com/t/custom-database-interface-overwrite-query-class/39356/4 "2025-03-15T12:43:16Z")

</div>

Actually this isn’t working in all cases:  
`[SAP][Advantage SQL Engine]Value in an IN expression must be a simple value'`

I’m now back to filtering the query in the compiler

```auto
    def filter_query(self):
        for key, annotation in self.query.annotations.items():
            if isinstance(annotation, Value) and isinstance(annotation.value, int):
                expr = RawSQL(
                    "CAST(%s as SQL_INTEGER)",
                    (annotation.value,),
                    output_field=annotation.output_field,
                )
                self.query.annotations[key] = expr

```

---

<div class="post-metadata">

**Author:** ![charettes](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/charettes/32/27_2.png) [@charettes](https://forum.djangoproject.com/u/charettes)\
**Post date:** [March 15, 2025, 7:16pm UTC](https://forum.djangoproject.com/t/custom-database-interface-overwrite-query-class/39356/5 "2025-03-15T19:16:07Z")

</div>

> [@fvollmer](#):
>
> Actually this isn’t working in all cases:  
> `[SAP][Advantage SQL Engine]Value in an IN expression must be a simple value'`

Assuming this is coming from a `filter(some_field__in=...)` lookup you could have most likely adapted `django.db.models.lookups.In.as_adsdb` like you did with `Value`.