With such a high productivity
factor that Smalltalk gives us, reaction times to market changes have enabled us to beat most of our competitors.

Dr. Colin Lewis, Vice-President, JPMorgan

How to Use ODBCEXDI to Handle JSON Data in Cincom® VisualWorks®

Posted on in Categories Smalltalk, VisualWorks

In the following sections, we’ll demonstrate how to use ODBCEXDI to handle JSON data in VisualWorks.


JSON Data in Oracle

In Oracle, the JSON column normally is defined as a CLOB column plus a check constraint to ensure that the text data is in JSON format.

Therefore, ODBCEXDI can be used to deal with Oracle JSON data without any modifications.

Here are the code examples:

“Connect to an Oracle server.”

conn := ODBCConnection new
           username: 'username';
           password: 'password';
         environment: 'DSNToOracle';
           yourself.
conn connect.

 

“Create a table with a JSON column.”

sess := conn getSession.
sess prepare: 'CREATE TABLE j_purchaseorder
   ( po_document CLOB CONSTRAINT ensure_json CHECK
   (po_document IS JSON))'.
sess execute.
sess answer.
sess answer.

 

“Inserting JSON data as part of the SQL statement.”

sess := conn getSession.
sess prepare: 'INSERT INTO j_purchaseorder
VALUES
(''
{
"name": "Apple Phone",
"type": "phone",
"brand": "ACME",
"price": 200,
"available": true,
"warranty_years": 1
}
'')'.
sess execute.
sess answer.
sess answer.

 

“Binding a nil for a JSON column.”

sess := conn getSession.
sess prepare: 'INSERT INTO j_purchaseorder VALUES (?)'.
sess bindInput: (Array with: nil).
sess execute.
sess answer.
sess answer.

 

“Binding a JSON string.”

sess := conn getSession.
sess prepare: 'INSERT INTO j_purchaseorder VALUES (?)'.
sess bindInput: (Array with: '{
"name": "Apple Phone",
"type": "phone",
"brand": "ACME",
"price": 200,
"available": true,
"warranty_years": 1
}
').
sess execute.
sess answer.
sess answer.

 

“Binding a nested JSON string.”

sess := conn getSession.
sess prepare: 'INSERT INTO j_purchaseorder VALUES (?)'.
sess bindInput: (Array with: '{"phone ": "not yet", "phone 1":
    {"name": "Apple Phone", "available": true, "type": "phone",
    "brand": "ACME", "warranty_years": 1, "price": 200}}
').
sess execute.
sess answer.
sess answer.

 

“Binding a normal string not in JSON format will cause an error.”

sess := conn getSession.
sess prepare: 'INSERT INTO j_purchaseorder VALUES (?)'.
sess bindInput: (Array with: '{
"name"}
').
sess execute.
sess answer.
sess answer.

 

“Selecting all of the inserted data to verify.”

sess := conn getSession.
sess prepare: 'SELECT * FROM j_purchaseorder'.
sess execute.
ans := sess answer.
ans upToEnd inspect.
sess answer.

 

“Selecting item contents from the JSON data.”

sess := conn getSession.
sess prepare: 'SELECT po.po_document.name FROM j_purchaseorder po'.
sess execute.
ans := sess answer.
ans upToEnd inspect.
sess answer.

JSON, JSONB and HSTORE data in PostgreSQL

Since all of the JSON, JSONB and HSTORE data are treated as text by the PostgreSQL ODBC driver, we don’t have to use bindTemplate to indicate their data types in ODBCEXDI.

The following are the code examples for accessing PostgreSQL’s JSON, JSONB and HSTORE data from ODBCEXDI:

 

“Connect to a PostgreSQL server.”

aConnection := ODBCConnection new.
aConnection
           environment: 'DSNToPostgreSQL';
           username: 'username';
           password: 'password';
           connect.

 

“Create a test table with a Json column.”

aSession := aConnection getSession.
aSession prepare: 'CREATE table json_data (data JSON)'.
aSession execute.
answer := aSession answer.
answer := aSession answer.

 

“Insert nil into a Json column.”

aSession := aConnection getSession.
aSession prepare: 'INSERT INTO json_data (data) VALUES ((?))'.
aSession bindInput: (Array with: nil).
aSession execute.
answer := aSession answer.
answer := aSession answer.

 

“Test Json data as part of the SQL statement.”

aSession := aConnection getSession.
aSession prepare: 'INSERT INTO json_data (data)
VALUES
(''
{
"name": "Apple Phone",
"type": "phone",
"brand": "ACME",
"price": 200,
"available": true,
"warranty_years": 1
}
'')'.
aSession execute.
answer := aSession answer.
answer := aSession answer.

 

“Create a Json string as input.”

aJsonString :='{
"name": "Apple Phone",
"type": "phone",
"brand": "ACME",
"price": 200,
"available": true,
"warranty_years": 1
}'.

 

