Sql error 22023

ReportServer Forum You are not logged in. #1 2021-02-10 08:56:49 Retrieve a jsonb object from Postgresql This 2021, I have been reviewing for internal use Reportserver 3.3.0 with Postgresql datasource, reports are fine. That is until I came across tables that contain JSON. How do I extract JSON objects for reports? The TYPE was […]

Содержание

  1. ReportServer Forum
  2. #1 2021-02-10 08:56:49
  3. Retrieve a jsonb object from Postgresql
  4. #2 2021-02-10 09:32:12
  5. Re: Retrieve a jsonb object from Postgresql
  6. #3 2021-02-10 11:07:53
  7. Re: Retrieve a jsonb object from Postgresql
  8. Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar
  9. ОШИБКА: невозможно вызвать json_array_elements для не-массива
  10. 1 ответ

ReportServer Forum

You are not logged in.

#1 2021-02-10 08:56:49

Retrieve a jsonb object from Postgresql

This 2021, I have been reviewing for internal use Reportserver 3.3.0 with Postgresql datasource, reports are fine. That is until I came across tables that contain JSON.

How do I extract JSON objects for reports? The TYPE was recognised as VARCHAR and not JSON

Example of a JSON data, how do I extract only the value for «name»?

I have tried this SQL QUERY but got the error as below;

SELECT p.product -> ‘name’ FROM products AS p

Query could not be executed: ERROR: operator does not exist: character varying -> unknown Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 224

Last edited by nicholas (2021-02-10 09:03:30)

#2 2021-02-10 09:32:12

Re: Retrieve a jsonb object from Postgresql

is this a valid postgres query (sending it directly, without ReportServer)? SELECT p.product -> ‘name’ FROM products AS p
can you pls post a screenshot of the results?

and if you put this query into a dynamic list, what do you see exactly? can you pls post the complete error stack ?

#3 2021-02-10 11:07:53

Re: Retrieve a jsonb object from Postgresql

This is specific for Postgresql, and I have prepared the sample as below

Data structure:
CREATE TABLE public.products (
id int4 NOT NULL PRIMARY KEY,
«name» varchar NOT NULL,
product jsonb NULL
);

The direct SQL query

SELECT name, jsonb_agg(t->’name’) as brand, jsonb_agg(t->’value’) as code FROM products, jsonb_array_elements(products.product) t
group by name ;

The results
name |brand |code |
————|————————|——————-|
Apple Pie |[«Apple Pie»] |[«1All-1-1000-xzp»]|
Orange bod |[«Orange Gala»] |[«1Bll-1-99-aaa»] |
Chicken Pie|[«Downtown Chicken Pie»]|[«1Bll-1-201-aaa»] |

When run sql in Reportserver

Error
Query could not be executed: ERROR: function jsonb_array_elements(character varying) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 653

Is there an alternative method to make that query? One problem I face, not knowing the supported Query functions for JSON in Reportserver.

Last edited by nicholas (2021-02-10 11:25:47)

Источник

Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

From: Adrian Klaver
To: Alexander Farber
Cc: pgsql-general

Subject: Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar Date: 2018-03-02 16:21:44 Message-ID: 0b0c6908-1b0d-0f3f-4dd3-d6000326b02c@aklaver.com Views: Raw Message | Whole Thread | Download mbox | Resend email Thread: Lists: pgsql-general

On 03/02/2018 06:42 AM, Alexander Farber wrote:
> Hi Adrian, I 100% agree that nobody except me should debug my huge
> stored function, but if you look at my PostgreSQL 10.3 log —
>

Which proves what has already been proven, that at least some of the
data is correct. The issue is data that is not correct as evidenced by
the error message:

select mid, jsonb_array_length(tiles) from words_moves where gid=609;
ERROR: 22023: cannot get array length of a scalar
LOCATION: jsonb_array_length, jsonfuncs.c:1579

This is not going to get solved until you identify the ‘bad’ tiles data.

Источник

ОШИБКА: невозможно вызвать json_array_elements для не-массива

У меня есть запрос Postgres, который возвращает следующие значения. Этот запрос возвращает одни данные для идентификатора.

