ODBC Isolation level: odbc

Showing posts with label odbc. Show all posts

Monday, March 26, 2012

Offtopic: "query analyzer" for odbc datasources

I usually do sql-server programming but sometimes I also need to test and query ODBC-datasources with SQL such as Access or Progress databases. I usually do this using active server pages which I find rather obscure, and I would prefer to have a nice little program sort of like query analyzer, where I can specify the connection-string and write my SQL in there somewhere. Does anyone know of such a program?To test and query ODBC-datasources in the manner described one may use Microsoft Query MSQRY32.exe, Sql Plus, Sql *, or a variety of similar tools from packages like ER Studio, ERWin, etc.|||I posted this message in a usenet group aswell and someone came up with a neet little _free_ program that was exactly what I was looking for with alot of nice features...

-> http://www.indus-soft.com/winsql/sql

Offline mode ?

Hi all,
I am designing and testing a report using an ODBC connection. The
report is short (one or two pages) but the SQL request has many tables
and takes several minutes. Being in development phase I don't need the
datas to be refreshed from SQL database every time I click on
"preview" to see the report, but this is what happens (I find no other
reason for so long response time...). So I spend a lot of time waiting
for response.
Is there a kind of "offline" mode to see layout modifications without
refreshing datas every time ?
Thanks !
Jean-MarcTry using your SQL query to export the data from the multiple tables into a
single table. For your development and testing, base your report data on the
single table.
Using Query Analyzer, do:
SELECT * INTO MyTable FROM (rest of complex query)
Now base your test report on the data in MyTable. (You can also use DTS to
create the single table, and can even export the data into a local Access
table).
Charles Kangai, MCT, MCDBA
"Jean-Marc Audrin" wrote:
> Hi all,
> I am designing and testing a report using an ODBC connection. The
> report is short (one or two pages) but the SQL request has many tables
> and takes several minutes. Being in development phase I don't need the
> datas to be refreshed from SQL database every time I click on
> "preview" to see the report, but this is what happens (I find no other
> reason for so long response time...). So I spend a lot of time waiting
> for response.
> Is there a kind of "offline" mode to see layout modifications without
> refreshing datas every time ?
> Thanks !
> Jean-Marc
>sql

Tuesday, March 20, 2012

Odd issue with bound columns when fetching data.

I'm using ODBC 3.0 in code written in C.

I have an service that connects using a system DSN using the SQL Server 2000 driver. On my development system with SQL Server 2005 Express installed, the queries work fine. I prepare a statement and then bind the columns that will be in the result set. On my system, I get all the data as it should be but on a live system, I do not get the proper data for the last three rows. I don't get any error messages from the query or the column binding and there are also no extra information messages that I can retrieve.

The table is something like the following. The names are changed to protect the guilty.

CREATE TABLE DOWNLOAD

(

FIELD_1 NUMERIC( 6,0 ),

ID CHAR( 15),

FNAME( 30 ),

LNAME( 40 ),

CLIENT_ID NUMERIC( 9,0),

CLIENT_NAME CHAR( 50 ),

CODE NUMERIC( 4,0 ),

PHONE CHAR( 10 )

)

On my system, running the exact same binary code as the client machine, I get all of the data from all of the columns as it should be. The problem is when I run on a client's system, the CLIENT_NAME, CODE, and PHONE columns return null values even when there is data there. On my development system the SQL Server instance runs on the same machine. On the client's sytem I am connecting to a remote instance of SQL Server 2000 on another system on the network.

My quandry is what could be different between the two systems that is causing me the problems?

Can you ran SQL Profiler on both machines and see if there is any difference?|||I've turned the logging on at the client side. The logs don't show any errors and it looks like the column binding in SQLBindCol is correct. I haven't seen the server side logs but the gurus on the server side say that they can't see anything wrong there. I've played around with the SQL statement and tried using CONVERT() to change the CHAR to VARCHAR but that doesn't seem to help.

I'm at a total loss why the same code running on my development system runs fine but fails on another system. My guess is that there is some configuration difference but I don't know what that is because I am using the samve version of the SQL Server 2000 ODBC driver on both machines. Its not like there is anything odd or non standard in the table definition either.

|||Just as an update and possibly more information.

I've got it working now but I don't like the solution. What I ended up doing is not binding the columns and calling SQLGetData() after the fetch to get the column data. I don't like this because it costs me about 500 ms for the each time I call the function.

