mapepire

README

Mapepire Golang Client SDK

mapepire-go is a golang client implementation for Mapepire that provides a simple interface for connecting to an IBM i server and running SQL queries. The client is designed to work with the Mapepire Server Component.

Setup

Install with go get

go get github.com/deady54/mapepire-go

Server Component Setup

To use mapepire-go, you will need to have the Mapepire Server Component running on your IBM i server. Follow these instructions to set up the server component: Mapepire Server Installation

Example Usage

The following go program initializes a DaemonServer object that will be used to connect with the Server Component. A single SQLJob object is created to facilitate this connection from the client side. A query object is then initialized with a simple SELECT query which is finally executed.

package main

import (
	"log"

	mapepire "github.com/deady54/mapepire-go"
)

func main() {
	// Initialize credentials
	creds := mapepire.DaemonServer{
		Host:               "HOST",
		User:               "USER",
		Password:           "PASS",
		Port:               "8076",
		IgnoreUnauthorized: true,
	}

	// Establish connection
	job := mapepire.NewSQLJob("ID")
	job.Connect(creds)

	// Initialize and execute query
	query, _ := job.Query("SELECT * FROM employee")
	result, _ := query.Execute()

	// Access data and information
	log.Println(result.Success)
	log.Println(result.Metadata)
	log.Println(result.Data)
}

The result is a JSON object that contains the metadata and data from the query, which will be stored in the ServerResponse object. Here are the different fields returned:

• id field contains the query ID
• hasResults field indicates whether the query returned any results
• success field indicates whether the query was successful
• metadata field contains information about the columns returned by the query
• data field contains the results of the query
• isDone field indicates whether the query has finished executing
• updateCount field indicates the number of rows updated by the query (-1 if the query did not update any rows)
• error field contains errors, if any.

Query Options

In the QueryOptions object are some additional options for the query execution:

options := mapepire.QueryOptions{
		Rows:        5,
		Parameters:  [][]any{},
		TerseResult: false,
		IsCLcommand: false,
	}
query, _ := job.QueryWithOptions("SELECT * FROM employee", options)

Prepared Statements

Statements can be easily prepared and executed with parameters:

options := mapepire.QueryOptions{Parameters: [][]any{{"Max", "Olly"}}}
query, _ := job.QueryWithOptions("SELECT * FROM employee WHERE (name = ? OR name = ?)", options)
result, _ := query.Execute()

There can also be added more to the batch:

options := mapepire.QueryOptions{Parameters: [][]any{{"1264", "Mark"}, {"1265", "Tom"}}}
query, _ := job.QueryWithOptions("INSERT INTO employee (ID, NAME) VALUES (?, ?)", options)
result, _ := query.Execute()

CL Commands

CL commands can be easily run by setting the IsCLcommand option to be true on the QueryOptions object or by directly using the CLCommand function on a job.

query, _ := job.ClCommand("CRTLIB LIB(MYLIB1) TEXT('My cool library')")
result, _ := query.Execute()

Pooling

To streamline the creation and reuse of SQLJob objects, your application should establish a connection pool on startup. This is recommended as connection pools significantly improve performance as it reduces the number of connection object that are created.

A pool can be initialized with a given starting size and maximum size. Once initialized, the pool provides APIs to access a free job or to send a query directly to a free job.

// Create a pool with a max size of 5, starting size of 3 and maximum wait time of 1 second
options := mapepire.PoolOptions{Creds: creds, MaxSize: 5, StartingSize: 3, MaxWaitTime: 1}
pool, _ := mapepire.NewPool(options)

// Initialize and execute query
resultChan, _ := pool.ExecuteSQL("SELECT * FROM employee")
result := <-resultChan

// Close pool and jobs
pool.Close()

JDBC Options

When specifying the credentials in the DaemonServer object, JDBC options can be defined in the Properties field. For a full list of all options, check out the documentation here.

creds := mapepire.DaemonServer{
		Host:               "HOST",
		User:               "USER",
		Password:           "PASS",
		Port:               "8076",
		IgnoreUnauthorized: true,
		Properties:         "prompt=false;translate binary=true;naming=system"
	}

Tracing Options

Tracing can be achieved by setting the configuration level and destination of the same tracer.

• Tracelevel (jtopentracelevel): OFF, ON, ERRORS, DATASTREAM, INPUT_AND_ERRORS
• Tracedest (jtopentracedest): file, in_mem