Теперь я хочу получить бизнес-адрес для всего населения в таблице dw_data_job . Я изменил запрос на следующий.

Теперь я получаю ошибку следующего содержания:

Тип столбца dw_data_job.addresses — text , и данные выглядят следующим образом — когда значением JSON является массив :

Другое примерное значение с не-массивом (объектом):

Запрос должен содержать данные json как в виде массива, так и без него, чтобы получить адрес, город, штат и код страны для всех строк данных, в которых вложенный ключ #text имеет значение ‘BUSINESS’ .

1 ответ

Как сообщает вам сообщение об ошибке (и указал Джефф), в таблице dw_data_job есть одна или несколько строк, где столбец addresses не содержит допустимого массива JSON (или NULL ) . Допустимый литерал JSON, да, иначе мы увидели бы другое сообщение об ошибке, вызванное неудачным преобразованием в ::json , но не массив JSON .

Выполните этот запрос, чтобы определить строки с нарушением:

(Включает значения NULL в addresses , что не приведет к возникновению сообщаемой ошибки, но также может быть проблемой.)

Чтобы просто пропустить строки с недопустимыми данными и продолжить запрос:

Я упростил синтаксис с помощью компактного оператора #> для глубоко вложенных значений и возвращать все text вместо некоторых json (как обоснованное предположение).

Чтобы включить как простые объекты, так и массивы, вы можете:

Источник

Источник

@k00ni

I executed the following query via PDO_ODBC on PHP 5.5.x:

SELECT DISTINCT ?g WHERE { GRAPH ?g { ?s ?p ?o }}

and get the following errors:

Fatal error: Uncaught exception ‘Exception’ with message ‘SQLSTATE[22023]: Invalid parameter value: -1 [OpenLink][Virtuoso iODBC Driver][Virtuoso Server]SR540: procedure view’s procedure returned value of type IRI_ID (dtp 243) instead of IRI_ID (dtp 244) for column (inx: 1) (SQLExecute[4294967295] at /build/php5-RpYHCf/php5-5.5.9+dfsg/ext/pdo_odbc/odbc_stmt.c:254)’ in /home/k00ni/Documents/CubeVizEndpoint/vendor/saft/saft/src/Saft/Addition/Virtuoso/Store/Virtuoso.php on line 341

and

Exception: SQLSTATE[22023]: Invalid parameter value: -1 [OpenLink][Virtuoso iODBC Driver][Virtuoso Server]SR540: procedure view’s procedure returned value of type IRI_ID (dtp 243) instead of IRI_ID (dtp 244) for column (inx: 1) (SQLExecute[4294967295] at /build/php5-RpYHCf/php5-5.5.9+dfsg/ext/pdo_odbc/odbc_stmt.c:254) in /home/k00ni/Documents/CubeVizEndpoint/vendor/saft/saft/src/Saft/Addition/Virtuoso/Store/Virtuoso.php on line 341

But when i remove the DISTINCT, everything is fine.

Here is some code:

$this->connection = new PDO(
    'odbc:' . (string)$this->configuration['dsn'],
    (string)$this->configuration['username'],
    (string)$this->configuration['password']
);
$this->connection->setAttribute(PDO::ATTR_AUTOCOMMIT, false);
$this->connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdoQuery = $this->connection->prepare(
    $sparqlQuery,
    array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)
);
$pdoQuery->execute();

@HughWilliams

Can you provide a complete runnable program sample I can run locally, with instructions on use, to try and recreate this issue ?

Also, please confirm the Virtuoso version being used ?

@k00ni

Hey @HughWilliams,

the Virtuoso version is 07.20.3214, which i build myself using dpkg-buildpackage from the project github repository (tag 7.2.1 i think).

Here is some code to exploit the error/bug:

<?php

$dsn = 'VOS';
$username = 'dba';
$password = 'dba';

$connection = new PDO(
    'odbc:' . $dsn, $username, $password
);
$connection->setAttribute(PDO::ATTR_AUTOCOMMIT, false);
$connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdoQuery = $connection->prepare(
    "CALL DB.DBA.SPARQL_EVAL('define output:format "JSON" SELECT DISTINCT ?g WHERE { GRAPH ?g { ?s ?p ?o }}', 'NULL', 0)",
    array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)
);
$pdoQuery->execute();

