Quick ORM - Datatype being changed on insert of records via array of structs

Communities Quick ORM
1

I encountered this issue recently with Quick ORM. The issue is that when I attempt to do an insert with an array of structs, a column datatype is being incorrectly changed by the Quick ORM from an integer (numeric) to varchar and the database reports the error that the conversion cannot occur.

The database grammer is SQL Server.

I have a model.cfc and the accompanying service.cfc.

One other note: When I loop over the array of stucts returned by the get function (see below) and call the insert function passing in only the one array element (an array with length of one and the value being the struct of values from the database), then the insert function performs without error. However, when sending the full array of structs as the argument to the insert, it fails for the error mentioned.

In the model file the column is being set like so:

property name="id" sqltype="CF_SQL_INTEGER";

Here is my get function (services file):

function getConferencesProd(id){

        try{
            var query = db.getQuickBuilder().getQb().where('id', id).setReturnFormat( 'array').get(options = { datasource:"prod" });

            if(isNull(query)){
                return [];
            }

            return query;
        }
        catch (any e) {
            helpers.exceptionHandlerHelper::throwException(message = e.message);
        }
    }

Here is the insert function:

function insertFromProdToDev(fields = []){

        if(arrayIsEmpty(fields)){
            return fields;
        }

        for(field in fields){
           structDelete(field, "cId");
        }
        
        try{            
            var result = db.getQuickBuilder().getQb().insert(values = fields, options = { datasource:"test" });
            return result;

        }catch (any e) {
            helpers.exceptionHandlerHelper::throwException(message = e.message);
        }

    }

The associated sql for a multiple record insert looks something like:

INSERT INTO (col1, col2, col3...)
VALUES ( (val1, val2, val3...), (val3, val4, val5...) )

Values are parameters and the params have the datatypes specified (all done by the ORM, no manual additions). However, the foreign key id is being parameterized as a VARCHAR instead of an INTEGER and the database reports a datatype conversion error. But, only when an array of structs is passed. If you pass an array with just one element, then it works fine.

Any assistance would be greatly appreciated. I do not know if this a bug (i.e. different code is being executed/employed for the insert by array of structs) or if it is a syntax error of which I’m not aware.

Thank you!

If more detail is needed, I can provide.

2

First, your Quick model isn’t really coming in to play here since you are grabbing the QueryBuilder directly. That’s why your sqltype annotation isn’t doing anything.

When qb tries to infer a sqltype for an array it does so by checking all the values and using the inferred type if it is the same for all the values. If it is not the same for all the values it uses varchar. You can inspect your data you are trying to insert to see why it is not all numbers.

Finally, you can always override the sqltype inference in qb by passing a query param struct: { value = “1”, sqltype = “integer” }. Map your values into this struct format before passing it to insert.

3

@elpete This answers a number questions. Thank you!

I’ve not seen a way/syntax to specify the return format and datasource using Quick.

I can’t find an example for Quick. If it can be done, can you assist with syntax (or order to chain each together)? I believe I can specify the options in the get part, but I’ve not figured out how to set the return format.

QUICK:  
var query = db.where("id", id).get();

What is the Quick equivalent to this:

Query Builder:
var query = db.getQuickBuilder().getQb().where('id', id).setReturnFormat( 'array').get(options = { datasource:"prod" });