“Bind the JSON string to insert.”

aSession := aConnection getSession.
aSession prepare: 'INSERT INTO json_data (data) VALUES ((?))'.
aSession bindInput: (Array with: aJsonString).
aSession execute.
answer := aSession answer.
answer := aSession answer.

 

“Create a nested Json string as input.”

aJsonString1 :='{"phone ": "not yet", "phone 1": {"name":
"Apple Phone", "available": true, "type": "phone", "brand":
"ACME", "warranty_years": 1, "price": 200}}'.

 

“Bind the JSON string to insert.”

aSession := aConnection getSession.
aSession prepare: 'INSERT INTO json_data (data) VALUES ((?))'.
aSession bindInput: (Array with: aJsonString1).
aSession execute.
answer := aSession answer.
answer := aSession answer.

 

“Retrieve the contents from the database to verify.”

aSession := aConnection getSession.
aSession prepare: 'SELECT * from json_data'.
aSession execute.
answer := aSession answer.
result := answer upToEnd.
result inspect.
answer := aSession answer.

 

“Test Jsonb.”

“Create a test table with a Jsonb column.”

aSession := aConnection getSession.
aSession prepare: 'CREATE table jsonb_data (data JSONB)'.
aSession execute.
answer := aSession answer.
answer := aSession answer.

 

“Test nil.”

aSession := aConnection getSession.
aSession prepare: 'INSERT INTO jsonb_data (data) VALUES ((?))'.
aSession bindInput: (Array with: nil).
aSession execute.
answer := aSession answer.
answer := aSession answer.

 

“Test Json data as part of the SQL statement.”

aSession := aConnection getSession.
aSession prepare: 'INSERT INTO jsonb_data (data)
VALUES
(''
{
"name": "Apple Phone",
"type": "phone",
"brand": "ACME",
"price": 200,
"available": true,
"warranty_years": 1
}
'')'.
aSession execute.
answer := aSession answer.
answer := aSession answer.

 

“Create a Json string as input.”

aJsonString :='{
"name": "Apple Phone",
"type": "phone",
"brand": "ACME",
"price": 200,
"available": true,
"warranty_years": 1
}'.

 

“Bind the JSON string to insert.”

aSession := aConnection getSession.
aSession prepare: 'INSERT INTO jsonb_data (data) VALUES ((?))'.
aSession bindInput: (Array with: aJsonString).
aSession execute.
answer := aSession answer.
answer := aSession answer.

 

“Create a nested Json string as input.”

aJsonString1 :='{"phone ": "not yet", "phone 1": {"name": "Apple Phone",
"available": true, "type": "phone", "brand": "ACME", "warranty_years":
1, "price": 200}}'.

 

“Bind the JSON string to insert.”

aSession := aConnection getSession.
aSession prepare: 'INSERT INTO jsonb_data (data) VALUES ((?))'.
aSession bindInput: (Array with: aJsonString1).
aSession execute.
answer := aSession answer.
answer := aSession answer.

 

“Retrieve the contents from the database to verify.”

aSession := aConnection getSession.
aSession prepare: 'SELECT * from jsonb_data'.
aSession execute.
answer := aSession answer.
result := answer upToEnd.
result inspect.
answer := aSession answer.

 

“Test Hstore.”

“Create a test table with a Hstore column.”

aSession := aConnection getSession.
aSession prepare: 'CREATE TABLE hstore_data (data HSTORE)'.
aSession execute.
answer := aSession answer.
answer := aSession answer.

 

“Test nil.”

aSession := aConnection getSession.
aSession prepare: 'INSERT INTO hstore_data (data) VALUES ((?))'.
aSession bindInput: (Array with: nil).
aSession execute.
answer := aSession answer.
answer := aSession answer.

 

“Test hstore data as part of the SQL statement.”

aSession := aConnection getSession.
aSession prepare: 'INSERT INTO hstore_data (data) VALUES
(''
"cost"=>"500",
"product"=>"iphone",
"provider"=>"apple"
'')'.
aSession execute.
answer := aSession answer.
answer := aSession answer.

 

“Create a Hstore string as input.”

aHstoreString :='"cost"=>"500",
"product"=>"iphone",
"provider"=>"apple"'.

 

“Test binding Hstore string.”

aSession := aConnection getSession.
aSession prepare: 'INSERT INTO hstore_data (data) VALUES(?)'.
aSession bindInput: (Array with: aHstoreString).
aSession execute.
answer := aSession answer.
answer := aSession answer.

 

“Retrieve the contents from the database to verify.”

aSession := aConnection getSession.
aSession prepare: 'SELECT * from hstore_data'.
aSession execute.
answer := aSession answer.
result := answer upToEnd.
result inspect.
answer := aSession answer.