I'd still like to find a solution for why the bound columns don't work.
|||1) I would still recommend you to run SQL Profiler on the servers.
2) How do you bind exactly? Is it possible for you to write (and post the source here) a small ODBC application which demonstrates this problem?|||This is the code I use to bind the columns:

BOOL BindRecord( SQLHSTMT hStmt, PACCDATA_FIELD *pRecord, PINT piRecCount )
{
BOOL bRval = FALSE;
PACCDATA_FIELD pField;
SQLRETURN r;
INT iCnt;
CHAR szBuf[128];
SQLINTEGER len;

if( pRecord && *pRecord )
{
for( iCnt = 0; iCnt < *piRecCount; iCnt++ )
{
pField = *pRecord + iCnt;
len = pField->nLen;

switch( pField->iType )
{
case SQL_CHAR:
r = SQLBindCol( hStmt, pField->iColNum, SQL_C_CHAR, pField->pcVal, pField->nDataSize, &len );
break;

case SQL_DATETIME:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->ucVal, pField->nDataSize, &len );
break;

case SQL_DECIMAL:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->ucVal, pField->nDataSize, &len );
break;

case SQL_NUMERIC:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->lVal, pField->nDataSize, &len );
break;

case SQL_INTEGER:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->lVal, pField->nDataSize, &len );
break;

case SQL_SMALLINT:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->sVal, pField->nDataSize, &len );
break;

case SQL_DOUBLE:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->dVal, pField->nDataSize, &len );
break;

case SQL_FLOAT:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->dVal, pField->nDataSize, &len );
break;

case SQL_REAL:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->fVal, pField->nDataSize, &len );
break;

case SQL_VARCHAR:
r = SQLBindCol( hStmt, pField->iColNum, SQL_C_CHAR, pField->pvcVal, pField->nDataSize + 1, &len );
break;

case SQL_UNKNOWN_TYPE:
ST_WriteLog( "st_ctimpact", "BindRecord", "Field data type is SQL_UNKNOWN_TYPE", ST_LOG_DEBUG );
default:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, pField->ptr, pField->nDataSize, &len );
break;
}

if( r == SQL_SUCCESS_WITH_INFO )
{
lstrcpy( szBuf, "Field name: " );
lstrcat( szBuf, pField->szColName );
ST_WriteLog( "st_AcceleratedData", "BindRecord", szBuf, ST_LOG_WARNING );
ShowSQLMessages( "BindRecord", SQL_HANDLE_STMT, hStmt );
bRval = TRUE;
}
else if( r != SQL_SUCCESS )
{
lstrcpy( szBuf, "Field name: " );
lstrcat( szBuf, pField->szColName );
ST_WriteLog( "st_AcceleratedData", "BindRecord", szBuf, ST_LOG_WARNING );
ShowSQLErrors( "BindRecord", SQL_HANDLE_STMT, hStmt );
}
else
bRval = TRUE;
}
}
return( bRval );
}

A key to remember is that other columns that are bound in this way, and have the same data types, are returning data just fine. I've looked at the logs on the client side where this code works, and there are no errors and the prameters look correct as far as I can tell. Also this code works as advertized on my system but not on the live system. Right now, I don't think my customer will be willing to let me mess around on their system trying to come up with an application that would recreate the problem. Also note that I cannot recreate the problem on my own system. I had hoped that it would be broken on my system so I could debug and find out what was the problem but because it works, I can't.
|||I do not think you have to mess around with your customer system.
I would suggest you write a straightforward ODBC application from scratch just going against this very table.
Use some static arrays and bind directly with SQLBindCol, so to exclude any dependecies on how PACCDATA_FIELD or other parts of your custom code are written.
Then when you are sure it works as supposed on your machine, try it against your customer's.|||Then I guess you didn't read the thread all that well. I don't have to write a test app. The live code works fine on my system on the same table definition in the same database name as the live system. The problem is when I deployed the code, some of the bound columns don't return data. Since I can't recreate the problem on my system, I'm at a loss as to what the problem is.
|||I'm not sure if it is possilbe to resolve your issue without actual experimentation. Your system is not identical to the live system, is it? So your argument that the code works on your system is not helpful to you. If you really want to find out where the problem is you might try and start narrowing down on the issue. That is what I suggested to you.|||It just seemed to me that you asking me to make a test case that is exactly what I have running. I think I might have it working now and it must have something to do with my adding RTRIM() and CONVERT() functions in the select statement. I removed them and it seems to work better now.