// Establish connection
job := mapepire.NewSQLJob("ID")
job.Connect(creds)

// Set Trace configuration
options := mapepire.TraceOptions{Tracelevel: "ERRORS", Tracedest: "file"}
job.SetTraceConfig(options)

// Receive Trace data
job.GetTraceData()

Secure Connections

By default, Mapepire will always try to connect securely. A majority of the time, servers are using their own self-signed certificate that is not signed by a recognized CA (Certificate Authority). There is currently only one option with the Golang client.

Allow all Certificates

On the DaemonServer object, the IgnoreUnauthorized option can be set to true which will allow either self-signed certificates or certificates from a CA.

Validate Self-signed Certificates

[!WARNING] Validation of self-signed certificates is currently not supported.

Documentation

Index

• Constants
• type DaemonServer
• type JobPool
  • func NewPool(options PoolOptions) (*JobPool, error)
  • func (jp *JobPool) AddJob(s *SQLJob) error
  • func (jp *JobPool) Close()
  • func (jp *JobPool) ExecuteSQL(sql string) (*ServerResponse, error)
  • func (jp *JobPool) ExecuteSQLWithOptions(command string, queryops QueryOptions) (*ServerResponse, error)
  • func (jp *JobPool) GetJob() (s *SQLJob, err error)
  • func (jp *JobPool) GetJobCount() int
• type PoolOptions
• type Query
  • func (q *Query) Execute() (*ServerResponse, error)
  • func (q *Query) FetchMore(contID string, rows string) (*ServerResponse, error)
  • func (q *Query) SQLClose(contID string) error
• type QueryOptions
• type SQLJob
  • func NewSQLJob(ID string) *SQLJob
  • func (s *SQLJob) ClCommand(command string) (*Query, error)
  • func (s *SQLJob) Close() error
  • func (s *SQLJob) Connect(server DaemonServer) error
  • func (s *SQLJob) GetStatus() string
  • func (s *SQLJob) GetTraceData() error
  • func (s *SQLJob) GetVersion() (string, error)
  • func (s *SQLJob) Query(sql string) (*Query, error)
  • func (s *SQLJob) QueryWithOptions(command string, options QueryOptions) (*Query, error)
  • func (s *SQLJob) SetTraceConfig(ops TraceOptions) error
• type ServerError
  • func (e *ServerError) Error() string
• type ServerResponse
• type TraceOptions
• type WebsocketError
  • func (e *WebsocketError) Error() string

Constants

const (
	STATE_RUN_DONE = iota
	STATE_RUN_MORE_DATA
	STATE_NOT_YET_RUN
)

const (
	JOBSTATUS_BUSY        = "BUSY"
	JOBSTATUS_ERROR       = "ERROR"
	JOBSTATUS_ENDED       = "ENDED"
	JOBSTATUS_READY       = "READY"
	JOBSTATUS_CONNECTING  = "CONNECTING"
	JOBSTATUS_NOT_STARTED = "NOT_STARTED"
)

const (
	DEFAULT_FETCH_SIZE = 100
	MAX_FETCH_SIZE     = 1000
)

Types

type DaemonServer

type DaemonServer struct {
	Host               string // Hostname or IP
	User               string // Username for authentication
	Password           string // Password for authentication
	Port               string // Default port is 8076
	IgnoreUnauthorized bool   // Ignore unauthorized certificate
	Technique          string // CLI or TCP
	Properties         string // A semicolon-delimited list of JDBC connection properties
}

Represents a DB2 server daemon with connection details.

type JobPool

type JobPool struct {
	// contains filtered or unexported fields
}

Connection pool

func NewPool

func NewPool(options PoolOptions) (*JobPool, error)

Create a new pool object

func (*JobPool) AddJob

func (jp *JobPool) AddJob(s *SQLJob) error

Add a job back to the pool

func (*JobPool) Close

func (jp *JobPool) Close()

Closes the pool and its jobs

func (*JobPool) ExecuteSQL

func (jp *JobPool) ExecuteSQL(sql string) (*ServerResponse, error)

Execute a SQL query with a job from the pool

func (*JobPool) ExecuteSQLWithOptions

func (jp *JobPool) ExecuteSQLWithOptions(command string, queryops QueryOptions) (*ServerResponse, error)

Execute a SQL query with options, using a job from the pool

func (*JobPool) GetJob

func (jp *JobPool) GetJob() (s *SQLJob, err error)

