[unixODBC]Connection switch - how can I recover?

[dianeghir]

Hi

I am trying to access a solidDB database using php and apache, and I know almost nothing about php or apache. Occasionally I get an apache error:

[Fri Dec 04 08:45:19 2009] [error] [client 10.10.1.116]
PHP Warning: odbc_exec() [<a href='function.odbc-exec'>function.odbc-exec</a>]:
SQL error: [unixODBC]Connection switch, some session context may be lost,
SQL state S1000 in SQLExecDirect in /usr/local/apache2/htdocs/query.php on line 42

I tried to modify my code to close and reopen the connection when this happens, but that didn't work.

.php code is shown below.

Any help will be greatly appreciated.

thanks

<?php
$ts = date("Y/m/d H:i:s");
$ip =$_GET['ip'];
$msg = " ";
$host = "10.10.1.102";
$data = $ts . $msg . $ip . " connect(" . $host . ")\n";
file_put_contents('php.log', $data, FILE_APPEND);

$counter = 1;
$rows = 0;

$tryit = true;

$connection = odbc_pconnect('solidDB','dba','dba');

if ($connection == 0) {
print("<?xml version=\"1.0\"?>\n");
print("<soapenv:Envelope xmlns:soapenv=\"

http://schemas.xmlsoap.org/soap/envelope/\">\n");

print("<soapenv:Body>\n");
print("<soapenv:Fault>\n");
print("<faultcode>soapenv:Server</faultcode>\n");
print("<faultstring>VID System Error</faultstring>\n");
print("<detail>\n");
print("<ns:query_fault_details xmlns:ns=\"

http://vid.verizon.com/\">\n");

print("<ip>$host</ip>\n");
print("<error>Unable to connect to database 'soliddb:1315'</error>\n");
print("</ns:query_fault_details>\n");
print("</detail>\n");
print("</soapenv:Fault>\n");
print("</soapenv:Body>\n");
print("</soapenv:Envelope>\n");

$msg = " (cannot connect) ";

} else {

// define query
$query = "select bsid, misdn, mdn, md5, session_id, start_time, timeout from vid where ip='$ip'";

while( $tryit ) {
//executing query and get result
$result = odbc_exec($connection, $query);

if($result) {
//looping throught the result
while(odbc_fetch_row($result)) {
$rows+=1;
$bsid = odbc_result($result, 1);
$misdn= odbc_result($result, 2);
$mdn= odbc_result($result, 3);
$md5= odbc_result($result, 4);
$session_id= odbc_result($result, 5);
$start_time= odbc_result($result, 6);
$timeout= odbc_result($result, 7);

print("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");
print("<soapenv:Envelope xmlns:soapenv=\"

http://schemas.xmlsoap.org/soap/envelope/\"

\n");
print(" xmlns:vid=\"

http://vid.verizon.com/\">\n");

print("<soapenv:Body>\n");
print("<vid:basic_query_response>\n");
print("<ip>$ip</ip>\n");
print("<bsid>$bsid</bsid>\n");
print("<misdn>$misdn</misdn>\n");
print("<mdn>$mdn</mdn>\n");
print("<md5>$md5</md5>\n");
print("<session_id>$session_id</session_id>\n");
print("<start_time>$start_time</start_time>\n");
print("<timeout>$timeout</timeout>\n");
print("<is_active>true</is_active>\n");
print("</vid:basic_query_response>\n");
print("</soapenv:Body>\n");
print("</soapenv:Envelope>\n");

$msg = " (data found in " . $counter . " attempt(s)) ";
$tryit = false;
}
$counter+=1;
}
else {
// no results set means ODBC error....I think....what to do?
$counter+=1;
odbc_close($connection);
$connection = odbc_pconnect('solidDB','dba','dba');
}

if ($counter >= 3) {
$tryit = false;
}
}

if($rows == 0) {
print("<?xml version=\"1.0\"?>\n");
print("<soapenv:Envelope xmlns:soapenv=\"

http://schemas.xmlsoap.org/soap/envelope/\"

\n");
print(" xmlns:vid=\"

http://vid.verizon.com/\">\n");

print("<soapenv:Body>\n");
print("<vid:basic_query_response>\n");
print("<ip>$ip</ip>\n");
print("<bsid />\n");
print("<misdn />\n");
print("<mdn />\n");
print("<md5 />\n");
print("<session_id />\n");
print("<start_time />\n");
print("<timeout />\n");
print("<is_active>false</is_active>\n");
print("</vid:basic_query_response>\n");
print("</soapenv:Body>\n");
print("</soapenv:Envelope>\n");

$msg = " (no data found in " . $counter . " attempts) ";
}
}

// odbc_close($connection);
$ts = date("Y/m/d H:i:s");

$data = $ts . $msg . $ip . " disconnect\n";
file_put_contents('php.log', $data, FILE_APPEND);
?>

 

[jpadie]

one reason could be that you close the odbc connection in the second part of the if ($result) conditional. however this if condition is still within a while loop that might not have finished. so subsequent calls to odbc_exec will fail.

try removing the odbc_close call and move from pconnect to connect. that way the connection will automatically close. in any event having pconnect with explicit odbc_close seems a bit pointless.

other than that, the code looks ok so any errors are likely to be in the OS/ODBC?DB interaction.

 

[dianeghir]

Hi jpadie

Thanks for the response.

I have tried with and without pooling but get the the '[unixODBC]Connection switch, some session context may be lost' error with both.

My original idea was to closed and reopen the connection when I get the error....I try 2 times, but that didn't work.

How can I restore the context when it is lost so I get the proper response from the database? Is there a way to wait on odbc_exec?


thanks

diane

 

[ingresman]

I see that Solid is one of the many DB's IBM provide !
do you ever get the error is any other ODBC app >, could you try and replicate it somehow. Do you have any other PHP apps that use this driver ?
I'm wondering if some kind of oddness is going on, after all it is an immemory database and is tuned for speed not ODBC access.
I can't find anything about a connection switch, if you can find any ref;s to it please post them
Your code looks ok, but I havn't printed it and it does look a bit odd (sorry !)Your question about making ODBC_EXEC wait, where do you want to make it wait?