ODBC Returns Wrong Data
-
I've got a query going out to a Cache database via ODBC. It almost always works (90% or so). The other 10% of the time, it returns the wrong data for one of the columns (a LONGVARCHAR column) By wrong, I mean the data is from another row. At first, I though maybe there was too much text in the column and it was trashing a buffer or something, so I cahange my parameter from a VarChar to Text, but it made no difference. I just retried it again now without specifying the datatype at all, and it made no difference. Further investigation showed that the correct data was only 1600 characters, so that isn't going to be the problem, anyway. I've even cleared the parameters for each loop of the read and it still does it. I just now tried to do a select specifically on one of the bad rows and it was still bad, so ti has to be something going on with ODBC or the remote server. Right? Anyone ever see anything like this before?
No dogs or cats are in the classroom. My Mu[sic], Films and Windows Programs, etc.
Usually when I have problems like this, I first try and eliminate something like ODBC. Is there any way to connect natively to the database. Preferably by running something on the server that is hosting the database? If you can do something like that, it will help to eliminate whether the database itself is doing something unusual or unpredictable. As far as ODBC goes, can you try other drivers and duplicate the issue still? :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
Usually when I have problems like this, I first try and eliminate something like ODBC. Is there any way to connect natively to the database. Preferably by running something on the server that is hosting the database? If you can do something like that, it will help to eliminate whether the database itself is doing something unusual or unpredictable. As far as ODBC goes, can you try other drivers and duplicate the issue still? :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
The server is in Idaho or someplace up thataway, and I'm in Texas, so I won't being doing any hands-on. :laugh: (Nope. Can't remote.) There are no native drivers. There is a slight possibility that I could get a .NET driver... If they let me. And no other ODBC driver will work. Freaking InterSystems Cache. The DBA up there is goign to check with InterSystems and see if they have any ideas.
No dogs or cats are in the classroom. My Mu[sic], Films and Windows Programs, etc.
-
I've got a query going out to a Cache database via ODBC. It almost always works (90% or so). The other 10% of the time, it returns the wrong data for one of the columns (a LONGVARCHAR column) By wrong, I mean the data is from another row. At first, I though maybe there was too much text in the column and it was trashing a buffer or something, so I cahange my parameter from a VarChar to Text, but it made no difference. I just retried it again now without specifying the datatype at all, and it made no difference. Further investigation showed that the correct data was only 1600 characters, so that isn't going to be the problem, anyway. I've even cleared the parameters for each loop of the read and it still does it. I just now tried to do a select specifically on one of the bad rows and it was still bad, so ti has to be something going on with ODBC or the remote server. Right? Anyone ever see anything like this before?
No dogs or cats are in the classroom. My Mu[sic], Films and Windows Programs, etc.
Could you post the query? Be aware that Cache X| ::pinch of salt over the shoulder:: isn't SQL-92 compliant and the worst thing (in my opinion) is that it doesn't support operator precedence and that could be a factor here. When I have used it (against my will) I used an ADO.net connector when I could, but prod used ODBC. I don't recall any differences.
-
The server is in Idaho or someplace up thataway, and I'm in Texas, so I won't being doing any hands-on. :laugh: (Nope. Can't remote.) There are no native drivers. There is a slight possibility that I could get a .NET driver... If they let me. And no other ODBC driver will work. Freaking InterSystems Cache. The DBA up there is goign to check with InterSystems and see if they have any ideas.
No dogs or cats are in the classroom. My Mu[sic], Films and Windows Programs, etc.
This sounds like a classic No-Win No-Win situation. You better get a good prescription for anti-depressants. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
Could you post the query? Be aware that Cache X| ::pinch of salt over the shoulder:: isn't SQL-92 compliant and the worst thing (in my opinion) is that it doesn't support operator precedence and that could be a factor here. When I have used it (against my will) I used an ADO.net connector when I could, but prod used ODBC. I don't recall any differences.
select ID, DateOfChecksum, Checksum, RoutineCode, RoutineName, Site from CARC.RoutinesOST But, like I said, it mostly works.
No dogs or cats are in the classroom. My Mu[sic], Films and Windows Programs, etc.
-
select ID, DateOfChecksum, Checksum, RoutineCode, RoutineName, Site from CARC.RoutinesOST But, like I said, it mostly works.
No dogs or cats are in the classroom. My Mu[sic], Films and Windows Programs, etc.
GenJerDan wrote:
it mostly works
I think that's their slogan. :sigh: Is CARC.RoutinesOST a view or something else other than a table? Can you make a copy in another database (e.g. SQL Server) to test against? What you describe certainly is a mystery.
-
GenJerDan wrote:
it mostly works
I think that's their slogan. :sigh: Is CARC.RoutinesOST a view or something else other than a table? Can you make a copy in another database (e.g. SQL Server) to test against? What you describe certainly is a mystery.
It's a table. I can't think of any way to get a copy of it. :( The really fun part is that the WAN connection (or something) is so bad that it takes me 4+ hours to retrieve the data... The connection goes off into never-never land frequently.
No dogs or cats are in the classroom. My Mu[sic], Films and Windows Programs, etc.
-
I've got a query going out to a Cache database via ODBC. It almost always works (90% or so). The other 10% of the time, it returns the wrong data for one of the columns (a LONGVARCHAR column) By wrong, I mean the data is from another row. At first, I though maybe there was too much text in the column and it was trashing a buffer or something, so I cahange my parameter from a VarChar to Text, but it made no difference. I just retried it again now without specifying the datatype at all, and it made no difference. Further investigation showed that the correct data was only 1600 characters, so that isn't going to be the problem, anyway. I've even cleared the parameters for each loop of the read and it still does it. I just now tried to do a select specifically on one of the bad rows and it was still bad, so ti has to be something going on with ODBC or the remote server. Right? Anyone ever see anything like this before?
No dogs or cats are in the classroom. My Mu[sic], Films and Windows Programs, etc.
GenJerDan wrote:
The other 10% of the time, it returns the wrong data for one of the columns (a LONGVARCHAR column)
Try two queries. One that gets everything but the blob. The second that gets only the blob. And given the connection problems presumably this is already a data migration process rather than a processing process. If so a second query for the blob could be used to verify.
-
GenJerDan wrote:
The other 10% of the time, it returns the wrong data for one of the columns (a LONGVARCHAR column)
Try two queries. One that gets everything but the blob. The second that gets only the blob. And given the connection problems presumably this is already a data migration process rather than a processing process. If so a second query for the blob could be used to verify.
Data migration? :laugh: No, they want me to pull this data EVERY DAY for use in a web app here. :laugh: Pulling the column in question all by its lonesome results in the same problem. Pulling the other columns just gives me the other columns. They've always been correct.
No dogs or cats are in the classroom. My Mu[sic], Films and Windows Programs, etc.
-
I've got a query going out to a Cache database via ODBC. It almost always works (90% or so). The other 10% of the time, it returns the wrong data for one of the columns (a LONGVARCHAR column) By wrong, I mean the data is from another row. At first, I though maybe there was too much text in the column and it was trashing a buffer or something, so I cahange my parameter from a VarChar to Text, but it made no difference. I just retried it again now without specifying the datatype at all, and it made no difference. Further investigation showed that the correct data was only 1600 characters, so that isn't going to be the problem, anyway. I've even cleared the parameters for each loop of the read and it still does it. I just now tried to do a select specifically on one of the bad rows and it was still bad, so ti has to be something going on with ODBC or the remote server. Right? Anyone ever see anything like this before?
No dogs or cats are in the classroom. My Mu[sic], Films and Windows Programs, etc.
This may be a silly suggestion (I know nothing about Cache and don't use ODBC anymore) but can /n (return) in the data be causing an issue.
Never underestimate the power of human stupidity RAH
-
This may be a silly suggestion (I know nothing about Cache and don't use ODBC anymore) but can /n (return) in the data be causing an issue.
Never underestimate the power of human stupidity RAH
No idea. It defintiely uses \n rather than \r\n, but all of the records use the same eoln so they should all be broken. It's just too weird. And it's the same records every time. Wish I could substitute the text in the column with something else to see if it still does it, but no way can we do that.
No dogs or cats are in the classroom. My Mu[sic], Films and Windows Programs, etc.
-
No idea. It defintiely uses \n rather than \r\n, but all of the records use the same eoln so they should all be broken. It's just too weird. And it's the same records every time. Wish I could substitute the text in the column with something else to see if it still does it, but no way can we do that.
No dogs or cats are in the classroom. My Mu[sic], Films and Windows Programs, etc.
-
Data migration? :laugh: No, they want me to pull this data EVERY DAY for use in a web app here. :laugh: Pulling the column in question all by its lonesome results in the same problem. Pulling the other columns just gives me the other columns. They've always been correct.
No dogs or cats are in the classroom. My Mu[sic], Films and Windows Programs, etc.
GenJerDan wrote:
No, they want me to pull this data EVERY DAY for use in a web app her
Which would be migration. A non-migration app would be one that pulled it for every user query.
GenJerDan wrote:
Pulling the column in question all by its lonesome results in the same problem.
Given the size maybe you can convert it to a varchar in the SQL. Disassociation from the type might fix the problem.
-
GenJerDan wrote:
No, they want me to pull this data EVERY DAY for use in a web app her
Which would be migration. A non-migration app would be one that pulled it for every user query.
GenJerDan wrote:
Pulling the column in question all by its lonesome results in the same problem.
Given the size maybe you can convert it to a varchar in the SQL. Disassociation from the type might fix the problem.