<< Click to Display Table of Contents >> Navigation:  ThinBASIC Modules > ADODB > ADODB Module Classes > ADODB_Connection > ADODB_Connection Methods > <ADODB_Connection>.LookUpJson

 

Description

 
Execute an SQL statement and return data in a JSon format.

 

Syntax

 

s = <ADODB_Connection>.LookUpJson (sSQL [, JSonFormatType])

 

Returns

 

String.

 

Parameters

 

 

Remarks

 

This function is not a standard ADODB function but a personalized thinBasic function.

 

JSon output formats:

 

%adLookUpJson_Format_Flat

Output will be structured as an array of JSon objects each containing field name and field value pairs

 

Example from this statement

String sJSON = pConn.LookUpJSon("select top 3 * from Authors")

 

sJSON string will be like:

 
  [
     {
        "Au_ID":"1",
        "Author":"Adams, Pat",
        "Year Born":"2017",
        "Comments":""
     },
     {
        "Au_ID":"2",
        "Author":"Adrian, Merv",
        "Year Born":"",
        "Comments":""
     },
     {
        "Au_ID":"3",
        "Author":"Ageloff, Roy",
        "Year Born":"1943",
        "Comments":""
     }
  ]

 

%adLookUpJson_Format_Info

Output will be structured with some execution info and an array of JSon objects each containing field name and field value pairs.

Info reported:

1.status: "OK" or "KO" if some errors occurred

2.error: node present only if status is "KO"

a.level: nested error level

b.code: error code

c.description: error description

3.items: returns the number of objects inside data node

4.data: array of JSon objects composed by field name and field data pairs

 

Example from this statement

String sJSON = pConn.LookUpJSon("select top 3 * from Authors")

 

sJSON string will be like:

 

{
   "status":"OK",
   "items":"3",
   "data":[
      {
         "Au_ID":"1",
         "Author":"Adams, Pat",
         "Year Born":"2017",
         "Comments":""
      },
      {
         "Au_ID":"2",
         "Author":"Adrian, Merv",
         "Year Born":"",
         "Comments":""
      },
      {
         "Au_ID":"3",
         "Author":"Ageloff, Roy",
         "Year Born":"1943",
         "Comments":""
      }
   ]
}

 

 

Restrictions

 

See also

 

Examples

 
Uses "Console"
Uses "ADODB"
uses "cjson"
 
String sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & APP_SourcePath & "Biblio.mdb"
 
Printl $("Connection string is: {sConn}")
 
'---Declare a new ADODB Connection object and open it
Dim pConn As New ADODB_CONNECTION
pConn.Open(sConn)
 
If pConn.State = %ADSTATEOPEN Then
   printl "Connection was OK"
 
  dim JSON     as new CCJson
  String sJSON = pConn.LookUpJSon("select * from Titles", %adLookUpJson_Format_Info)
   JSON.Parse(sJSON)
   
  printl "status......:", JSON.Path.Value("status")
  printl "items.......:", JSON.Path.Value("items")
 
  if JSON.Path.Value("status") = "OK" then
    for nRec as long = 1 to JSON.Path.Value("items")
      printl $"Record {nRec} out of {JSON.Path.Value(""items"")}" in %CCOLOR_FCYAN
      printl "  Title...........:" in %CCOLOR_FGREEN, JSON.Path.Value("data".(nRec)."Title")
      printl "  Year Published..:" in %CCOLOR_FGREEN, JSON.Path.Value("data".(nRec)."Year Published")
      printl "  ISBN........... :" in %CCOLOR_FGREEN, JSON.Path.Value("data".(nRec)."ISBN")
      printl "  Comments....... :" in %CCOLOR_FGREEN, JSON.Path.Value("data".(nRec)."Comments")
    Next
  Else
        printl "Error:", JSON.Path.Value("error"."description")
  end If
   
  printl
  PrintL "  pConnection.Close        :", pConn.CLOSE
 
Else
    PrintL "-It was not possible to open a connection-" In %CCOLOR_FLIGHTRED
End If
 
printl "---Press a key to finish---" in %CCOLOR_FINTENSEWHITE
WaitKey