It will lead to the following output:

Fatal error: Uncaught exception ‘Exception’ with message ‘SQLSTATE[22023]: Invalid parameter value: -1 [OpenLink][Virtuoso iODBC Driver][Virtuoso Server]SR540: procedure view’s procedure returned value of type IRI_ID (dtp 243) instead of IRI_ID (dtp 244) for column (inx: 1) (SQLExecute[4294967295] at /build/php5-RpYHCf/php5-5.5.9+dfsg/ext/pdo_odbc/odbc_stmt.c:254)’

and

Exception: SQLSTATE[22023]: Invalid parameter value: -1 [OpenLink][Virtuoso iODBC Driver][Virtuoso Server]SR540: procedure view’s procedure returned value of type IRI_ID (dtp 243) instead of IRI_ID (dtp 244) for column (inx: 1) (SQLExecute[4294967295] at /build/php5-RpYHCf/php5-5.5.9+dfsg/ext/pdo_odbc/odbc_stmt.c:254)

@HughWilliams

Thanks for the sample program, with which I have been able to recreate this issue and reported to development to look into:

hwilliams@ubuntu:~$ php -f php.php 
PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[22023]: Invalid parameter value: -1 [OpenLink][Virtuoso iODBC Driver][Virtuoso Server]SR540: procedure view's procedure returned value of type IRI_ID (dtp 243) instead of IRI_ID (dtp 244) for column  (inx: 1) (SQLExecute[4294967295] at /build/php5-RpYHCf/php5-5.5.9+dfsg/ext/pdo_odbc/odbc_stmt.c:254)' in /home/hwilliams/php.php:18
Stack trace:
#0 /home/hwilliams/php.php(18): PDOStatement->execute()
#1 {main}
  thrown in /home/hwilliams/php.php on line 18
hwilliams@ubuntu:~$

@HughWilliams

Actually the same error occurs running the query via isql so it is not related to the use of PDO ODBC:

SQL> CALL DB.DBA.SPARQL_EVAL('define output:format "JSON" SELECT DISTINCT ?g WHERE { GRAPH ?g { ?s ?p ?o }}', 'NULL', 0);

*** Error 22023: VD [Virtuoso Server]SR540: procedure view's procedure returned value of type IRI_ID (dtp 243) instead of IRI_ID (dtp 244) for column  (inx: 1)
at line 26 of Top-Level:
CALL DB.DBA.SPARQL_EVAL('define output:format "JSON" SELECT DISTINCT ?g WHERE { GRAPH ?g { ?s ?p ?o }}', 'NULL', 0)
SQL>

Looking at the definition to the SPARQL_EVAL procedure it states return SQL result set i.e. in tabular for , but you are requesting JSON output ?

http://docs.openlinksw.com/virtuoso/fn_sparql_eval.html

If json is removed then the results are returned 👍

SQL> CALL DB.DBA.SPARQL_EVAL('SELECT DISTINCT ?g WHERE { GRAPH ?g { ?s ?p ?o }}', 'NULL', 0);
g
LONG VARCHAR
_______________________________________________________________________________

http://www.openlinksw.com/schemas/virtrdf#
http://www.w3.org/ns/ldp#
http://localhost:8890/sparql
.
.
.
http://localhost:8890/sys
http://localhost:8890/phpBB3_v/
http://localhost:8890/oracle#

Set 1, 282 Rows.
1 sets, 1379 msec.
RESULT=0
SQL>

@k00ni

Thanks for the clarification! Because it seems the error is on our side, its not a bug of Virtuoso, .. so issue closed.

Источник

On 03/02/2018 06:42 AM, Alexander Farber wrote:
> Hi Adrian, I 100% agree that nobody except me should debug my huge
> stored function, but if you look at my PostgreSQL 10.3 log —
>

Which proves what has already been proven, that at least some of the
data is correct. The issue is data that is not correct as evidenced by
the error message:

