kirchaj
Posts: 215
|
| Posted: 10/27/2010, 9:29 AM |
|
I have a current database running with Postgres 7.3 and Php 4x. Everything with CCS has been running fine. I am looking to upgrade to a newer server running Postgres 8x and Php 5x and decided to test it on my laptop first to see what issues I might run into. Plenty.
The one right now I can't get past is some custom code I have written to display and image if a count based on a user id is more than 0. Here is the code
$db = new clsDBportfolio();
$where = "stu_id=".CCGetUserID()." AND course_cp_index=".$course_cp_course_semester->course_cp_index->GetValue().
" AND crssem_index=".$course_cp_course_semester->crssem_index->GetValue();
$result = CCDLookUp("count(*)", "student_scores",$where, $db);
$course_cp_course_semester->scoresavailable->Visible = $result;
$db->close();
Here is the error I now get when running on the newer versions of the DB and php
Warning: pg_exec() [function.pg-exec]: Query failed: ERROR: operator does not exist: character varying = integer LINE 1: SELECT count(*) FROM student_scores WHERE stu_id=800000001 A... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. in C:\xampp\htdocs\portfolio\student\db_pgsql.php on line 101
I am frustrated as this has always worked in the past and I can't seem to figure out the problem. The STU_ID is varchar(9) and the ccgetuserid returns an integer (I think) but I don't know how to fix this one.
Any help would be greatly appreciated.
TK
|
 |
 |
kirchaj
Posts: 215
|
| Posted: 10/27/2010, 7:08 PM |
|
Ok. Here was the solution.
Instead of this
$where = "stu_id=".CCGetUserID()." AND
I needed this
$where = "stu_id=' ".CCGetUserID()." ' AND
The first statement format worked with PHP 4 but did not work with PHP5.
|
|
|
|
kirchaj
Posts: 215
|
| Posted: 10/29/2010, 6:13 AM |
|
With a quick response, here is what support had to say about the issue. Obviously a better solution then mine.
when creating custom sql query you need to remember about type cast. The CCGetUserID() function returns integer value that should be converted to string value if stu_id field is varchar.
It is recommended to use ToSQL() method of database connection class to convert data into proper data type in custom queries.
E.g.
$where = "stu_id=".$db->ToSQL(CCGetUserID(), ccsText)." AND course_cp_index=".$db->ToSQL($course_cp_course_semester->course_cp_index->GetValue(), ccsInteger).
" AND crssem_index=".$db->ToSQL($course_cp_course_semester->crssem_index->GetValue(), ccsInteger);
Please remember that I provided ccsInteger data type as example only, please use real data types in your code.
|
|
|
|
|
