Quick ORM - Datatype not being accepted when inserting with a struct in an array element

Communities Quick ORM
1

I ran into an issue and wanted to see if it can be cleared up.

Quick ORM is implemented with SQL Server as the grammar.

I have a model file with a column that is setup like so:

property name="columnXYZ" sqltype="CF_SQL_CHAR";

In the database the columnXYZ is defined as a datatype UNIQUEIDENTIFIER. In the ACF docs the equivalent datatype choice are: cf_sql_char for SQL Server datatypes of char, nchar, uniqueidentifier.

However, I attempt insert a row into the table, the error returned is: “conversion from char to uniqueindentifier failed”

I tried setting the property sqltype for the column in model file to CF_SQL_VARCHAR and CF_SQL_IDSTAMP and the issue was still the same. The error stated it was unable to convert.

I removed the property for that column in the model file and the insert completed without issue.

Can you comment if this is a bug or if I’m not using the correct syntax?

Here’s my function in the service file that returns the array of structs I want to insert into the database:

function getRegTypeProd(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’s my insert function in the service file:

The “fields” argument is an array of structs from the function above.

function insertFromProdToDev(fields = []){

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

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

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

    }

Any explanation/assistance would be very much appreciated.

2

@inLeague might be able to share some wisdom about unique identifiers in Quick and SQL Server.

3

We use sqltype="idstamp" for uniqueidentifier fields in SQL Server.

The only time we get a ‘conversion to uniqueidentifier failed’ error is if we try and SELECT or INSERT or UPDATE a record based on an invalid GUID.

In getRegTypeProd above, I don’t think query is every going to be null, is it? An empty QB query is an empty array if you’re calling it with .get() – if you want a nullish value you’d want .first() or .findOrFail()

4

Thank you for the insights. I can confirm that in most cases the field will be empty (which I would say is an invalid GUID).

To the second point, yes I would agree that it would not be null. This is an artifact as the function was copied and then modified for this and I missed revising it.

Side note: I was originally doing a lookup with Quick to return a list of events to populate a select element. When the loop executed, it was somewhat slow. When I switched it to QB, set the return format, and then ran the loop, it was much faster.

For example:

var sourceEvent = db.getQuickBuilder().getQb().orderBy("eventTitle").setReturnFormat( 'query').get("event_key, eventTitle, eventCode", { datasource:"prod" });

vs,

var sourceEvent = db.orderBy("eventTitle").get("event_key, eventTitle, eventCode");

The loop, for the first it works just the same as queryExecute, but the second I have to do the loop differently in that the value must be obtained by using getColumnName() vs query.columnName.

Is this expected or am I not doing something correct?

Thank you!

5

That is expected. When you ‘drop to QB’ (getQuickBUilder(), getQB(), or just using .asQuery() which is the preferred way now) then CF under the hood is running queryExecute and arranging an array of structs for you.

When you use .get(), you are instantiating one Quick entity per row returned. This is very expensive. Less expensive than it used to be but still always going to be a lot slower than returning an array of structs. This is mostly due to Wirebox and the overhead associated with wiring up each individual entity.

Prior to Quick 7, the big reason to pay this cost was because you got the benefit of eager loading if you used Quick entities versus an array of structs (as QB would not stitch together the results into an array of deep structs). Now it will, so the only reason to prefer Quick entities is if you desperately need the CF object … but the performance gains of .asQuery() are such that it now makes more sense to push business logic off of the model and into a service layer.