problems about database class

[eluser]datactrl[/eluser]
Hi, all

I'm using PostgreSQL database with CI. The following code will cause a strange error which is on "$query->num_rows()", said referring a member function not on an object. Obviously it means $query is not an object. But I execute the same insert query on pgadmin by replacing the ? with the same case values, there is no problem at all. And even worse is that error will recover all deleted records on the same user id in job_user2. I found this because I have to delete same records in job_user2 for testing. And this failure recover all of them.

Code:

$s1 = "insert into job_user2 (user_id, job_name, frequent_job, read_write)
    select CAST(? as VARCHAR(16))as user_id, a.job_name, a.frequent_job, a.read_write
    from job_user2 a
    where a.user_id = ? and not exists
        (select b.job_name from job_user2 b
                where b.user_id = ? and b.job_name = a.job_name)";
$this->db->trans_start();
$query = $this->db->query($s1, array($_POST['copy_from_uid'], $_POST['copy_to_uid']));

$this->db->trans_complete();
if($query->num_rows() == 0){
    exit(WARNING . 'No any user ID copied');
}else{
    exit(OK . $query->num_rows() . ' Jobs copied');

};

[eluser]theprodigy[/eluser]

$s1 = "insert into job_user2 (user_id, job_name, frequent_job, read_write)
select CAST(? as VARCHAR(16))as user_id, a.job_name, a.frequent_job, a.read_write //<----- binding here
from job_user2 a
where a.user_id = ? and not exists //<----- binding here
(select b.job_name from job_user2 b
where b.user_id = ? and b.job_name = a.job_name)"; //<----- binding here
$this->db->trans_start();
$query = $this->db->query($s1, array($_POST['copy_from_uid'], $_POST['copy_to_uid'])); //<----- only two values passed in here

$this->db->trans_complete();
if($query->num_rows() == 0){
exit(WARNING . 'No any user ID copied');
}else{
exit(OK . $query->num_rows() . ' Jobs copied');

Is it possibly throwing the error, because you are trying to bind 2 values into 3 places?

[eluser]datactrl[/eluser]
Sorry, it's a mistake when I posted. Because I've changed to call a store procedure jobp_copy_jobs(from_id,to_td). That's why it becomes two parameters. And with the stored procedure it works fine.
With the stored procedure, the coding is as following...

Code:

$s1 = "select * from jobp_copy_jobs(?,?)";
$this->db->trans_start();
$query = $this->db->query($s1, array( $_POST['copy_from_uid'], $_POST['copy_to_uid']));
$this->db->trans_complete();
if($query->num_rows() == 0){
   exit(WARNING . 'No any user ID copied');
}else{
   exit(OK . $query->num_rows() . ' Jobs copied');
};

While the original should be as following.

Code:

$s1 = "insert into job_user2 (user_id, job_name, frequent_job, read_write)
    select CAST(? as VARCHAR(16))as user_id, a.job_name, a.frequent_job, a.read_write
    from job_user2 a
    where a.user_id = ? and not exists
        (select b.job_name from job_user2 b
                where b.user_id = ? and b.job_name = a.job_name)";
$this->db->trans_start();
$query = $this->db->query($s1, array( $_POST['copy_to_uid'],$_POST['copy_from_uid'], $_POST['copy_to_uid']));

[eluser]theprodigy[/eluser]

And with the stored procedure it works fine.

Does this mean it's fixed, and you are no longer looking for an answer?

[eluser]datactrl[/eluser]
If the insert sql in CI is working. I would like to do that rather than have to have one more stored procedure to do it.

[eluser]theprodigy[/eluser]
Well, I'm not really big on sub-selects and insert selects (especially inserting into the same table you are selecting from), but all I can see that MAY cause an issue is the lack of a space between a closing parenthesis and the 'as' keyword.

$s1 = "insert into job_user2 (user_id, job_name, frequent_job, read_write)
select CAST(? as VARCHAR(16))as user_id, a.job_name, a.frequent_job, a.read_write //<----- on this line
from job_user2 a
where a.user_id = ? and not exists
(select b.job_name from job_user2 b
where b.user_id = ? and b.job_name = a.job_name)";

[eluser]datactrl[/eluser]
I make a space between ')' and 'as' and the problem is the same. I don't think it's a problem of SQL. Because I copy the exact SQL onto pgAdmin to execute it and it works. And I have database debug on. So if there is an error from database I should catch it. I believe it's a problem from CI database class. When the error happened, it also rollback my deleted records which have been committed as I said I have to delete some detail records before I do the 'COPY' test.