3.1.1.0 upgrade with SELECT COUNT(*) error

Quote Aaron:

I just upgraded to 3.1.1.0 today and republished my project. I didn't make any code changes. Now some db queries are not working at all. I've turned on debugging for mysql and have found completely incorrect sql statements in the generated code. No where in my code have I asked for it to run a select count statement but this is where the errors are.

I've checked my old (backed up project before upgrading) and this code was not in there.

Anyone else having this issue or know how to solve it?

Regards

Aaron

Quote Aaron:

Thanks for the suggestions guys.

I updated the entire project (F9). I also haven't modified the common.php file.

Funny thing is, I looked at the statement about the grids getting auto COUNT statements and the bug fix they posted on the updates page for CCS 3.1.1.0. Their bug fix said they fixed where it wouldn't make COUNT code if there were multiple select statements. In the issue mentioned here, I have a complex, multi-select statement and that is where CCS is messing up the COUNT statement. Interestingly I also have a very similar query on another page and it *wasn't* updated in this manner. It still does not have a COUNT statement auto-generated.

So, I'm thinking there is still a bug.

Aaron

Quote Aaron:

You mean the complex query? I have custom code in the page, but not dealing with what's going on. Or do you mean the COUNT (*) query it is making?

Aaron

  
SELECT  
Rx.rxControlCode,  
Person.nameLast,  
Person.nameFirst,  
Person.phoneHome,  
Person.socialSecurityNumber,  
Person.PersonID,  
physicianNameLast,  
rxPhysicianID,  
PracticeID,  
RxStatusHistory.statusChangeDate,  
RxStatusHistory.statusChangeTime,  
RxStatusHistory.nextActionDate,  
RxStatusHistory.nextActionTime,  
RxClaimID,  
rxStatusDescr,  
Rx.RxID,  
organizationName  
  
FROM  
Rx  
LEFT Join RxClaim ON (RxClaim.RxID = Rx.RxID)  
Inner Join Person ON (Person.PersonID = Rx.PatientID)  
Inner Join Organization ON (Organization.OrganizationID =Rx.PracticeID),  
  
( SELECT RxID, RxStatusHistory.RxStatusHistoryID,  RxStatusHistory.statusChangeDate, RxStatusID, nextActionDate  
  
    FROM RxStatusHistory,  
  
    (  
    SELECT SUBSTRING_INDEX(MAX(CONCAT(statusChangeDate, '-', RxStatusHistoryID)), '-', -1) AS RxSHID  
  
    FROM RxStatusHistory  
  
    GROUP BY RxID  
  
    ORDER BY RxStatusHistoryID ASC) AS RxSH  
  
    WHERE RxSH.RxSHID = RxStatusHistory.RxStatusHistoryID  
    ) AS RxSHMax,  
  
( SELECT DISTINCT  RxID, Person.nameLast AS physicianNameLast, Person.nameFirst AS physicianNameFirst  
  
    FROM Rx, Physician, Person  
  
    WHERE Rx.rxPhysicianID = Physician.PhysicianID  
  
    AND Physician.PersonID = Person.PersonID  
  
) AS TempPhysician  
  
Inner Join RxStatusHistory ON (RxStatusHistory.RxID = TempPhysician.RxID)  
Inner Join RxStatus ON (RxStatus.RxStatusID = RxStatusHistory.RxStatusID)  
  
WHERE RxSHMax.RxID = Rx.RxID  
AND TempPhysician.RxID = Rx.RxID  
AND RxSHMax.RxStatusHistoryID = RxStatusHistory.RxStatusHistoryID  
AND Rx.PatientID = Person.PersonID  
AND Person.nameLast LIKE '%{s_nameLast}%'  
AND Person.nameFirst LIKE '%{s_nameFirst}%'  
AND (  
        Person.socialSecurityNumber LIKE '%{s_socialSecurityNumber}%'  
        OR Person.socialSecurityNumber IS NULL  
    )  
AND Rx.rxControlCode LIKE '%{s_rxControlCode}%'  
AND (  
        RxStatusHistory.nextActionDate LIKE '%{s_nextActionDate}%'  
        OR RxStatusHistory.nextActionDate IS NULL  
    )  
AND physicianNameLast LIKE '%{s_physicianLast}%'  
AND rxStatusDescr LIKE '%{s_status}%'  
ORDER BY RxStatusHistory.statusChangeDate DESC  

  
SELECT DISTINCT  
  
