mikra01 / nimodpi Goto Github PK

View Code? Open in Web Editor NEW

20.0 5.0 0.0 195 KB

Oracle ODPI-C wrapper for Nim

License: MIT License

Nim 100.00%

odpi-c nim nim-lang oracle

nimodpi's Introduction

nimodpi

Oracle ODPI-C wrapper for Nim

Oracle ODPI-C Documentation

dependencies

Oracle Instant Client (see ODPI-C Documentation for details)
nimterop

announcement

unfortunately nimterop does not compile with the latest nim release (2.x). Nimterop seems stale; due to that we decided to switch to futhark. The actual quickfix present is stay at nim version 1.6.

how to install

clone this project directly or use "nimble install nimodpi". then import the module "db_oracle" within your project. the dependent ODPI-C source is directly included into your project (via nimterop)

See the "isMainModule" section at the end of the module for some examples.

Besides the abstraction layer you can also consume the raw ODPI-C API if something is missing ( or PR me your solution ).

See /demo/demo.nim for some direct ODPI-C examples. The subdir /demo also contains an example how sqlplus could be utilised for script execution.

Remarks

upgraded to Nim Compiler Version devel (should also work with latest release), ODPI-C 4.3.0 and nimterop 0.6.11. The ODPI-C version is hardcoded within the static section of nimodpi.nim (nimterop control file) - don't forget to flush nimcache when changing the version between compile runs. The NIM-API could be subject of change.

Oracle XE installation

For testing just install a local copy of the oracle xe: https://www.oracle.com/database/technologies/appdev/xe.html

The oracle instant client is already included.

Tests only against windows 10 OS - if you face problems while installing xe onto your machine: the installer logs everything under the program files directory within the subdirectory /Oracle/Inventory/logs. Before installing make sure a third party virus scanner is disabled. (I faced no problems with the windows defender).

My database was created with the nls_character set "AL32UTF8"

To check your database characterset you could query for:

select DECODE(parameter, 'NLS_CHARACTERSET', 'CHARACTER SET',
'NLS_LANGUAGE', 'LANGUAGE',
'NLS_TERRITORY', 'TERRITORY') name,
value from v$nls_parameters
WHERE parameter IN ( 'NLS_CHARACTERSET', 'NLS_LANGUAGE', 'NLS_TERRITORY')
/

If you like to browse the schemas use Oracle SQL Developer for instance. Future examples will be based on the HR schema. This user is locked by default so the examples will only work if you use sysdba for the connecting user.

demo

before running the demo, adjust "/demo/democredentials.nim" (login and connection_string) for the database you like to connect to. No ddl is executed.

run the raw ODPI-C demo with "nimble demo".

run the nim demo with "nimble db_oracle". this demo executes some DDL and performs a cleanup.

Todo

direct bindings almost completed except SODA/BLOB/VARRAY; the nimish abstraction layer (db_oracle) is functional but more examples needed. next steps would be provide documented examples.

Comments, bug reports and PR´s appreciated.

nimodpi's People

Stargazers

Watchers

nimodpi's Issues

DPI-1020: application requires ODPI-C 4 (min 4.3) but is using a shared library at version 4.1

With the latest version I get the following problem:

db_oracle.nim(440)       newOracleContext
Error: unhandled exception: newOracleContext: (code: 0, offset16: 0, message: "DPI-1020: application requires ODPI-C 4 (min 4.3) but is using a shared library at version 4.1", messageLength: 94, encoding: "UTF-8", fnName: "dpiContext_createWithParams", action: "check version", sqlState: "HY000", isRecoverable: 0, isWarning: 0, offset: 0) [IOError]

IA64 in README.md

In README.md:

I tested it only for the IA64 architecture and only tested against nim-compiler version 1.0.2.

Did you confuse x86-64 with IA-64?

Not sure if I'm using this correctly

Looking at the when isMainModule block of db_odpi.nim I put this together.

import strformat

import nimodpi

const
  userName:         string = "{username}"
  password:         string = "{password}"
  server:           string = "{server}"
  port:             string = "{port}"
  serviceName:      string = "{servicename}"
  connectionString: string = fmt"""(DESCRIPTION = (ADDRESS = 
                    (PROTOCOL = TCP)
                    (HOST = {server})  
                    (PORT = {port}))
                    (CONNECT_DATA =(SERVER = DEDICATED)
                    (SERVICE_NAME = {serviceName} )
                  ))"""

var octx: OracleContext

newOracleContext(octx, DpiAuthMode.SYSDBA)

var conn: OracleConnection

createConnection(octx, connectionString, userName, password, conn)

let strSQL = """
select
  current_date
from dual
"""

But not even trying to execute the SQL, I get the following error.

~\.nimble\pkgs\nimodpi-0.1.0\nimodpi.nim(478, 8) template/generic instantiation of `cImport` from here
~\.choosenim\toolchains\nim-1.2.0\lib\core\macros.nim(632, 26) Error: undeclared identifier: 'dpiAuthMode'

Any advice?

BLOB/CLOB/LOB API routines missing

nimble demo not running

Nim version 1.6.0:

D:\SoftDevelopment\Projects\nimodpi>nimble demo
   Warning: Package 'nimodpi' has an incorrect structure. The top level of the package source directory should contain at most one module, named 'nimodpi.nim', but a file named 'db_oracle.nim' was found. This will be an error in the futu