Receive a job from the pool

func (*JobPool) GetJobCount

func (jp *JobPool) GetJobCount() int

Receive the count of jobs that have been initialized by the pool

type PoolOptions

type PoolOptions struct {
	Creds        DaemonServer // Credentials to connect to the server
	MaxWaitTime  int          // Max time to wait for a job (in seconds)
	MaxSize      int          // Pool max size
	StartingSize int          // Pool starting count
}

Represents the options for configuring a connection pool

type Query

type Query struct {
	ID string // The unique identifier
	// contains filtered or unexported fields
}

Represents a SQL Query that can be executed and managed within a SQL job

func (*Query) Execute

func (q *Query) Execute() (*ServerResponse, error)

Executes the query/command and returns the results

func (*Query) FetchMore

func (q *Query) FetchMore(contID string, rows string) (*ServerResponse, error)

Fetch more rows from a previous request with the ID

func (*Query) SQLClose

func (q *Query) SQLClose(contID string) error

Close cursor from a previous request. Select querys are automatically closed after fetching all data.

type QueryOptions

type QueryOptions struct {
	Rows        int     // The amount of rows to fetch
	Parameters  [][]any // Parameters, if any
	TerseResult bool    // Whether the result returns in terse format
	IsCLcommand bool    // Whether the command is a CL command
}

Represents options for query execution

type SQLJob

type SQLJob struct {
	ID      string        // Unique identifier
	Jobname string        // Name of the Job
	Status  string        // Status of the Job
	Options *TraceOptions // Trace configuration options
	// contains filtered or unexported fields
}

Represents a SQL job that manages connections and queries to a database.

func NewSQLJob

func NewSQLJob(ID string) *SQLJob

Receive a new SQL job with the given ID

func (*SQLJob) ClCommand

func (s *SQLJob) ClCommand(command string) (*Query, error)

Creates a query with the CL command

func (*SQLJob) Close

func (s *SQLJob) Close() error

Closes the SQL job and websocket connection.

func (*SQLJob) Connect

func (s *SQLJob) Connect(server DaemonServer) error

Creates a websocket connection and connects to the server.

func (*SQLJob) GetStatus

func (s *SQLJob) GetStatus() string

Receive the current job status

func (*SQLJob) GetTraceData

func (s *SQLJob) GetTraceData() error

Receive trace data (after setting config)

func (*SQLJob) GetVersion

func (s *SQLJob) GetVersion() (string, error)

Receive the current version info

func (*SQLJob) Query

func (s *SQLJob) Query(sql string) (*Query, error)

Creates a query with the SQL

func (*SQLJob) QueryWithOptions

func (s *SQLJob) QueryWithOptions(command string, options QueryOptions) (*Query, error)

Creates a query with the given options

func (*SQLJob) SetTraceConfig

func (s *SQLJob) SetTraceConfig(ops TraceOptions) error

Set trace configuration options

type ServerError

type ServerError struct {
	Method  string
	Message string
}

func (*ServerError) Error

func (e *ServerError) Error() string

type ServerResponse

type ServerResponse struct {
	ID             string // The unique identifier of the request
	Job            string // The name of the DB Job
	Success        bool   // Whether the request was successful
	Metadata       *metadata
	Data           []map[string]interface{}
	TerseData      [][]any `json:"terse_data"`      // Data in terse format if specified
	HasResults     bool    `json:"has_results"`     // Whether the response has results
	IsDone         bool    `json:"is_done"`         // Whether the query execution is complete
	ParameterCount int     `json:"parameter_count"` // The number of parameters in the prepared query
	UpdateCount    int     `json:"update_count"`    // The number of rows affected by the query (returns -1 if none)
	Error          error   // The error message, if any
	SqlState       string  // The SQL state code
	SqlRC          int     // The SQL error code
}

Represents the server response.

type TraceOptions

type TraceOptions struct {
	Tracelevel       string // OFF, ON, ERRORS, DATASTREAM, INPUT_AND_ERRORS
	Tracedest        string // One of (file, in_mem)
	Jtopentracelevel string // OFF, ON, ERRORS, DATASTREAM, INPUT_AND_ERRORS
	Jtopentracedest  string // One of (file, in_mem)
	// contains filtered or unexported fields
}

Represents trace configuration options

type WebsocketError

type WebsocketError struct {
	Method  string
	Message string
}

func (*WebsocketError) Error

func (e *WebsocketError) Error() string