SQL query - strange behaviour?

[eluser]Las3r[/eluser]
Hey there,

For my "support ticket overview" page i'm trying to fetch all info from the database, i do this with the following code:

Code:

$db1 = $this->load->database('web', TRUE);
                
        $query = 'SELECT category,ticket_contents FROM
                support_tickets
                WHERE
                sender = ?';
                
        
        $db1->query($query,'las3r');

This gives the following error :

A Database Error Occurred

Error Number:

SELECT category,ticket_contents FROM support_tickets WHERE sender = 'las3r'

When I execute this query in MSSQL Enterprise studio it shows all the info it should, and the query executes as expected.

HOWEVER (this is the odd part)

when i execute THIS:

Code:

$query = 'SELECT category FROM
                support_tickets
                WHERE
                sender = ?';

It works and gives me a blank screen (no errors). So when i go and fetch 1 item it works, but when i fetch multiple it fails. Normally I'd just use a select * from xxx where yyy, statement, but i keep having problems with CI.

Can anyone help me out ?

The idea is to fetch all the ticket data into an array (happens in Model), then pass the array on to the Controller, to show it in a readable format in the View, but i'm stuck here.

Thank you.

p.s. I have tried to find a solution on the wiki's and the user guide, but they don't really explain this problem I'm experiencing

With some help from pistolPete, i wrote the following function, which works fine:

Code:

function submit_ticket($category,$title,$content)
    {
        $db1 = $this->load->database('web', TRUE);
                
        $sql = 'INSERT INTO support_tickets
                (sender,category,ticket_title,ticket_contents,date,last_edit,is_answered)
                VALUES
                (?,?,?,?, GETDATE() , GETDATE() ,?) ';

        $submit = $db1->query($sql,array($_SESSION['username'],$category,$title,$content,0));
        if ($db1->affected_rows()  == 1)
        {
        return TRUE;
        }
        else
        {
        return FALSE;
        }

In other words, i do know how to write queries and how to extract stuff...

[eluser]Las3r[/eluser]
I checked the SQL profiler in MSSQL Enterprise manager and it showed me the following info as attached,

it seems its using transaction commits / rollbacks, but i do not use this anywhere in CI (yet). Also the query that it executes on ci_iat_web executes fine..

Erik

[eluser]TheFuzzy0ne[/eluser]
It's strange that there's no database error number... I would suggest trying to escape your query better. I don't see how it should make any difference, but I reckon it's worth a try.

Code:

$query = 'SELECT `category`, `ticket_contents`
    FROM `support_tickets`
    WHERE `sender` = ?';

Or use the Active Record class:

Code:

$this->db->select('category, ticket_contents');

$result = $this->db->get_where(
        'support_tickets',
        array('sender' => 'las3r')
    );

[eluser]Las3r[/eluser]
MrFuzzy1, thanks for your quick reply.

Tried both, and both gave the "Database error",
AR-class shows this error for example:

A Database Error Occurred

Error Number:

SELECT * FROM support_tickets WHERE sender = 'las3r'

The lame part is that i have other select from - functions that work fine in the same application, this one is just using a different database (as specified in teh code), so you'd say that the problem lies with the database / connectivity, however the second post i made here shows a function in the same model that inserts iformation, and that DOES work fine.

It does just not make any sense.

[eluser]TheFuzzy0ne[/eluser]
I am 99.9% certain that it's a problem with the configuration of your database, I've no idea what though... The fact that there is no error number (although this might be normal for MSSQL databases), and that there is absolutely nothing wrong with the query, suggests that this is the problem.

Just out of interest, can you confirm that your CodeIgniter database configuration is correct?

[eluser]Las3r[/eluser]
YEa i know.

MSSQL does not require any ''quotes'' around the collumnnames, i got case-sensitive = off, and im using the following configs:

Code:

$active_group = "accounts";
$active_record = TRUE;

$db['accounts']['hostname'] = "xxx";
$db['accounts']['username'] = "sa";
$db['accounts']['password'] = "xxx";
$db['accounts']['database'] = "ci_me_mu";
$db['accounts']['dbdriver'] = "mssql";
$db['accounts']['dbprefix'] = "";
$db['accounts']['pconnect'] = FALSE;
$db['accounts']['db_debug'] = TRUE;
$db['accounts']['cache_on'] = FALSE;
$db['accounts']['cachedir'] = "";
$db['accounts']['char_set'] = "utf8";
$db['accounts']['dbcollat'] = "utf8_general_ci";

$db['characters']['hostname'] = "yyyy";
$db['characters']['username'] = "sa";
$db['characters']['password'] = "yyyyy";
$db['characters']['database'] = "ci_mu";
$db['characters']['dbdriver'] = "mssql";
$db['characters']['dbprefix'] = "";
$db['characters']['pconnect'] = FALSE;
$db['characters']['db_debug'] = TRUE;
$db['characters']['cache_on'] = FALSE;
$db['characters']['cachedir'] = "";
$db['characters']['char_set'] = "utf8";
$db['characters']['dbcollat'] = "utf8_general_ci";

$db['web']['hostname'] = "zzzz";
$db['web']['username'] = "sa";
$db['web']['password'] = "zzzz";
$db['web']['database'] = "ci_iat_web";
$db['web']['dbdriver'] = "mssql";
$db['web']['dbprefix'] = "";
$db['web']['pconnect'] = FALSE;
$db['web']['db_debug'] = TRUE;
$db['web']['cache_on'] = FALSE;
$db['web']['cachedir'] = "";
$db['web']['char_set'] = "utf8";
$db['web']['dbcollat'] = "utf8_general_ci";

THe thing though, is that the inserting works, im using sa (superadmin) user that has all access to everything imaginable, and no limitations whatsoever. All 3 DBs are on the same physical server, accessed with the same user/pass and SQL Profiler in MSSQL shows all queries on all databases executed as "correct".

The odd part is that im using select * from statements on other databases, and it works completely.

I'll go and try this config on my linux box (running on dev-box @ windows now) to see if that changes anything, but i don't think so.

EDIT: I checked on linux, but indeed it won't work as well - same problem!

Erik

[eluser]TheFuzzy0ne[/eluser]
I'm wondering if the problem has something to do with a firewall somewhere, or the permissions on remote connections. As your database is clearly not on the localhost, it's the only thing I can think of. Is there any reason you can't use a database on the localhost?

[eluser]Las3r[/eluser]
That would be a possibility, however I manage the firewalls myself, and got an allow_all from my ip (1433 and 1434 ports both open to me), as well as my linux webserver (that is hosting the old application, im hosting a new one). The website connects fine to all 3 databases, proven by the fact that the SQL profiler on the remote SQL server is see PHP 5 logging in, and doing the actual query (as shown in the attachment above). This means there's no connecitivity problem, but rather a CI problem.

We got quite some hardware, and the remote server is setup as a testing environment with MSSQL with the appropriate licenses, so that's why i'm hosting remote.

Erik.

[eluser]TheFuzzy0ne[/eluser]
I'm still really confused here. The select statement in the error looks totally valid. Sorry, but I'm stumped on this one. Hopefully an MSSQL guru will try to help.

[eluser]Las3r[/eluser]
Yea me too, I mean i work with MSSQL on a daily basis, and this just doesn't make sense.

Thanks for helping me try Fuzzy, I really appreciate it. Do you know of any MSSQL experts in this forum ?

Erik