Jam.py documentation

How can I use data from other database tables

You can use data from other database tables.

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 and specify the database connection attributes.

• Import tables information as described in the Integration with existing database

• Select project node in the task tree, click Database button restore previous values.

Then in the server module of the new items you must add code to read and write the data to the database

Below is the code for MySQL database (auto incremented primary field):

import MySQLdb
from jam.db import mysql

def on_open(item, params):
    connection = item.task.create_connection_ex(mysql, database='demo', \
        user='root', password='111', host='localhost', encoding='UTF8')
    try:
        sql = item.get_select_query(params, mysql)
        rows = item.task.select(sql, connection, mysql)
    finally:
        connection.close()
    return rows, ''

def on_apply(item, delta, params):
    connection = item.task.create_connection_ex(mysql, database='demo', \
        user='root', password='111', host='localhost', encoding='UTF8')
    try:
        sql = delta.apply_sql(params, mysql)
        result = item.task.execute(sql, None, connection, mysql)
    finally:
        connection.close()
    return result

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

You can use the task on_open and on_apply events. Below is the code from task client module:

import MySQLdb
from jam.db import mysql

def on_open(item, params):
    if item.item_name in ['table1', 'table2']: # or
    #if item.table_name in ['table1', 'table2']:
      connection = item.task.create_connection_ex(mysql, database='demo', \
          user='root', password='111', host='localhost', encoding='UTF8')
      try:
          sql = item.get_select_query(params, mysql)
          rows = item.task.select(sql, connection, mysql)
      finally:
          connection.close()
      return rows, ''

def on_apply(item, delta, params):
    if item.item_name in ['table1', 'table2']:
      connection = item.task.create_connection_ex(mysql, database='demo', \
          user='root', password='111', host='localhost', encoding='UTF8')
      try:
          sql = delta.apply_sql(params, mysql)
          result = item.task.execute(sql, None, connection, mysql)
      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.