Hello,
using: SmartClient Version: v8.3p_2013-01-09/PowerEdition Deployment (built 2013-01-09)
I have a fairly complex SQL query, written with the SQL templating system like so:
This query populates a ListGrid.
The problem is that, it will populate the 0,75 start+endrow values, but upon scrolling it doesn't increase this amount.
Is it due to the structure of the query, or should this be working fine?
Also consider the log, maybe it gives away some information:
using: SmartClient Version: v8.3p_2013-01-09/PowerEdition Deployment (built 2013-01-09)
I have a fairly complex SQL query, written with the SQL templating system like so:
Code:
<customSQL> <![CDATA[
SELECT * FROM (
SELECT
Lecture.Lecture_id as Lecture_id,
Lecture_presence_Student.presence_id as presence_id,
Student.SaasCustomer_id as SaasCustomer_id,
'Les' as unifiedType,
Student.firstname as unifiedFirstname,
Student.tussenvoegsel as unifiedTussenvoegsel,
Student.surname as unifiedSurname,
Lecture.lectureName,
Lecture.startDate,
Lecture.endDate,
Lecture_presence_Student.status as unifiedStatus,
Lecture_presence_Student.reason as unifiedReason,
Company.companyName as unifiedCompanyName,
CourseEdition.editionName as unifiedEditionName,
ContactPerson.ContactPerson_id as ContactPerson_id,
Student.Student_id as Student_id
FROM
Lecture_presence_Student
INNER JOIN Student ON Lecture_presence_Student.Student_id=Student.Student_id
INNER JOIN Lecture ON Lecture.Lecture_id = Lecture_presence_Student.Lecture_id
INNER JOIN CourseEdition ON Lecture.CourseEdition_id = CourseEdition.CourseEdition_id
LEFT OUTER JOIN ContactPerson ON ContactPerson.ContactPerson_id = Student.ContactPerson_id
LEFT OUTER JOIN Company ON Student.Company_id = Company.Company_id
UNION
SELECT
ExamMoment.ExamMoment_id as Lecture_id,
-1*Exam_participants_Student.participants_id as presence_id,
Student.SaasCustomer_id as SaasCustomer_id,
'Examen' as unifiedType,
Student.firstname as unifiedFirstname,
Student.tussenvoegsel as unifiedTussenvoegsel,
Student.surname as unifiedSurname,
ExamMoment.emName as lectureName,
ExamMoment.examDateStart as startDate,
ExamMoment.examDateEnd as endDate,
ExamParticipationToExamMoment.epPresence as unifiedStatus,
ExamParticipationToExamMoment.epRemark_ as unifiedReason,
Company.companyName as unifiedCompanyName,
CourseEdition.editionName as unifiedEditionName,
ContactPerson.ContactPerson_id as ContactPerson_id,
Student.Student_id as Student_id
FROM
ExamParticipationToExamMoment
INNER JOIN Exam_participants_Student ON Exam_participants_Student.participants_id = ExamParticipationToExamMoment.participants_id
INNER JOIN Exam ON Exam_participants_Student.Exam_id = Exam.Exam_id
INNER JOIN ExamMoment ON ExamMoment.ExamMoment_id = ExamParticipationToExamMoment.ExamMoment_id
INNER JOIN Block ON Exam.Block_id = Block.Block_id
INNER JOIN Student ON Exam_participants_Student.Student_id = Student.Student_id
LEFT OUTER JOIN ContactPerson ON ContactPerson.ContactPerson_id = Student.ContactPerson_id
LEFT OUTER JOIN Company ON Student.Company_id = Company.Company_id
LEFT OUTER JOIN CourseEdition ON CourseEdition.CourseEdition_id = ExamMoment.CourseEdition_id
) as Lecture_presence_Student
WHERE ($defaultWhereClause) AND Lecture_presence_Student.unifiedStatus <> 'onverwerkt'
#if ($criteria.ContactPerson > 0) AND Lecture_presence_Student.ContactPerson_id=$criteria.ContactPerson_id #end
ORDER BY $defaultOrderClause
#if ($dsRequest.getEndRow() > -1)
LIMIT $dsRequest.getStartRow(), $dsRequest.getEndRow()
#end
]]></customSQL>
The problem is that, it will populate the 0,75 start+endrow values, but upon scrolling it doesn't increase this amount.
Is it due to the structure of the query, or should this be working fine?
Also consider the log, maybe it gives away some information:
Code:
=== 2013-01-21 11:10:52,779 [0-16] DEBUG RPCManager - Request #1 (DSRequest) payload: {
criteria:{
},
operationConfig:{
dataSource:"Lecture_presence_Student",
operationType:"fetch",
textMatchStyle:"substring"
},
startRow:0,
endRow:75,
componentId:"isc_StudentPresenceReport_PresenceGrid_3",
appID:"builtinApplication",
operation:"PresenceGridReport",
oldValues:{
}
}
=== 2013-01-21 11:10:52,824 [0-16] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
=== 2013-01-21 11:10:52,824 [0-16] DEBUG DeclarativeSecurity - DataSource Lecture_presence_Student is not in the pre-checked list, processing...
=== 2013-01-21 11:10:52,825 [0-16] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
=== 2013-01-21 11:10:52,825 [0-16] DEBUG DeclarativeSecurity - DataSource Lecture_presence_Student is not in the pre-checked list, processing...
=== 2013-01-21 11:10:52,825 [0-16] DEBUG AppBase - [builtinApplication.PresenceGridReport] No userTypes defined, allowing anyone access to all operations for this application
=== 2013-01-21 11:10:52,825 [0-16] DEBUG AppBase - [builtinApplication.PresenceGridReport] No public zero-argument method named '_PresenceGridReport' found, performing generic datasource operation
=== 2013-01-21 11:10:52,826 [0-16] INFO SQLDataSource - [builtinApplication.PresenceGridReport] Performing fetch operation with
criteria: {SaasCustomer_id:1} values: {SaasCustomer_id:1}
=== 2013-01-21 11:10:52,834 [0-16] INFO SQLDataSource - [builtinApplication.PresenceGridReport] Executing SQL query on 'Mysql': SELECT * FROM (
SELECT
Lecture.Lecture_id as Lecture_id,
Lecture_presence_Student.presence_id as presence_id,
Student.SaasCustomer_id as SaasCustomer_id,
'Les' as unifiedType,
Student.firstname as unifiedFirstname,
Student.tussenvoegsel as unifiedTussenvoegsel,
Student.surname as unifiedSurname,
Lecture.lectureName,
Lecture.startDate,
Lecture.endDate,
Lecture_presence_Student.status as unifiedStatus,
Lecture_presence_Student.reason as unifiedReason,
Company.companyName as unifiedCompanyName,
CourseEdition.editionName as unifiedEditionName,
ContactPerson.ContactPerson_id as ContactPerson_id,
Student.Student_id as Student_id
FROM
Lecture_presence_Student
INNER JOIN Student ON Lecture_presence_Student.Student_id=Student.Student_id
INNER JOIN Lecture ON Lecture.Lecture_id = Lecture_presence_Student.Lecture_id
INNER JOIN CourseEdition ON Lecture.CourseEdition_id = CourseEdition.CourseEdition_id
LEFT OUTER JOIN ContactPerson ON ContactPerson.ContactPerson_id = Student.ContactPerson_id
LEFT OUTER JOIN Company ON Student.Company_id = Company.Company_id
UNION
SELECT
ExamMoment.ExamMoment_id as Lecture_id,
-1*Exam_participants_Student.participants_id as presence_id,
Student.SaasCustomer_id as SaasCustomer_id,
'Examen' as unifiedType,
Student.firstname as unifiedFirstname,
Student.tussenvoegsel as unifiedTussenvoegsel,
Student.surname as unifiedSurname,
ExamMoment.emName as lectureName,
ExamMoment.examDateStart as startDate,
ExamMoment.examDateEnd as endDate,
ExamParticipationToExamMoment.epPresence as unifiedStatus,
ExamParticipationToExamMoment.epRemark_ as unifiedReason,
Company.companyName as unifiedCompanyName,
CourseEdition.editionName as unifiedEditionName,
ContactPerson.ContactPerson_id as ContactPerson_id,
Student.Student_id as Student_id
FROM
ExamParticipationToExamMoment
INNER JOIN Exam_participants_Student ON Exam_participants_Student.participants_id = ExamParticipationToExamMoment.participants_id
INNER JOIN Exam ON Exam_participants_Student.Exam_id = Exam.Exam_id
INNER JOIN ExamMoment ON ExamMoment.ExamMoment_id = ExamParticipationToExamMoment.ExamMoment_id
INNER JOIN Block ON Exam.Block_id = Block.Block_id
INNER JOIN Student ON Exam_participants_Student.Student_id = Student.Student_id
LEFT OUTER JOIN ContactPerson ON ContactPerson.ContactPerson_id = Student.ContactPerson_id
LEFT OUTER JOIN Company ON Student.Company_id = Company.Company_id
LEFT OUTER JOIN CourseEdition ON CourseEdition.CourseEdition_id = ExamMoment.CourseEdition_id
) as Lecture_presence_Student
WHERE (('1'='1')) AND Lecture_presence_Student.unifiedStatus <> 'onverwerkt'
LIMIT 0, 75
=== 2013-01-21 11:10:52,834 [0-16] DEBUG PoolableSQLConnectionFactory - [builtinApplication.PresenceGridReport] Executing pingTest 'select 1 from dual' on connection 824381750
=== 2013-01-21 11:10:52,914 [0-16] DEBUG SQLConnectionManager - [builtinApplication.PresenceGridReport] Returning borrowed connection '824381750'
=== 2013-01-21 11:10:52,914 [0-16] DEBUG SQLDriver - [builtinApplication.PresenceGridReport] About to execute SQL query in 'Mysql' using connection '824381750'
=== 2013-01-21 11:10:52,914 [0-16] INFO SQLDriver - [builtinApplication.PresenceGridReport] Executing SQL query on 'Mysql': SELECT * FROM (
SELECT
Lecture.Lecture_id as Lecture_id,
Lecture_presence_Student.presence_id as presence_id,
Student.SaasCustomer_id as SaasCustomer_id,
'Les' as unifiedType,
Student.firstname as unifiedFirstname,
Student.tussenvoegsel as unifiedTussenvoegsel,
Student.surname as unifiedSurname,
Lecture.lectureName,
Lecture.startDate,
Lecture.endDate,
Lecture_presence_Student.status as unifiedStatus,
Lecture_presence_Student.reason as unifiedReason,
Company.companyName as unifiedCompanyName,
CourseEdition.editionName as unifiedEditionName,
ContactPerson.ContactPerson_id as ContactPerson_id,
Student.Student_id as Student_id
FROM
Lecture_presence_Student
INNER JOIN Student ON Lecture_presence_Student.Student_id=Student.Student_id
INNER JOIN Lecture ON Lecture.Lecture_id = Lecture_presence_Student.Lecture_id
INNER JOIN CourseEdition ON Lecture.CourseEdition_id = CourseEdition.CourseEdition_id
LEFT OUTER JOIN ContactPerson ON ContactPerson.ContactPerson_id = Student.ContactPerson_id
LEFT OUTER JOIN Company ON Student.Company_id = Company.Company_id
UNION
SELECT
ExamMoment.ExamMoment_id as Lecture_id,
-1*Exam_participants_Student.participants_id as presence_id,
Student.SaasCustomer_id as SaasCustomer_id,
'Examen' as unifiedType,
Student.firstname as unifiedFirstname,
Student.tussenvoegsel as unifiedTussenvoegsel,
Student.surname as unifiedSurname,
ExamMoment.emName as lectureName,
ExamMoment.examDateStart as startDate,
ExamMoment.examDateEnd as endDate,
ExamParticipationToExamMoment.epPresence as unifiedStatus,
ExamParticipationToExamMoment.epRemark_ as unifiedReason,
Company.companyName as unifiedCompanyName,
CourseEdition.editionName as unifiedEditionName,
ContactPerson.ContactPerson_id as ContactPerson_id,
Student.Student_id as Student_id
FROM
ExamParticipationToExamMoment
INNER JOIN Exam_participants_Student ON Exam_participants_Student.participants_id = ExamParticipationToExamMoment.participants_id
INNER JOIN Exam ON Exam_participants_Student.Exam_id = Exam.Exam_id
INNER JOIN ExamMoment ON ExamMoment.ExamMoment_id = ExamParticipationToExamMoment.ExamMoment_id
INNER JOIN Block ON Exam.Block_id = Block.Block_id
INNER JOIN Student ON Exam_participants_Student.Student_id = Student.Student_id
LEFT OUTER JOIN ContactPerson ON ContactPerson.ContactPerson_id = Student.ContactPerson_id
LEFT OUTER JOIN Company ON Student.Company_id = Company.Company_id
LEFT OUTER JOIN CourseEdition ON CourseEdition.CourseEdition_id = ExamMoment.CourseEdition_id
) as Lecture_presence_Student
WHERE (('1'='1')) AND Lecture_presence_Student.unifiedStatus <> 'onverwerkt'
LIMIT 0, 75
=== 2013-01-21 11:10:53,147 [0-16] INFO DSResponse - [builtinApplication.PresenceGridReport] DSResponse: List with 75 items
=== 2013-01-21 11:10:53,386 [0-16] DEBUG SQLConnectionManager - About to close PoolableConnection with hashcode "824381750"
=== 2013-01-21 11:10:53,386 [0-16] DEBUG PoolableSQLConnectionFactory - Executing pingTest 'select 1 from dual' on connection 824381750
=== 2013-01-21 11:10:53,409 [0-16] DEBUG RPCManager - Content type for RPC transaction: text/plain; charset=UTF-8
=== 2013-01-21 11:10:53,409 [0-16] DEBUG RPCManager - DMI response, dropExtraFields: true
=== 2013-01-21 11:10:53,414 [0-16] INFO Compression - /generatedcode/sc/IDACall: 23834 -> 1474 bytes
Comment