If this software was useful to you, you can help me to maintain it by a donation.
This module allows the use of the OpenEdge (Progress) databases in the Django Framework. It provides the ability to create tables from Django models, or to access existing tables (4GL or SQL) in your Django application.
It uses the OpenEdge ODBC connexion through pyodbc
The South integration permits to migrate the database schema.
You can directely use the pip command
sudo pip install pyodbcOpenEdge
You cas also download the installer from Pypi.
Then from the terminal (linux) or cmd (Win) go in the setup directory, and type
python setup.py install
The ODBC connexion is done through Pyodbc (see here )
In order to use pyodbcOpenEdge, you have to setup an odbc connexion.
First, You have to install the unixodbc package.
Once unixodbc is installed, you have to configure the /etc/odbcinst.ini like that
[progress]
Description = ODBC for Progress
Driver = /home/dlc102B/odbc/lib/pgoe1023.so
Setup = /home/dlc102B/odbc/lib/pgoe1023.so
DriverODBCVer = 3.50
APILevel = 1
ConnectFunctions = YYN
CPTimeout = 60
FileUsage = 0
SQLLevel = 0
UsageCount = 1
Diver and Setup are the absolute path name of the odbc libraries shipped with OpenEdge. The meaning of the others parameters are discribe here
The /etc/odbc.ini have to be configured like that
[Django]
Driver=progress
Servername=localhost
Username=django
Password=django
Database=mydb
Port=50000
Driver refers to the [progress] entry in the /etc/odbcinst.ini Port refers to the TCP Port used when the db server is run.
On linux systems, the pyodbc module have to be modifed in order to work with the OE db. So, you have to download the sources and apply the following modification:
The “Connection Attribute for Unicode” have to be modifed (see here):
- if (SQL_SUCCEEDED(ret)) + + if (SQL_SUCCEEDED(ret)) { + // TODO: check ODBC driver vendor here + if (true) { // DataDirect "application wants UCS-2" option: + Py_BEGIN_ALLOW_THREADS + ret = SQLSetConnectAttr(hdbc, 1040+21, (SQLPOINTER)1, SQL_IS_INTEGER); + Py_END_ALLOW_THREADS + if (!SQL_SUCCEEDED(ret)) + { + RaiseErrorFromHandle("SQLSetConnnectAttr(SQL_ATTR_APP_WCHAR_TYPE)", hdbc, SQL_NULL_HANDLE); + return 0; + } + } + return true; + }
Once this modification is done, you have to recompile the pyodbc module.
On Windows systems, no modifications is required.
Once OpenEdge, ODBC, pyodbc are configured, you can test the ODBC connexion with this sample program
import pyodbc,
import datetime,sys,os
def connectOpenedge(driver=None,dsn=None,host=None,db=None,uid=None,pwd=None,port=None):
cnxstring=';HOST=%s;DB=%s;UID=%s;PWD=%s;PORT=%s'%(host,db,uid,pwd,port)
if driver is not None:
cnxstring = 'DRIVER={%s}%s'%(driver,cnxstring)
else:
cnxstring = 'DSN=%s%s'%(dsn,cnxstring)
cnxn = pyodbc.connect(cnxstring)
cursor=cnxn.cursor()
try:
cursor.execute("SET SCHEMA 'PUB'")
except Exception as e:
raise pyodbc.DatabaseError(e)
exc_type, exc_obj, exc_tb = sys.exc_info()
fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1]
print(exc_type, fname, exc_tb.tb_lineno)
raise pyodbc.Error
cnxn.commit()
r=cursor.execute('SELECT TOP 1 * FROM "mytable" ')
print cursor.description
print r.fetchall()
return
if __name__ == '__main__':
## DNS connexion
connectOpenedge(dsn='django',host='localhost',db='mydb',uid='django',pwd='django',port='50000')
## Driver connexion
connectOpenedge(driver='django',host='localhost',db='mydb',uid='django',pwd='django',port='50000')
This program should display the datatype of the mytable’s fields, and the first row of the table.
In order to connect to the OE db with ODBC, the file $DLC/odbc/PGODBC.LIC must be copied in the current directory, otherwise, the following error will raised
pyodbc.DatabaseError: ('60', '[60] [DataDirect][ODBC 20101 driver]6060 (6060) (SQLExecDirectW)')
Sample configuration
DBENGINE='OpenEdge.pyodbc' ## Progress
DBNAME='mydb'
DBUSER='django'
DBPASSWORD='password'
DBHOST='localhost'
DBPORT='50000'
TYPECNX={'DSN':'mydb'}
DEFAULTSCHEMA = 'PUB'
CPINTERNAL='cp1252'
DATABASES = {
'default': {
'ENGINE': DBENGINE,
'NAME': DBNAME,
'TYPECNX' : TYPECNX,
'DEFAULTSCHEMA' : DEFAULTSCHEMA,
'USER': DBUSER,
'PASSWORD': DBPASSWORD,
'HOST': DBHOST,
'PORT': DBPORT,
'CPINTERNAL' : CPINTERNAL,
}
}
#===============================================================================
# South support
#===============================================================================
SOUTH_DATABASE_ADAPTERS = {'default':'OpenEdge.south.OpenEdge'}
TYPECNX can be DSN or DRIVER. If DNS is used, then both /etc/odbc.ini and /etc/odbcinst.ini have to be configured, and the value given is the entry in the /etc/odbc.ini file. If DRIVER is use, only the /etc/odbcinst.ini have to be configured, and the value given is the entry in the /etc/odbcinst.ini file.
On windows, ONLY DRIVER can be used, The driver name is taken from the registry HKEY_LOCAL_MACHINE/SOFTWARE/ODBC/ODBCINST.INI Driver Subkey
DEFAULTSCHEMA is used to perform queries without prefixing the tables name with schema name.
CPINTERNAL This is the database codepage used by OpenEdge, default value is iso8859-1. Change this value if you have accentued characters issues.
In order to use multi database in Django , see this documentation.
Below, an example of confgiuration with One Postgres Db and One OpenEdge Db. The default Db is Postgres
######### OpenEdge Db Conf ##############
DBENGINEOE='OpenEdge.pyodbc' ## Progress
DBNAMEOE='thetwo'
DBUSEROE='django'
DBPASSWORDOE='django'
DBHOSTOE='localhost'
DBPORTOE='50000'
TYPECNX={'DSN':'django'}
DEFAULTSCHEMA = 'PUB'
CPINTERNAL='cp1252'
######### Postgres Db Conf ##############
DBENGINE='django.db.backends.postgresql_psycopg2'
DBNAME='theone'
DBUSER='smith'
DBPASSWORD='smith'
DBHOST='localhost'
DBPORT=''
######### Db's config #####################
DATABASES = {
'default': {
'ENGINE': DBENGINE, # Add 'postgresql_psycopg2', 'mysql', 'sqlite3' or 'oracle'.
'NAME': DBNAME, # Or path to database file if using sqlite3.
'TYPECNX' : TYPECNX,
'DEFAULTSCHEMA' : DEFAULTSCHEMA, #Default OpenEdge Schema
'USER': DBUSER, # Not used with sqlite3.
'PASSWORD': DBPASSWORD, # Not used with sqlite3.
'HOST': DBHOST, # Set to empty string for localhost. Not used with sqlite3.
'PORT': DBPORT, # Set to empty string for default. Not used with sqlite3.
},
'openedge': {
'ENGINE': DBENGINEOE, # Add 'postgresql_psycopg2', 'mysql', 'sqlite3' or 'oracle'.
'NAME': DBNAMEOE, # Or path to database file if using sqlite3.
'TYPECNX' : TYPECNX,
'DEFAULTSCHEMA' : DEFAULTSCHEMA, #Default OpenEdge Schema
'USER': DBUSEROE, # Not used with sqlite3.
'PASSWORD': DBPASSWORDOE, # Not used with sqlite3.
'HOST': DBHOSTOE, # Set to empty string for localhost. Not used with sqlite3.
'PORT': DBPORTOE, # Set to empty string for default. Not used with sqlite3.
'CPINTERNAL' : CPINTERNAL, # Internal db codepage
}
}
#### Db's routing
DATABASE_ROUTERS = ['routers.OeRouter']
Sample router configuration
__init__.py
from defrouters import OeRouter
defrouters.py
class OeRouter(object):
"""
A router to control all database operations on models in the
oeapp application.
"""
def db_for_read(self, model, **hints):
"""
Attempts to read oeapp models go to openedge.
"""
if model._meta.app_label == 'oeapp':
return 'openedge'
return None
def db_for_write(self, model, **hints):
"""
Attempts to write oeapp models go to openedge.
"""
if model._meta.app_label == 'oeapp':
return 'openedge'
return None
Sample OpenEdge model configuration
from django.db import models
from OpenEdge.OEmodels.OpenEdgeExtentField import OpenEdgeExtentField
class Mytable(models.Model):
name = models.CharField(max_length=60, blank=True,primary_key=True)
chartab = OpenEdgeExtentField(max_length=8, blank=True,OEextents=5,OEtype='char',verbose_name='fieldext')
class Meta:
db_table = 'myTable'
app_label = 'oeapp'
In the above sample, chartab is a custom field which is able to handle OenEdge fields with extents (details here ).
There is no particularity, tables are automaticaly created by Django from models.
Model example
class MasterTable(models.Model):
name = models.CharField(max_length=60, blank=True)
adress = models.DecimalField(null=True, max_digits=7, decimal_places=2, default=0.0)
class Slave1(models.Model):
car = models.CharField(max_length=60, blank=True)
master = models.ManyToManyField(MasterTable)
class Slave2(models.Model):
boat = models.CharField(max_length=60, blank=True)
master = models.ForeignKey(MasterTable)
This section describes how to generate Django Models from existing tables.
Use the manage.py inspectdb command to get the tables definitions to be generated as Django’s models
python manage.py inspectdb > OEmodels.py
This step has to be done against a settings.py file, which must contains only the OpenEdge database definition.
The OEmodels.py file must be customized to handle only the desired tables and fields, and to configure the fields with extent (details here ).
The generated file look like this
class Alltype(models.Model):
chartype = models.CharField(max_length=16, blank=True)
inttype = models.IntegerField(null=True, blank=True)
dectype = models.DecimalField(null=True, max_digits=17, decimal_places=2, blank=True)
datetype = models.DateField(null=True, blank=True)
logtype = models.BooleanField(null=True, blank=True)
blobtype = models.TextField(blank=True) # This field type is a guess.
int64type = models.BigIntegerField(null=True, blank=True)
rawtype = models.TextField(blank=True) # This field type is a guess.
clobtype = models.CharField(max_length=4194304, blank=True)
detetimetype = models.DateTimeField(null=True, blank=True)
datetimetztype = models.CharField(max_length=32, blank=True)
class Meta:
db_table = 'alltype
The comment # This field type is a guess. means that the original type is not handle by the Django Backend, and that a generic type is used.
If no column ID with primary unique key property exists in the table, you have to define a column with the primary_key=True property in order to be able to query this table in then Django admin app . (see limitations )
name = models.CharField(max_length=60, blank=True,primary_key=True)
When the ODBC driver reads a table which contains a field with extents, the values are handled like a characters string where each extent is separated by a semi-colon (;).
Even if the field’s type is integer or decimal, the values will be returned as a characters string.
To handle this behavior, you can use the custom field OpenEdgeExtentField shipped with the pyodbcOpenEdge backend.
To use it, you have to import the module inthe models.py
from OpenEdge.OEmodels.OpenEdgeExtentField import OpenEdgeExtentField
Then, in your Django model, you can define an extent field as below
fieldext = OpenEdgeExtentField(max_length=8, blank=True,OEextents=5,OEtype='char',verbose_name='fieldext')
The OEextents represents the number of extent for this field in the OpenEdge Db.
The OEtype represents the original type in OpenEdge
When the model is used, the field will be rendered as a python list, made of OEextents elements.
Each elements will be of a python type as close as posible than the original one.
The following table gives the equivalences OpenEdge - Python
| OpenEdge Type | Python type |
|---|---|
| char | str |
| int | int |
| log | bool |
| dec | decimal.Decimal |
| int64 | long |
| datetime | datetime.datetime |
| date | datetime.date |
To be able to use the Django admin app against the OpenEdge tables, the models used must have a primary unique key column.
Django does not support the multicolumns primary key, so, if the primary unique key for an OpenEgde table is of this type, you will not be able to handle this tables in the admin app.
If a column contains data which overflow the column width , then, the ODBC Driver will raise an error
pyodbc.Error: ('60', '[60] [DataDirect][ODBC 20101 driver][20101]Column mycolumn in table PUB.mytable has value exceeding its max length or precision. (-210012) (SQLFetch)')
To control your db, you can use the $DLC/bin/dbtool, menu 1. This will let you know and then fix the columns which overflows.
The reading of a field DATETIME-TZ in a 4GL table through the SQL driver causes an error
/home/smith/dlc102B/bin/_sqlsrv2: free(): invalid next size (fast)