|
| [WM17] - Why this SQL is giving Error |
| Iniciado por guest, 15,may. 2015 14:25 - 11 respuestas |
| |
| | | |
|
| |
| Publicado el 15,mayo 2015 - 14:25 |
I have been banging my head for 4 day in trying to solve this problem. As I have not been able to solve this problem I am posting it here for assistance.
I have following data structure
-- Creating the attendence table CREATE TABLE "attendence" ( "attendence_id" INTEGER PRIMARY KEY , "attendence_standard_id" INTEGER , "attendence_division_id" INTEGER , "attendence_subject_id" INTEGER , "attendence_student_id" INTEGER , "attendence_date" DATE , "attendence_present_absent" SMALLINT , "attendence_lecture_id" INTEGER ); -- Creating the division_master table CREATE TABLE "division_master" ( "division_master_id" INTEGER PRIMARY KEY , "division_master_name" VARCHAR(50) ); -- Creating the enrollment_details table CREATE TABLE "enrollment_details" ( "enrollment_details_id" INTEGER PRIMARY KEY , "enrollment_details_student_id" INTEGER , "enrollment_details_standard_id" INTEGER , "enrollment_details_subject_id" INTEGER , "enrollment_details_division_id" INTEGER , "enrollment_details_Serial_no" VARCHAR(50) , "enrollment_details_year_id" INTEGER ); -- Creating the standard_master table CREATE TABLE "standard_master" ( "standard_master_id" INTEGER PRIMARY KEY , "standard_master_name" VARCHAR(50) ); -- Creating the student_master table CREATE TABLE "student_master" ( "student_master_id" INTEGER PRIMARY KEY , "student_master_roll_no" VARCHAR(50) , "student_master_fname" VARCHAR(50) , "student_master_mname" VARCHAR(50) , "student_master_sname" VARCHAR(50) , "student_master_mobile_no" VARCHAR(15) , "student_master_email_id" VARCHAR(200) , "student_master_dob" DATE , "student_master_gender" INTEGER , "student_master_fees_paid" BIT , "student_master_std_id" INTEGER , "student_master_div_id" INTEGER , "standard_master_fees_paid" BIT ); CREATE INDEX "WDIDX_student_master_student_master_roll_no" ON "student_master" ("student_master_roll_no"); CREATE INDEX "WDIDX_student_master_student_master_fnamestudent_master_mnamestudent_master_sname" ON "student_master" ("student_master_fname","student_master_mname","student_master_sname"); -- Creating the subject_master table CREATE TABLE "subject_master" ( "subject_master_id" INTEGER PRIMARY KEY , "subject_master_name" VARCHAR(50) );
I am using following SQL Query to retrieve Attendance of student
SELECT attendence.attendence_id AS attendence_id, attendence.attendence_standard_id AS attendence_standard_id, attendence.attendence_division_id AS attendence_division_id, attendence.attendence_subject_id AS attendence_subject_id, attendence.attendence_date AS attendence_date, attendence.attendence_student_id AS attendence_student_id, attendence.attendence_present_absent AS attendence_present_absent, student_master.student_master_roll_no AS student_master_roll_no, student_master.student_master_fname AS student_master_fname, student_master.student_master_mobile_no AS student_master_mobile_no, student_master.student_master_email_id AS student_master_email_id FROM student_master, attendence WHERE attendence.attendence_student_id = student_master.student_master_id AND ( attendence.attendence_date BETWEEN {ParamFromDate} AND {ParamToDate} AND attendence.attendence_student_id = {ParamStuID} ) ORDER BY student_master_roll_no ASC Here is the WL code
I is int = 1 //First Validate Data IF EDT_FromDate > EDT_ToDate THEN Error("Please select proper dates","From Date cannot be greater than To Date") RETURN END LooperDeleteAll(LOOP_Attendence) QRY_StudentAttendenceView.ParamFromDate = EDT_FromDate QRY_StudentAttendenceView.ParamToDate = EDT_ToDate QRY_StudentAttendenceView.ParamStuID = 1 //StudentID HExecuteQuery(QRY_StudentAttendenceView) I = 1 IF HNbRec(QRY_StudentAttendenceView) > 0 THEN HReadFirst(QRY_StudentAttendenceView) LooperAdd(LOOP_Attendence) LOOP_Attendence.ATT_AttendenceID = QRY_AttendenceOfSelectedStudent.attendence_id //<== This gives Error (Null Exception or some funny message like Field name not found in Query used, etc.) LOOP_Attendence.ATT_Is_Present = QRY_AttendenceOfSelectedStudent.present_absent //<== This also gives Error //Even if we try to use Info it gives error //.... other code removed for brevity ELSE Error("Cannot find students as per selected Date Range") END //IF HNbRec(QRY_StudentAttendence) > 0 THEN
The surprising part is that if I remove the date condition for Query it works!
It also works if I just use one Data condition like for example: attendence.attendence_date = {ParamFromDate} :o
What to do?
What must be the error?
Has anyone faced such a problem?
Please guide me to solve this error.
TIA
Yogi Yang |
| |
| |
| | | |
|
| | |
| |
| Publicado el 15,mayo 2015 - 14:57 |
what is the error returned by HExecuteQuery
try with:
if not HExecuteQuery(QRY_StudentAttendenceView) then
error(errorinfo())
end
and paste the error here. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 15,mayo 2015 - 15:12 |
Yogi,
I'm not surprised you seem to be having problems with dates. I've not yet come across a DBMS that isn't picky about dates in one way or another. Make sure the formats of your edit controls are correctly defined - normally reversed with no dashes or slashes; like; 20150101. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 15,mayo 2015 - 15:19 |
Hey Yogi
Isn't there anything wrong with your query naming?
You execute QRY_StudentAttendenceView
And for filling your looper QRY_AttendenceOfSelectedStudent...
Regards
Bart |
| |
| |
| | | |
|
| | |
| |
| Publicado el 15,mayo 2015 - 15:23 |
Hi Yogi,
You are executing the query QRY_StudentAttendenceView then you try to use a field from the (probably not initialized) query QRY_AttendenceOfSelectedStudent, hence the error. Just make sure you use QRY_StudentAttendenceView.
Best regards,
Alexandre Leclerc |
| |
| |
| | | |
|
| | |
| |
| Publicado el 15,mayo 2015 - 17:36 |
Quote
Paulo Oliveira
what is the error returned by HExecuteQuery
try with:
if not HExecuteQuery(QRY_StudentAttendenceView) then
error(errorinfo())
end
and paste the error here.
I checked it.
But ErrorInfo() come up blank after HExecuteQuery. Nothing is displayed!
TIA |
| |
| |
| | | |
|
| | |
| |
| Publicado el 15,mayo 2015 - 17:43 |
@Darren,
I don't think this is Date issue as the query will execute if I use only one data in condition!
@Bart,
You caught me. Actually the name difference crept in while copy pasting. I am really sorry about that. I will have to be more careful when I do copy pasting from WD to Browser.
@Alexandre,
If the query work when I remove the Between selection criteria of two dates and just set one single date. But it gives error only when I use two dates.
I have also tried to use >= and <= instead of between but this has also not worked for me.
TIA
Yogi Yang |
| |
| |
| | | |
|
| | |
| |
| Publicado el 15,mayo 2015 - 17:43 |
Hi Yogi,
I think Alexandre has answered your question :confused:
In your code:
- You set the params of the query
- You execute the query
- You read a row from the data returned by the query
- ...then your code uses a completely different query that (probably) hasn't been initialised |
| |
| |
| | | |
|
| | |
| |
| Publicado el 15,mayo 2015 - 20:01 |
Hi Yogi,
- Are you using HFSQL or MySQL? (The date format might be an issue. And also for the BETWEEN syntax, maybe...)
- Is the query generated but the editor or manually crafted by you?
- Have you tried with hard-coded dates just to see how it reacts?
- Have you tried to use a hand-made query using HExecuteSQLQuery()?
- Following our previous comments, what is exactly the error message and on which line? (To complete Paulo, use HErrorInfo(), no ErrorInfo()).
Best regards,
Alexandre Leclerc |
| |
| |
| | | |
|
| | |
| |
| Publicado el 15,mayo 2015 - 23:57 |
It could be missing double-quote for the date ie
attendence.attendence_date BETWEEN "20150501" AND "20150515"
try info your sql stmt to see if " included or not
HTH
King |
| |
| |
| | | |
|
| | |
| |
| Publicado el 16,mayo 2015 - 06:52 |
Quote
Alexandre Leclerc
- Are you using HFSQL or MySQL? (The date format might be an issue. And also for the BETWEEN syntax, maybe...)
I am using SQLite which is default for Android development in WM.
Quote
Alexandre Leclerc
- Is the query generated but the editor or manually crafted by you?
The query is generated by the Editor it is not crafted Manually
Quote
Alexandre Leclerc
- Have you tried with hard-coded dates just to see how it reacts?
No I have not tried that. Will check it today thanks for the tip.
Quote
Alexandre Leclerc
- Have you tried to use a hand-made query using HExecuteSQLQuery()?
No I always avoid hand crafted queries. One of the reason why I would like to use WM for development is because of the visual query editor.
Quote
Alexandre Leclerc
- Following our previous comments, what is exactly the error message and on which line? (To complete Paulo, use HErrorInfo(), no ErrorInfo()).
I have tired to use both but have not got any error information.
What happens is that after executing the query if I check the number of records returned by query using HNbRec() function I get the exact count expected.
When I execute HReadFirst() there is no error and when I check the HErrorInfo() there is nothing shown.
But if I check the HOut it returns True. I don't understand as to why?
TIA
Yogi Yang |
| |
| |
| | | |
|
| | |
| |
| Publicado el 25,mayo 2015 - 15:36 |
I have tried many things to solve the error but could not. But yesterday I managed to get it to work but in a very inefficient way!
Here is the code just for completeness:
I is int = 1 ds is Data Source MySQL is string bIsPresent is boolean dAttendenceDate is Date sSubName is string nTotalRecs is int dFromDate is Date dToDate is Date dFromDate = EDT_FromDate dToDate = EDT_ToDate //mysql = "SELECT attendence_id, attendence_student_id, attendence_present_absent, attendence_date FROM attendence WHERE (attendence.attendence_date BETWEEN '%1' AND '%2' AND attendence.attendence_student_id = %3) ORDER BY attendence_date" MySQL = "SELECT attendence_id FROM attendence WHERE (attendence.attendence_date BETWEEN '%1' AND '%2' AND attendence.attendence_student_id = %3) ORDER BY attendence_date" //First Validate Data IF EDT_FromDate > EDT_ToDate THEN Error("Please select proper dates","From Date cannot be greater than To Date") RETURN END MySQL = StringBuild(MySQL,EDT_FromDate..Value,EDT_ToDate..Value,gnStudentID) //MySQL = StringBuild(MySQL, gnStudentID) LooperDeleteAll(LOOP_Attendence) HExecuteSQLQuery(ds,hQueryDefault,MySQL) nTotalRecs = HNbRec(ds) //info("Total Attendence",HNbRec(attendence), "Student ID: " + gnStudentID) IF HNbRec(attendence) > 0 THEN HReadFirst(attendence) I = 1 WHILE NOT HOut(attendence) IF attendence.attendence_date >= dFromDate AND attendence.attendence_date <= dToDate AND attendence.attendence_student_id = gnStudentID THEN //Info(attendence.attendence_id,attendence.attendence_date,attendence.attendence_student_id,attendence.attendence_present_absent) WHEN EXCEPTION IN bIsPresent = attendence.attendence_present_absent dAttendenceDate = attendence.attendence_date HReadSeekFirst(subject_master,subject_master_id,attendence.attendence_subject_id) sSubName = subject_master.subject_master_name DO Error(ExceptionInfo(errFullDetails)) END WHEN EXCEPTION IN LooperAddLine(LOOP_Attendence,attendence.attendence_id,gnStudentID,dAttendenceDate,bIsPresent,sSubName) DO Error("While displaying Data",ExceptionInfo(errFullDetails)) END //WHEN EXCEPTION IN END //IF attendence.attendence_date >= dFromDate AND attendence.attendence_date <= dToDate AND attendence.attendence_student_id = gnStudentID THEN HReadNext(attendence) I = I + 1 END //WHILE NOT HOut(attendence) END //IF HNbRec(attendence) > 0 THEN
Thanks,
Yogi Yang |
| |
| |
| | | |
|
| | | | |
| | |
|