Change data type of query column

dev support
1

Is it possible to change the data type of a column after running a query?

I have a query that returns a list of location IDs and customer IDs and I do a query of query to aggregate the number of customers for each location. If I dump the metadata() for my query, it shows the Location field is TINYINT (although it is SMALLINT in my MySQL database, with values up to 148 currently). When I run my query of query function, I get the error “there is a problem to execute sql statement on query
Numeric value out of range” from lucee.runtime.db.HSQLDBHandler.__execute(HSQLDBHandler.java:379)

If I use querynew() to specify NUMERIC data in this column and load the same data, the issue goes away. If I keep the Location IDs below 128 there is no issue. Conversely, if I specify TINYINT in my querynew() the issue comes back. If I could specify the data type in my normal MySQL query, I should be able to solve the problem.

Lucee 5.2.5 and 5.3.3

<cfset register=querynew("location,customer","tinyint,numeric",{location:[110,128],customer:[14207,14288]})>
<cfquery name="report" dbtype="query">
  select location,count(customer) customers from register group by location
</cfquery>

Thanks,
Simon

2

I think you can just querysetcell on one row, with a value big enough and it will force the type conversion for the column.

I use this trick with mssql datasets coz QoQ still fails on joining on columns with numeric values

1 Like
3

Thanks Zac. I found I need to change to a VARCHAR field before changing it to a DOUBLE… setting a large number without the additional step left the field as TINYINT. Thanks for the suggestion.

<cfset register=querynew("location,customer","tinyint,numeric",{location:[110,128],customer:[14207,14288]})>
<cfset temp=register.location>
<cfset querysetcell(register,"location","abc",1)>
<cfset querysetcell(register,"location",temp,1)>
<cfquery name="report" dbtype="query">
  select location,count(customer) customers from register group by location
</cfquery>
1 Like