How can I use data from some other database(s) tables¶

You can use data from other database tables. It is not possible to use fields as Lookups on other tables this way.

First you must specify table name and fields information. You can do it the following way:

Select project node in the task tree and click Database button.
Set DB manual mode.
Specify the database connection attributes for external database table.
Import tables information as described in the Integration with existing database.
Select project node in the task tree, click Database button and restore previous values.

The other method is to manually create each table and fields matching the source. The DB manual mode must be set.

Then in the Server module for the new items, add code to read and write the data to the database tables by using on_open and on_apply.

Below code was tested with V7 for MySQL, MSSQL and PostgreSQL database (auto incremented primary field):

from jam.db.mysql_db2 import db
from jam.items import QueryData

    def on_open(item, params):
        connection = item.task.create_connection_ex(
            db,
            database='jam2',
            user='jam',
            password='jam',
            server='localhost',
            port='3307',
        )

        try:
            query_data = QueryData(params)
            sql, sql_params = db.get_select_query(item, query_data)
            rows = item.task.select(sql, connection, db, sql_params)
        finally:
            connection.close()
        return rows, ''

    def on_apply(item, delta, params, con):
        connection = item.task.create_connection_ex(
            db,
            database='jam2',
            user='jam',
            password='jam',
            server='localhost',
            port='3307',
        )
        try:
            result = delta.apply_delta(delta, params, connection, db)
            connection.commit()
        finally:
            connection.close()
        return result

Import example for MSSQL, the rest of code as for MySQL:

from jam.db.mssql_db1 import db
from jam.items import QueryData
...

Import example for PostgreSQL, the rest of code as for MySQL:

from jam.db.postgres_db import db
from jam.items import QueryData

Note

The below procedure was not tested with Jam.py V7.

If database use generators to get primary field values, you must specify them for new records (Firebird):

import fdb
from jam.db import firebird

def on_open(item, params):
    connection = item.task.create_connection_ex(firebird, database='demo.fdb', \
        user='SYSDBA', password='masterkey', encoding='UTF8')
    try:
        sql = item.get_select_query(params, firebird)
        rows = item.task.select(sql, connection, firebird)
    finally:
        connection.close()
    return rows, ''

def get_id(table_name, connection):
    cursor = connection.cursor()
    cursor.execute('SELECT NEXT VALUE FOR "%s" FROM RDB$DATABASE' % (table_name + '_SEQ'))
    r = cursor.fetchall()
    return r[0][0]

def on_apply(item, delta, params):
    connection = item.task.create_connection_ex(firebird, database='demo.fdb', \
        user='SYSDBA', password='masterkey', encoding='UTF8')
    for d in delta:
        if not d.id.value:
            d.edit()
            d.id.value = get_id(item.table_name, connection)
            for detail in d.details:
                for r in detail:
                    if not r.id.value:
                        r.edit()
                        r.id.value = get_id(r.table_name, connection)
                        r.post()
            d.post()
    try:
        sql = delta.apply_sql(params, firebird)
        result = item.task.execute(sql, None, connection, firebird)
    finally:
        connection.close()
    return result

Note

Do not set History attribute to True for this tables. If you do so you’ll get the exception. History table must be one for all databases that you use in the project. You can try to create the history table in the other database and write the on_open and on_apply event handlers for it.