ODBC Socket Server II / Bugs / #12 Long queries son't seem to work

Logged In: YES
user_id=980704

I've had similar problem, simple but large queries taking 25
minutes to return or just hanging, and was able to perform
great performance boost on large query results as follows:

1) If using PHP, replace the ODBC Socket Server class PHP
client class fgets() loop with the following:

do {
//Try some large number:
//$data = fread($fToOpen, 8192);
//$data = fread($fToOpen, 16384);
$data = fread($fToOpen, 32768);
if (strlen($data) == 0) {
break;
}
$sReturn .= $data;
} while (true);

2) As per bug reports, increase the buffer size in main-server.h

const int kBufferSize = 1024;

Also apply the memory leak fix reported in 617441.

3) The real problem lies in the Microsoft function

//now, execute the SQL Statement
Cmd1->CommandText = T2OLE((char*)sSQL.c_str());

in ExecSQL() and the way RecordSetToMSDTD() is forced to
do a temporary write of the query result to disk in order to get
it into XML.

I found the best solution was to port to Microsoft .NET 1.1
which is more XML friendly and the SQL execution code for
large queries on MS Access databases several orders of
magnitude faster. Here is my code. Note though:

- There is no MS Access ODBC driver (currently) for .NET. The
solution below forces you to change from using a DSN to
identify the database you want to use to an absolute directory
path like this:

sConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\\mydir\\mydatabases\\abigdatabase.mdb";

- Caveat Emptor. I am now an occasional programmer and
certainly NOT a MS Windows programmer. The code works for
me but it is probably beginner quality.

- If you try to recompile in VC++ .NET 2003, you'll get several
errors you'll need to fix elsewhere in the code.

main-server.cpp

Change the header includes so:

------------------------------------------------------------
#include "stdio.h"
#include "io.h"

#include "stdafx.h"
#include "main-server.h"
#include <fstream>
//.NET #include "iostream.h"

#include <iostream>
using namespace std;

/* Added for .NET OLE port: */
#using <mscorlib.dll>
#using <System.dll>
#using <System.XML.dll>
#using <System.Data.dll>

using namespace System;
using namespace System::Data;
using namespace System::Xml;
using namespace System::Data::OleDb;
//using namespace System::Data::SqlClient;
/* End of .NET OLE addition */
------------------------------------------------------------

Replace the ExecSQL function so:

------------------------------------------------------------
-------------
std::string ExecSQL(std::string sConnectionString, std::string
sSQL)
{
// Convert to .NET managed strings
System::String* strConnectionString =
sConnectionString.c_str();
System::String* strSQL = sSQL.c_str();

//Example: OleDbConnection * mySqlConnection = new
OleDbConnection(S"Provider=Microsoft.Jet.OLEDB.4.0;Data

Source=c:\\mydir\\mydbs\\agreatbigdb.mdb");
OleDbConnection * mySqlConnection = new
OleDbConnection(strConnectionString);

try
{
LogEvent("ExecSQL 1 Connect ...", 1);
mySqlConnection->Open();

//create the datareader object to connect to table
LogEvent("ExecSQL 2 Exec SQL...", 1);
OleDbDataAdapter * adapter = new
OleDbDataAdapter();
adapter->SelectCommand = new
OleDbCommand(strSQL, mySqlConnection);
//adapter->SelectCommand = new
OleDbCommand("SELECT * FROM postcodepredictions WHERE
code = '2000' ORDER BY isunit, code,

periodend ASC", mySqlConnection);
DataSet * dataset;
dataset = new DataSet("wholetable");
adapter->Fill(dataset);

/* GetXml returns the XML representation of the
DataSet without schema information. To write the schema
information

from the DataSet (as XML Schema) to a string, use
GetXmlSchema.
*
* To write a DataSet to a file, stream, or XmlWriter,
use the WriteXml method. The first parameter you pass to

WriteXml is the destination of the XML output. For example,
pass a string containing a file name, a System.IO.TextWriter
object, and so

on. You can pass an optional second parameter of an
XmlWriteMode to specify how the XML output is to be written.
*/
LogEvent("ExecSQL 3 Get XML ...", 1);
String * xmlDS = dataset->GetXml();
//String * xmlDS = dataset->GetXmlSchema();
//DEBUG Console::WriteLine(xmlDS);

//close the connection
LogEvent("ExecSQL 4 Close connection ...", 1);
mySqlConnection->Close();

/* Programming note:
Convert std::string to System::string:
std::string strOld = "Test";
System::String* strNew = strOld.c_str();
*/

/* Convert managed string to C/C++ string */
LogEvent("ExecSQL 5 String conversion ...", 1);
using namespace System::Runtime::InteropServices;
const char* str = (const char*)
(Marshal::StringToHGlobalAnsi(xmlDS)).ToPointer();
LogEvent("ExecSQL 6 Returning XML ...", 2);
// use str as you wish or copy it elsewhere
// free string
// Marshal::FreeHGlobal(IntPtr((void*)str));
//or call function
// MarshalString ( xmlDS, std::string& os );
return str; //must be std::string
}

//Some usual exception handling
catch(Exception * e)
{
Console::WriteLine(e->ToString());
using namespace System::Runtime::InteropServices;
const char* str = (const char*)
(Marshal::StringToHGlobalAnsi(e->ToString())).
ToPointer();
LogEvent(str, 3);
}
__finally
{
mySqlConnection->Close();
}

}
------------------------------------------------------------
-------------

Mike Collinson

Long queries son't seem to work

Group

Searches

Help

#12 Long queries son't seem to work

Discussion