select mid, jsonb_array_length(tiles) from words_moves where gid=609;
ERROR: 22023: cannot get array length of a scalar
LOCATION: jsonb_array_length, jsonfuncs.c:1579

This is not going to get solved until you identify the ‘bad’ tiles data.

> 2018-03-02 15:30:33.646 CET [16693] LOG:  duration: 0.110 ms  parse
> <unnamed>: SELECT out_uid  AS uid,  out_fcm  AS fcm,  out_apns AS apns,
> out_adm  AS adm,  out_body AS body  FROM words_play_game($1::int,
> $2::int, $3::jsonb)
> 2018-03-02 15:30:33.646 CET [16693] LOG: duration: 0.058 ms  bind
> <unnamed>: SELECT out_uid  AS uid, out_fcm  AS fcm,  out_apns AS apns,
> out_adm  AS adm,  out_body AS body FROM words_play_game($1::int,
> $2::int, $3::jsonb)
> 2018-03-02 15:30:33.646 CET [16693] DETAIL:  parameters: $1 = ‘7’, $2 =
> ‘609’, $3 =
> ‘[{«col»:0,»letter»:»К»,»row»:3,»value»:2},{«col»:0,»letter»:»И»,»row»:4,»value»:1}]’
> 2018-03-02 15:30:33.646 CET [16693] LOG:  execute <unnamed>: SELECT
> out_uid AS uid,  out_fcm  AS fcm,  out_apns AS apns, out_adm  AS adm,
> out_body AS body  FROM words_play_game($1::int, $2::int, $3::jsonb)
> 2018-03-02 15:30:33.646 CET [16693] DETAIL:  parameters: $1 = ‘7’, $2 =
> ‘609’, $3 =
> ‘[{«col»:0,»letter»:»К»,»row»:3,»value»:2},{«col»:0,»letter»:»И»,»row»:4,»value»:1}]’
>
> I just pass as the 3rd argument in_tiles to my stored function:
> ‘[{«col»:0,»letter»:»К»,»row»:3,»value»:2},{«col»:0,»letter»:»И»,»row»:4,»value»:1}]’
>
> and then take the in_tiles and store it unchanged in the words_moves table:
>
>         INSERT INTO words_moves (
>                 action,
>                 gid,
>                 uid,
>                 played,
>                 tiles
>         ) VALUES (
>                 ‘play’,
>                 in_gid,
>                 in_uid,
>                 CURRENT_TIMESTAMP,
>                 in_tiles
>         ) RETURNING mid INTO STRICT _mid;
>
> Does anybody happen to see what could I do wrong there?
>
> Thank you for any hints
> Alex
>
> P.S: Here my stored fuinction:
> https://gist.github.com/afarber/88a832a1b90a8940764ad69b2b761914
>         Here my table:
> https://gist.github.com/afarber/06cc37114ff8dd14f05077f312904361
>         And here is how I call the stored function from Java:
>
>    String SQL_PLAY_GAME            =
>             «SELECT » +
>                 «out_uid  AS uid,  » +
>                 «out_fcm  AS fcm,  » +
>                 «out_apns AS apns, » +
>                 «out_adm  AS adm,  » +
>                 «out_body AS body  » +
>             «FROM words_play_game(?::int, ?::int, ?::jsonb)»;
>
>     private void handlePlay(int gid, String tiles) throws SQLException,
> IOException {
>         LOG.info(«handlePlay: {} -> {} {}», mUid, gid, tiles);
>         try (Connection db = DriverManager.getConnection(DATABASE_URL,
> DATABASE_USER, DATABASE_PASS);
>                 PreparedStatement st =
> db.prepareStatement(SQL_PLAY_GAME)) {
>             st.setInt(1, mUid);
>             st.setInt(2, gid);
>             st.setString(3, tiles);
>             runPlayerAction(st, gid);
>         }
>     }

—
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com

Источник

Понравилась статья? Поделить с друзьями:
  • Sql error 500051
  • Sql error 50000
  • Sql error 22018
  • Sql error 42p16 error cannot drop columns from view
  • Sql error 22012 error division by zero