re.
      Hint: If this is the primary source file in the package, rename it to 'nimodpi.nim'. If it's a source file required by the main module, or if it is one of several modules exposed by 'nimodpi', then move it into a 'nimodpi\' subdire
ctory. If it's a test file or otherwise not required to build the the package 'nimodpi.nim', prevent its installation by adding `skipFiles = @["db_oracle.nim"]` to the .nimble file. See https://github.com/nim-lang/nimble#libraries for mo
re info.
  Executing task demo in D:\SoftDevelopment\Projects\nimodpi\nimodpi.nimble
Hint: used config file 'C:\Software\nim-1.6.0\config\nim.cfg' [Conf]
Hint: used config file 'C:\Software\nim-1.6.0\config\config.nims' [Conf]
Hint: used config file 'D:\SoftDevelopment\Projects\nimodpi\config.nims' [Conf]
.........................................................
D:\SoftDevelopment\Projects\nimodpi\demo\demo.nim(1, 8) Error: cannot open file: ../nimodpi
stack trace: (most recent call last)
C:\Users\walte\AppData\Local\Temp\nimblecache-0\nimscriptapi_1162033381.nim(187, 16)
D:\SoftDevelopment\Projects\nimodpi\nimodpi.nimble(15, 8) demoTask
C:\Software\nim-1.6.0\lib\system\nimscript.nim(273, 7) exec
C:\Software\nim-1.6.0\lib\system\nimscript.nim(273, 7) Error: unhandled exception: FAILED: nim odpic_demo [OSError]
     Error: Exception raised during nimble script execution

D:\SoftDevelopment\Projects\nimodpi>

Release missing

We are planning to include Nim in our OraBench project and therefore need versioning of nimodpi.

Thank you for your support.!

Generic execute statement missing

Both statements executeDDL and executeStatement are based on the PreparedStatement. Thus it is not possible to execute e.g. the following compound statement including CREATE TABLE and CREATE TRIGGER:

SQL Statement:

BEGIN EXECUTE IMMEDIATE 'CREATE TABLE ora_bench_table (key VARCHAR2(32) PRIMARY KEY, data VARCHAR2(4000), no_partitions NUMBER DEFAULT 32, partition_key NUMBER(5)) PARTITION BY RANGE (partition_key) (PARTITION p00000 VALUES LESS THAN (1), PARTITION p00001 VALUES LESS THAN (2), PARTITION p00002 VALUES LESS THAN (3), PARTITION p00003 VALUES LESS THAN (4), PARTITION p00004 VALUES LESS THAN (5), PARTITION p00005 VALUES LESS THAN (6), PARTITION p00006 VALUES LESS THAN (7), PARTITION p00007 VALUES LESS THAN (8), PARTITION p00008 VALUES LESS THAN (9), PARTITION p00009 VALUES LESS THAN (10), PARTITION p00010 VALUES LESS THAN (11), PARTITION p00011 VALUES LESS THAN (12), PARTITION p00012 VALUES LESS THAN (13), PARTITION p00013 VALUES LESS THAN (14), PARTITION p00014 VALUES LESS THAN (15), PARTITION p00015 VALUES LESS THAN (16), PARTITION p00016 VALUES LESS THAN (17), PARTITION p00017 VALUES LESS THAN (18), PARTITION p00018 VALUES LESS THAN (19), PARTITION p00019 VALUES LESS THAN (20), PARTITION p00020 VALUES LESS THAN (21), PARTITION p00021 VALUES LESS THAN (22), PARTITION p00022 VALUES LESS THAN (23), PARTITION p00023 VALUES LESS THAN (24), PARTITION p00024 VALUES LESS THAN (25), PARTITION p00025 VALUES LESS THAN (26), PARTITION p00026 VALUES LESS THAN (27), PARTITION p00027 VALUES LESS THAN (28), PARTITION p00028 VALUES LESS THAN (29), PARTITION p00029 VALUES LESS THAN (30), PARTITION p00030 VALUES LESS THAN (31), PARTITION p00031 VALUES LESS THAN (32))'; EXECUTE IMMEDIATE 'CREATE TRIGGER ora_bench_table_before_insert BEFORE INSERT ON ora_bench_table FOR EACH ROW BEGIN :new.partition_key := MOD (ASCII (SUBSTR (:new.key, 1, 1)) * 251 + ASCII (SUBSTR (:new.key, 2, 1)), :new.no_partitions); END ora_bench_table_before_insert;'; END;

Resulting error message:

[01:19:30] ora_bench.nim - FATAL: runTrial() - Problem dropping the database table: executeDDL: statement is no DDL. please use executeStatement instead.

Source code:

  var sqlCreateSql: db_oracle.SqlQuery = osql paramsRunTrial.sqlCreateStr
  var sqlDropSql: db_oracle.SqlQuery = osql paramsRunTrial.sqlDropStr

  try:
    db_oracle.executeDDL(connections[0], sqlCreateSql)
    logger.log(lvlDebug, "last DDL statement=", paramsRunTrial.sqlCreateStr)
  except:
    try:
      db_oracle.executeDDL(connections[0], sqlDropSql)
      db_oracle.executeDDL(connections[0], sqlCreateSql)
      logger.log(lvlDebug, "last DDL statement after DROP=",
          paramsRunTrial.sqlCreateStr)
    except:
      logger.log(lvlFatal, "runTrial() - Problem dropping the database table: ",
          getCurrentExceptionMsg())