Address.AddressID,   
Address.addressStreet,   
Address.addressBuilding,   
Address.addressSuite,   
Address.addressCity,   
Address.addressState,   
Address.addressPostalCode,   
AddressType.addressTypeName,  
  
Rx.rxControlCode,  
Person.nameLast,  
Person.nameFirst,  
Person.nameMiddle1,  
Person.dateOfBirth,  
Person.phoneHome,  
Person.phoneWork,  
Person.phoneCell,  
Person.emailAddressPrimary,  
Person.socialSecurityNumber,  
Person.PersonID,  
physicianNameLast,  
physicianNameFirst,  
rxPhysicianID,  
PracticeID,  
Rx.RxID,  
Organization.organizationName  
  
FROM  
Organization AS Org,   
PersonOrganization,   
PersonOrganizationAddress,   
Address,   
AddressType,  
AddressAddressType,   
Rx  
Inner Join Person ON (Person.PersonID = Rx.PatientID)  
Inner Join Organization ON (Organization.OrganizationID =Rx.PracticeID),  
  
( SELECT DISTINCT  RxID, Person.nameLast AS physicianNameLast, Person.nameFirst AS physicianNameFirst  
  
    FROM Rx, Physician, Person  
  
    WHERE Rx.rxPhysicianID = Physician.PhysicianID  
  
    AND Physician.PersonID = Person.PersonID  
  
) AS TempPhysician  
  
WHERE TempPhysician.RxID = Rx.RxID  
AND Rx.PatientID = Person.PersonID  
AND Person.PersonID = PersonOrganization.PersonID    
AND PersonOrganization.OrganizationID = Org.OrganizationID    
AND PersonOrganizationAddress.PersonOrganizationID = PersonOrganization.PersonOrganizationID    
AND PersonOrganizationAddress.AddressID = Address.AddressID    
AND AddressAddressType.AddressID = Address.AddressID    
AND AddressAddressType.AddressTypeID = AddressType.AddressTypeID  
AND Person.PersonID = {PersonID}   
AND Rx.rxControlCode = {rxControlCode}   

  
Database Error: You have an error in your SQL syntax;   
check the manual that corresponds to your MySQL server version for the   
right syntax to use near ') AS TempPhysician   
WHERE TempPhysician.RxID = Rx.RxID AND Rx.PatientID = Person' at line 7  

  
Debug: query = SELECT COUNT(*) FROM Rx, Physician, Person   
WHERE Rx.rxPhysicianID = Physician.PhysicianID   
AND Physician.PersonID = Person.PersonID ) AS TempPhysician   
WHERE TempPhysician.RxID = Rx.RxID AND Rx.PatientID = Person.PersonID   
AND Person.PersonID = PersonOrganization.PersonID   
AND PersonOrganization.OrganizationID = Org.OrganizationID   
AND PersonOrganizationAddress.PersonOrganizationID = PersonOrganization.PersonOrganizationID   
AND PersonOrganizationAddress.AddressID = Address.AddressID   
AND AddressAddressType.AddressID = Address.AddressID   
AND AddressAddressType.AddressTypeID = AddressType.AddressTypeID   
AND Person.PersonID = 63541   
AND Rx.rxControlCode = 0701311200005  

Quote Aaron:

Kevin,

Yep. Health information. Fun eh? :)

While I'd love to continue trouble shooting this, I just can't take the time to get this working with the latest version when I can at least deal with and know about the problems in 3.0.1.2. Installing and reinstalling isn't worth it for me right now. Every time I upgrade I have to jump through more hoops to get my projects working again. There shouldn't be any reason a properly formed query should have to be massaged to work with CCS.

I appreciate your suggestions. They were right on track with what I was thinking (dreading) I'd have to do as well. If I stumble upon the magic solution I'll be sure to post it here.

Thanks.

Aaron

Quote :

Please open ..\CodeChargeStudio3\Components\Databases\MySQL.xml files
And replace
supportSelectFromSelect="False"
with
supportSelectFromSelect="True"

Then re-start CCS and regenerate the page. CountSQL should be built in a different way and that should resolve the problem.

Quote Aaron:

I mentioned my version of mysql in the request for support so I have no idea if this is dependent on mysql version and/or capabilities.

I agree it needs to be documented somewhere. I find it odd that prior to this version upgrade some worked and others didn't. Anyway....

Thanks for the heads up on that one. Hope others find this thread and information useful.

Aaron