Largest Provider of Commercial Smalltalk
Cincom is one of the largest commercial providers of Smalltalk, with twice as many customers and partners as other commercial providers.

Tom Nies

Get Started

Menu

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

Posted on by Author Jeremy Jordan 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.

 

Related posts:

  1. Two Ways to Handle Date, Time and Timestamp Data Types with SQLite in Cincom VisualWorks Learning two ways to process date, time and timestamp data with SQLite....
  2. How to Handle Date, Time and Timestamp Data Types with SQLite in Cincom® VisualWorks® SQLite does not support built-in date, time and timestamp storage classes. Users normally make use of its built-in date and time functions to handle these data types. In this article, we’ll […]...
  3. How Can You Use the Cincom® VisualWorks® EXDIs from Cincom® ObjectStudio®? Since ObjectStudio and VisualWorks share the same foundation, ObjectStudio users have access to all functionality from both ObjectStudio and VisualWorks and have the opportunity to choose the appropriate features in both ObjectStudio and VisualWorks to meet their development needs....
  4. ODBC 3.0 Support in Cincom® VisualWorks® For historical reasons, most APIs that are used in VisualWorks’ ODBCEXDI belong to ODBC 1.0 and ODBC 2.0. ...