New post
Question
· Jul 14, 2020

Getting Fatal error in Date Column in cache db

#Databases #Caché

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: :]
[Cache Error: <%0AmBs1+1^%sqlcq.SMTKTUAT.cls498.1>]
[Location: ]
[%msg: %0AmBs1+1^%sqlcq.SMTKTUAT.cls498.1>]

Discussion (7)1
Log in or sign up to continue
Kevin Chan · Jul 14, 2020

%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.

0 0
Kevin Chan · Jul 14, 2020

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.

0 0
Nigel Salm · Jul 15, 2020

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

0 0