New post
Question
· Mar 23, 2021

SQL Error 400 Illegal Value

#SQL #Caché

Cache ODBC State S1000 Native Code 400 Illegal Value

This is my query:

select  DateTijdSec from 
 GLPPatTcActie 
 where pnr = '27085070017' and LTestId->Makey='BLA' and VerzamelDatTijd < '2021-03-04-2021 09:04' and glpactieid->makey in ('TAV','TMA') order by DateTijdSec desc

Most likely there is a wrong date time in the table, how can i get the data?

Product version: Caché 2016.1
$ZV: 2016.1.3
Discussion (8)0
Log in or sign up to continue
Josefien Janssen · Mar 23, 2021

This is the complete error:

<eb1>[C:\Program Files (x86)\George Poulose\QTODBC50\QTODBC50.exe]
[SQLCODE: <-400>:<Fatale fout >]
[Cache Error: <<ILLEGAL VALUE>zVerzamelDatTijdStorageToLogica+1^User.GLPPatTcActie.1>]
[Location: <ServerLoop - Query Fetch>]
[%msg: <Unexpected error occurred:  <ILLEGAL VALUE>zVerzamelDatTijdStorageToLogica+1^User.GLPPatTcActie.1>]
State:S1000,Native:400,Origin:[Cache ODBC][State : S1000][Native Code 400]</eb1>

0 0
Robert Cemper · Mar 23, 2021

OK!
Now I see the problem. Each datatype has its specific conversion from internal format to ODBC format
So if VerzamelDatTijd is %Date or %Time or %Timestamp and the stored content doesn't fit
you get most likely this error.
It could be by principle := it fits never, a design issue
It could be just a glitch in some data record.
I suggest you start the SQL shell from terminal prompt.  USER>do $system.SQL.Shell()
And then run your modified query:

select  ID,%INTERNAL(VerzamelDatTijd),%ODBCout(VerzamelDatTijd),DateTijdSec
from  GLPPatTcActie 
 where pnr = '27085070017' and LTestId->Makey='BLA'
-- and VerzamelDatTijd < '2021-03-04 09:04'
and glpactieid->makey in ('TAV','TMA')
-- order by DateTijdSec desc

ID shows the flaky record
%INTERNAL() shows the raw content
%ODBCOUT() should trigger the error

Once you know the content you should be able to fix it.

0 0
Josefien Janssen · Mar 23, 2021

Hello Robert,

Thank you for the support. unfortunately I get the same error.

This is the first time that i run a query at the shell prompt.

These are my actions (the table GLPPatTcActie belongs to namespace LABSQL):

Namespace: LABSQL
You're in namespace LABSQL
Default directory is f:\databases\labsql\
LABSQL>do $system.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------
 
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
LABSQL>>select ID,%INTERNAL(VerzamelDatTijd),%ODBCout(VerzamelDatTijd),DateTijdSec from GLPPatTcActie where pnr = '27085070017' and LTestId->Makey='BLA' and glpactieid->makey in ('TAV','TMA')
1.      select ID,%INTERNAL(VerzamelDatTijd),%ODBCout(VerzamelDatTijd),DateTijdSec from GLPPatTcActie where pnr = '27085070017' and LTestId->Makey='BLA' and glpactieid->makey in ('TAV','TMA')
 
ID      Expression_2    Expression_3    DateTijdSec
 
 
[SQLCODE: <-400>:<Fatale fout >]
[%msg: <Unexpected error occurred:  <ILLEGAL VALUE>zVerzamelDatTijdStorageToLogica+1^User.GLPPatTcActie.1>]
0 Rows(s) Affected
statement prepare time: 0.8245s, elapsed execute time: 0.0167s.
---------------------------------------------------------------------------
LABSQL>>  << entering multiline statement mode >>
        1>>

0 0
Robert Cemper · Mar 23, 2021

OK. this looks like the first record you find is already in trouble.
I'd expect your data type is something else then stored.
Next omit the conversion %ODBCOUT()  just to see what internal content is and where it starts

  select ID,%INTERNAL(VerzamelDatTijd),
        DateTijdSec from GLPPatTcActie
         where pnr = '27085070017'
         and LTestId->Makey='BLA'
         and glpactieid->makey in ('TAV','TMA') 

The next question is of course which program writes these values? and how? what's the source?

I get more and more the impression that some 'aged' program writes directly into the global.
Probably directly some $HOROLOG which is a combined %DATE,%TIME construct.
 

0 0