sql
  • leftbower

    leftbower

    05/20/2022, 11:49 PM
    I'm struggling with retrieving the correct data from a MySQL 8 db having two relevant tables. I want to get a list of all open jobs and jobs that closed today that have a specific
    process_id
    . First a
    jobs
    table which has a pk
    job_id
    (INT) and an associated
    process_id
    (INT). Then a
    jobs_log
    table that logs changes to a particular job's status and so has relevant
    job_id
    ,
    logdate
    (timestamp) and
    job_status
    (INT) fields. I want to query the db to get a recordset of all current job_ids with a specific
    process_id
    . In this case, "current" means the most recent
    job_status
    entry as long as: • that entry's
    job_status
    is < 2 OR • that entry's
    job_status
    is >= 2 but its most recent
    logdate
    is equal to today's date
    CURDATE()
    (i.e. it was changed to 2+ sometime today) The result should have the
    job_id
    , its current
    job_status
    and the
    logdate
    of that status change.
  • ryan

    ryan

    06/03/2022, 7:58 PM
    Hello all -Background I recently had to install MySQL 5.7 to run parallel with MySQL 8 due to differences in query results between the versions. I was successfully able to get everything set up in Lucee 5.3 to point to MySQL 5.7.Problem I am running a query of query and using a group by on only one field. It is actually grouping by month and year with the "monthly" field (ie values look like Mar'21 or Jun'22) without using an aggregate function on any fields in the SELECT clause. It works perfectly on my local machine. Example:
    SELECT 		marketValue,
    			marketPercent,
    			d_as_at_date,
    			monthly
    FROM 		qMonthlyPortfolioSum
    GROUP BY 	monthly
    ORDER BY	d_as_at_date
    My colleague is stating that he receives the following error:
    Not in aggregate function or group by clause: org.hsqldb.Expression@6b6ba733 in statement [SELECT marketValue, marketPercent, d_as_at_date, monthly FROM qMonthlyPortfolioSum GROUP BY monthly ORDER BY d_as_at_date]
    I cannot figure out why he is receiving an error when I am not on the query of query when we both have the same version of MySQL. I then looked in the Lucee Admin and noticed that the driver installed is version 8.0.28. I attempted to down grade MySQL Application to the highest version 5 available, which is version 5.1.40 and also version 6, but anything lower than 8.0.19 errors when validating the datasource in Lucee Admin. There is not a version 5.7 available to choose in the list of MySQL drivers for Lucee. I have even put the query of query together with the real query and executed the script against MySQL 5.7 the DBMS and it runs fine outside of Lucee as well. Would anyone have any sort of ideas as to the discrepancies between the errors on the local environments? Is it possible that I may not be running MySQL in some kind of restrictive mode and that maybe my colleague might have something restricted that would cause an error on his side?
  • ryan

    ryan

    06/16/2022, 3:18 PM
    Has anyone seen this error before?
    Message	
    string	param [NL] not found
    StackTrace	
    string	lucee.runtime.exp.ApplicationException: param [NL] not found at lucee.runtime.tag.util.QueryParamConverter.get(QueryParamConverter.java:241) at lucee.runtime.tag.util.QueryParamConverter.convert(QueryParamConverter.java:190) at lucee.runtime.tag.util.QueryParamConverter.convert(QueryParamConverter.java:73) at lucee.runtime.tag.Query._doEndTag(Query.java:592) at lucee.runtime.tag.Query.doEndTag(Query.java:565) at lucee.runtime.functions.query.QueryExecute.call(QueryExecute.java:86) at modules.labelview.labelview_cfc$cf$ei.udfCall(/modules/labelview/labelview.cfc:114) at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112) at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350) at lucee.runtime.type.UDFImpl.call(UDFImpl.java:223) at lucee.runtime.type.scope.UndefinedImpl.call(UndefinedImpl.java:786) at lucee.runtime.util.VariableUtilImpl.callFunctionWithoutNamedValues(VariableUtilImpl.java:787) at lucee.runtime.PageContextImpl.getFunction(PageContextImpl.java:1762) at modules.labelview.labelview_cfc$cf$ei.udfCall(/modules/labelview/labelview.cfc:47) at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112) at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350) at lucee.runtime.type.UDFImpl.callWithNamedValues(UDFImpl.java:213) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:664) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:593) at lucee.runtime.ComponentImpl.callWithNamedValues(ComponentImpl.java:1960) at lucee.runtime.ComponentSpecificAccess.callWithNamedValues(ComponentSpecificAccess.java:156) at lucee.runtime.ComponentPageImpl.callWDDX(ComponentPageImpl.java:635) at lucee.runtime.ComponentPageImpl.call(ComponentPageImpl.java:190) at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:1012) at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:935) at lucee.runtime.listener.ModernAppListener._onRequest(ModernAppListener.java:219) at lucee.runtime.listener.MixedAppListener.onRequest(MixedAppListener.java:44) at lucee.runtime.PageContextImpl.execute(PageContextImpl.java:2475) at lucee.runtime.PageContextImpl._execute(PageContextImpl.java:2465) at lucee.runtime.PageContextImpl.executeCFML(PageContextImpl.java:2436) at lucee.runtime.engine.Request.exe(Request.java:45) at lucee.runtime.engine.CFMLEngineImpl._service(CFMLEngineImpl.java:1198) at lucee.runtime.engine.CFMLEngineImpl.serviceCFML(CFMLEngineImpl.java:1144) at lucee.loader.engine.CFMLEngineWrapper.serviceCFML(CFMLEngineWrapper.java:97) at lucee.loader.servlet.CFMLServlet.service(CFMLServlet.java:51) at javax.servlet.http.HttpServlet.service(HttpServlet.java:590) at io.undertow.servlet.handlers.ServletHandler.handleRequest(ServletHandler.java:74) at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:129) at org.cfmlprojects.regexpathinfofilter.RegexPathInfoFilter.doFilter(RegexPathInfoFilter.java:47) at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61) at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131) at org.tuckey.web.filters.urlrewrite.RuleChain.handleRewrite(RuleChain.java:176) at org.tuckey.web.filters.urlrewrite.RuleChain.doRules(RuleChain.java:145) at org.tuckey.web.filters.urlrewrite.UrlRewriter.processRequest(UrlRewriter.java:92) at org.tuckey.web.filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java:405) at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61) at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131) at io.undertow.servlet.handlers.FilterHandler.handleRequest(FilterHandler.java:84) at io.undertow.servlet.handlers.security.ServletSecurityRoleHandler.handleRequest(ServletSecurityRoleHandler.java:62) at io.undertow.servlet.handlers.ServletChain$1.handleRequest(ServletChain.java:68) at io.undertow.servlet.handlers.ServletDispatchingHandler.handleRequest(ServletDispatchingHandler.java:36) at io.undertow.servlet.handlers.RedirectDirHandler.handleRequest(RedirectDirHandler.java:68) at io.undertow.servlet.handlers.security.SSLInformationAssociationHandler.handleRequest(SSLInformationAssociationHandler.java:117) at io.undertow.servlet.handlers.security.ServletAuthenticationCallHandler.handleRequest(ServletAuthenticationCallHandler.java:57) at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43) at io.undertow.security.handlers.AbstractConfidentialityHandler.handleRequest(AbstractConfidentialityHandler.java:46) at io.undertow.servlet.handlers.security.ServletConfidentialityConstraintHandler.handleRequest(ServletConfidentialityConstraintHandler.java:64) at io.undertow.security.handlers.AuthenticationMechanismsHandler.handleRequest(AuthenticationMechanismsHandler.java:60) at io.undertow.servlet.handlers.security.CachedAuthenticatedSessionHandler.handleRequest(CachedAuthenticatedSessionHandler.java:77) at io.undertow.security.handlers.AbstractSecurityContextAssociationHandler.handleRequest(AbstractSecurityContextAssociationHandler.java:43) at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43) at io.undertow.servlet.handlers.SendErrorPageHandler.handleRequest(SendErrorPageHandler.java:52) at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43) at io.undertow.servlet.handlers.ServletInitialHandler.handleFirstRequest(ServletInitialHandler.java:275) at io.undertow.servlet.handlers.ServletInitialHandler.access$100(ServletInitialHandler.java:79) at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:134) at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:131) at io.undertow.servlet.core.ServletRequestContextThreadSetupAction$1.call(ServletRequestContextThreadSetupAction.java:48) at io.undertow.servlet.core.ContextClassLoaderSetupAction$1.call(ContextClassLoaderSetupAction.java:43) at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44) at io.undertow.servlet.handlers.ServletInitialHandler.dispatchRequest(ServletInitialHandler.java:255) at io.undertow.servlet.handlers.ServletInitialHandler.access$000(ServletInitialHandler.java:79) at io.undertow.servlet.handlers.ServletInitialHandler$1.handleRequest(ServletInitialHandler.java:100) at io.undertow.server.Connectors.executeRootHandler(Connectors.java:387) at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:852) at org.jboss.threads.ContextClassLoaderSavingRunnable.run(ContextClassLoaderSavingRunnable.java:35) at org.jboss.threads.EnhancedQueueExecutor.safeRun(EnhancedQueueExecutor.java:2019) at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.doRunTask(EnhancedQueueExecutor.java:1558) at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1449) at org.xnio.XnioWorker$WorkerThreadFactory$1$1.run(XnioWorker.java:1280) at java.base/java.lang.Thread.run(Unknown Source)
  • websolete

    websolete

    06/16/2022, 3:21 PM
    if you paste the query sql it may be obvious what's causing it
  • ryan

    ryan

    06/16/2022, 3:46 PM
    Thanks @websolete, I figured out the issue.
  • bhartsfield

    bhartsfield

    06/16/2022, 4:06 PM
    Can we just rename this channel to "Websolet's SQL Channel"
  • websolete

    websolete

    06/16/2022, 4:15 PM
    My name is Websolete, and I approve this message.
  • ryan

    ryan

    06/16/2022, 4:47 PM
    LOL
  • ryan

    ryan

    06/16/2022, 4:49 PM
    OK, I thought I was going to be able to fix this "NL" issue. I have an straight INSERT INTO statement that has a import column name of
    [MVI_PRODUCT3+N:NL:TJ:TH:TF:TA:TF:T]
    I cannot change this, unfortunately. However, the insert works fine this way in a DBMS, just not in a queryexecute() function. It looks as though this is bombing on the
    :NL
    where queryexecute is thinking this is a query parameter. Is there any way of escaping colons so that queryexecute understands not to interpret it?
  • ryan

    ryan

    06/16/2022, 4:56 PM
    Ah, it may simply be the
    \
    . Going to test this
  • Scott Steinbeck

    Scott Steinbeck

    06/16/2022, 4:58 PM
    i have to escape
    :
    with
    ::
    in a queryExecute since it CF thinks its a placeholder
  • ryan

    ryan

    06/16/2022, 4:59 PM
    OK, thanks @Scott Steinbeck. I just tried
    \
    and it didn't work as suggested in a post. Will try
    ::
    now.
  • Scott Steinbeck

    Scott Steinbeck

    06/16/2022, 4:59 PM
    in postgres you can cast
    cost :: numeric
    which in my queryExecute becomes
    cost :::: numeric
  • ryan

    ryan

    06/16/2022, 5:02 PM
    The
    ::
    worked like a charm! Thanks, again, @Scott Steinbeck!
  • ryan

    ryan

    06/20/2022, 1:29 AM
    Regarding a previous chat about escaping colons
    :
    in a queryExecute method: What I thought was working was true, which happened to be when I was escaping column names that had
    :
    in them, but then when attempting to escape
    :
    the same way in the VALUES of an INSERT INTO statement, it seemed to escape them, but never converted a
    ::
    back to
    :
    . Thinking my code was faulty somewhere (still could be, but I don't honestly think it is), I spent quite a bit of time trying to figure out how to make it work, but wound up with an alternative by adding an update statement at the end to replace
    ::
    with
    :
    . Escaping single quotes work in VALUES properly, however.
  • Scott Steinbeck

    Scott Steinbeck

    06/20/2022, 8:08 PM
    @zackster I posted this here since it is related to DB but knowing it is lucee specific. I’ve noticed there are certain data types from Postgres that instead of being returned as values, get returned as
    org.postgresql.util.PGobject
    which then needs to have
    .toString()
    called on them to actually print the value. (eg.
    json
    jsonb
    and recently i found
    citext
    (case insensitive text) This seems like an additional step that could be handled before the query is returned. Is it possible to convert this somewhere before the query is returned?
  • Brian

    Brian

    07/18/2022, 4:16 PM
    I'm trying to use CommandBox to install a SQL Server connector for my ACF2021 instance and I get the following:
    CommandBox> install sqlserver
    × | Installing package [forgebox:sqlserver]
    |------------------------------------------------------
    | Verifying package 'sqlserver' in forgebox, please wait...
    | Error getting ForgeBox entry [sqlserver]  The entry slug sent is invalid or does not exist
    |------------------------------------------------------
    Is there a different source or am I doing something wrong?
  • Brian

    Brian

    07/19/2022, 7:25 PM
    An update to my previous question. Can anyone point me to instructions on how to install the Microsoft JDBC Driver 4.0 for SQL Server driver for CommandBox?
  • Brian

    Brian

    07/20/2022, 12:29 PM
    Is there anywhere I an add CF related documentation for others? Things on here eventually disappear.
  • Rodney

    Rodney

    07/20/2022, 12:35 PM
    You can always submit a pull request to cfdocs.org.
  • Rodney

    Rodney

    07/20/2022, 12:36 PM
    Or Ortus if it's CommandBox specific.
  • g

    gsr

    09/01/2022, 3:46 PM
    i am getting this error in sql [Macromedia][SQLServer JDBC Driver][SQLServer]Operand type clash: varchar is incompatible with IDList IDlist is a readonly attribute defined as: @searchList IDList readonly
  • g

    gsr

    09/01/2022, 3:46 PM
    from front i am using cfprocparam cfsqltype='varchar' to that
  • g

    gsr

    09/01/2022, 6:54 PM
    in my sql how does my case statement is wrong
    delete from dbo.errors  
    	where 
    		CASE 
    			WHEN @typeofcolumn='e'  THEN 'errorid' = @bugid ELSE NULL END
    		CASE 
    			WHEN @typeofcolumn='b'  THEN 'bugid' = @bugid ELSE NULL END
    	return;
    trying to use in the procedure
  • mk

    mk

    09/07/2022, 9:04 PM
    i have a spx which is returning me int values as itis declated as; declare @error int
    its ok to be as int, but in a case where i have to return multiple string values, i am trying to cast it to varchar so i can get the error
    set @error = 'my invalid error message'
    so i am trying like
    set cast(@error as varchar(100)) = 'my invalid error message'
    but i am getting a syntax error on the cast, am i doing anything wrong
  • John Wilson

    John Wilson

    09/20/2022, 6:57 PM
    SQL challenge
    I need to create blended benchmarks which are made up of components and weights. ( details here are not important ) If a blend is already in the system, I want to retrieve its benchmarkID. If not, I'm going to create a set of new records that make up the blend. There may be many blends that use the same components, but with differing weights. Given the following array, what do you suppose is the most efficient/elegant way to find a match? A single sql query would be ideal, but using qb.get() (or queryexecute() returning an array of structs) and arrayEach() is acceptable too.
    //  the table name is benchmarkBlends and the rows we're trying to match look like this:
    
    [
    	{
    		"benchmarkID": 46,
    		"componentID": 11,
    		"pctWeight": 52.2
    	},
    	{
    		"benchmarkID": 46,
    		"componentID": 13,
    		"pctWeight": 20.0
    	},
    	{
    		"benchmarkID": 46,
    		"componentID": 22,
    		"pctWeight": 27.8
    	}
    ]
    
    // the incoming blend looks like this:
    [
    	{
    		"componentID": 11,
    		"pctWeight": 52.2
    	},
    	{
    		"componentID": 13,
    		"pctWeight": 20.0
    	},
    	{
    		"componentID": 22,
    		"pctWeight": 27.8
    	}
    ]
  • salted

    salted

    10/16/2022, 4:21 AM
    Has anyone used sql 2017 or earlier with cf 2021 or later?
  • j

    Jim Priest

    12/06/2022, 2:39 PM
    Getting started with Liquibase: https://thecrumb.com/posts/2022-12-05-getting-started-with-liquibase/ In the next post I'll blog about how I built a task runner in CommandBox to make running Liquibase easier in our weird environment.