Getting Fatal error in Date Column in cache db
Hi,
I am getting below error in date column in a table. I am unable to query data or find distinct or to_char on this column.
Select distinct column name from table Select column name from table Select To_Char(column name) from table
Table has 3923509 rows.
Could someone help me how can I identify bad data in this table?
Any idea how we can avoid this error?
[SQLCODE: <-400>:<Fatal error occurred>] [Cache Error: <<VALUE OUT OF RANGE>%0AmBs1+1^%sqlcq.SMTKTUAT.cls498.1>] [Location: <ServerLoop - Query Fetch>] [%msg: <Unexpected error occurred: <VALUE OUT OF RANGE>%0AmBs1+1^%sqlcq.SMTKTUAT.cls498.1>]
Comments
%0AmBs1+1^%sqlcq.SMTKTUAT.cls498.1 is the label and offset of where in the cached query the error was thrown, but not the actual error itself.
How are you running the SQL query? ODBC? JDBC? Embedded? Dynamic? If ODBC or JDBC, do you have any logs?
Alternatively, try purging your cached queries ($ysstem.SQL.Purge()) and reattempt. There could be an issue with the cached query itself.
Is this a SQL query? ($ysstem.SQL.Purge()) when I try I am getting error like this [SQLCODE: <-1>:<Invalid SQL statement>]
I am using Aqua studio tool and connecting to database using JDBC. Not sure how can I get log details.
$system.SQL.Purge() is objectscript code. Sorry for assuming you were using IRIS Terminal.
If you're using JDBC, please edit your JDBC url to look like the following:
jdbc:IRIS://host:port/namespace/logfile
logfile will be written to the current directory of wherever the JDBC process in launched (i.e. the working directory of the JVM process).
It is not accepting IRIS. I have like this jdbc:Cache://host:port/namespace/logfile. it is connecting to database but I could not spot the logfile in my local.
The logfile will be in the working directory of wherever the JDBC process was launched, not necessarily your local working directory. If you're working from a Unix system, you can specify a full filepath. If you're working from Windows, then unfortunately, that will be an issue. A bug was recently fixed that allows the full filepath to be specified via the URL but that was only fixed recently in IRIS.
The tool is in Windows only.
Hi
Can you upload the class definition of the table that contains the data as well as the routines that were generated by the sql compiler i.e. the classes and routines for *.SMTKTUAT.*.* (.cls, *.int)
Have you tried accessing the data through the Management Portal->Stystem Explorer->SQL->Execute Query?
Have you tried setting up an ODBC DSN and then accessing the table from within say Excel?
If you set up a Windows ODBC DSN you can turn on logging for the DSN
To resolve this issue you need to isolate whether the issue lies within the code generated by IRIS for the sql statement, or whether it is the ODBC/JDBC connection functionality.
Have you written a simple class method using %Library.ResultSet as in
classmethod TestQuery() as %Status
{
set tSC=$$$OK
try {
set rs=##class(%ResultSet)).%New("%DynamicQuery:SQL")
set tSC=rs.Prepare("select col1, col2, ...., colN from Table where ....") if 'tSC quit
set tSC=rs.Execute() if 'tSC quit
while rs.Next(.tSC) {
if 'tSC quit
write !,rs.Data("Col1")," ",rs.Data("Col2")," ", ...., rs.Data("ColN")
}
}
catch ex {set tSC=ex.AsStatus()}
if 'tSC write !,$system.Status.GetErrorText(tsc)
quit tSC
}
Nigel