How can I use data from other database tables

[Moh. Sarip Hidayat]

Hi everyone.

I'm working on a new project using jam.py-v7.

I would need to work with multiple MariaDB databases.

On v5, information available on this page is still accurate:
How can I use data from other database tables — Jam.py documentation

I tried to find the equivalent for v7 and found this one:
How can I use data from some other database(s) tables — Jam.py documentation

Unfortunately that doesn't work.

First problem I encounter is this line from the example does not work:

from jam.db import mysql

I look into the source code and came up with this line:

from jam.db import mysql_db as mysql

At least this one doesn't throw an error. However, I'm not sure if that would work because of the second problem.

ERROR - 'Task' object has no attribute 'create_connection_ex'
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/jam/wsgi.py", line 710, in on_api
    data = self.get_response(item, method, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/jam/wsgi.py", line 733, in get_response
    return item.select_records(params, client_request=True)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/jam/items.py", line 243, in select_records
    result = self.on_open(self, params)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "esm.catalogs.product_glasses", line 15, in on_open
    connection = item.task.create_connection_ex(
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
AttributeError: 'Task' object has no attribute 'create_connection_ex'. Did you mean: 'create_connection'?

It seems the create_connection_ex function does not exist anymore in v7.

I tried to follow the suggestion by replacing it with create_connection_ and got this error instead:

Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/gunicorn/workers/gthread.py", line 479, in handle
    keepalive = self.handle_request(req, conn)
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/gunicorn/workers/gthread.py", line 678, in handle_request
    respiter = self.wsgi(environ, resp.start_response)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/werkzeug/local.py", line 235, in application
    return ClosingIterator(app(environ, start_response), self.cleanup)
                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/werkzeug/middleware/shared_data.py", line 250, in __call__
    return self.app(environ, start_response)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/jam/wsgi.py", line 242, in __call__
    return self.on_api(request)(environ, start_response)
           ^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/jam/wsgi.py", line 710, in on_api
    data = self.get_response(item, method, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/jam/wsgi.py", line 733, in get_response
    return item.select_records(params, client_request=True)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/jam/items.py", line 243, in select_records
    result = self.on_open(self, params)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "esm.catalogs.product_glasses", line 15, in on_open
    mysql,

I'm well aware of this warning on the v7 documentation:

The above procedures were not tested with Jam.py V7.

However, I'm not sure what the following line means:

The simple solution is to use the database synonyms.

May I ask for your help on how to make this work?

Thank you.

Best Regards,

Moh. Sarip Hidayat

Visit my website at musahi0128.github.io

[Dean D. Babic]

I asked Andrew for create_connection_ex but he never replied with the solution.
Maybe to contact him?
Synonyms are just that. How to use them depends on the database.

[Dean D. Babic]

Here is a chat gpt. It touches  get_select_query,  build_select_query_from_params, etc:

https://chatgpt.com/share/6a0feb4d-7d6c-83ec-8b2a-2a956971e3ce

At the end, I managed to display the data, but not been able to open the form. 
The v5 was quite simple regarding this. But, it did not have the server side permissions.

At the end, I resorted to synonyms on the SAME MSSQL server:
https://groups.google.com/g/jam-py/c/a-EKfgqMkZg/m/jGfC9FzHAgAJ

Did not have issues as the user in the above thread tho.

[Dean D. Babic]

If you c/p this code in items.py, below def connect:
    def connect(self):
        return self.pool.connect()

    def create_connection_ex(self, db_adapter, database, user=None, password=None, \
        host=None, port=None, encoding=None, server=None):
       
        from jam.items import DBInfo
        db_info = DBInfo()
        db_info.server = server
        db_info.database = database
        db_info.user = user
        db_info.password = password
        db_info.port = port
       
        return db_adapter.connect(db_info)

it is possible to use create_connection_ex like this:

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)

print(f"queries: {sql}")

rows = item.task.select(sql, connection, db)

return rows, ''

finally:

connection.close()

localhost is completely different server with port 3307.

More work is need to open the Form and CRUD. But, it a start....

[Dean D. Babic]

So it looks like Jam V5 is passing ID to query but V7 is not.

V5:
All keys: ['__expanded', '__fields', '__order', '__filters', '__limit', '__offset', '__client_request']
{'__expanded': True, '__fields': ['artistid', 'name'], '__order': [], '__filters': [['artistid', 1, 1]], '__limit': 0, '__offset': 0, '__client_request': True}
queries: SELECT "ARTISTS"."ARTISTID", "ARTISTS"."NAME" FROM "ARTISTS" AS "ARTISTS" WHERE "ARTISTS"."ARTISTID" = 1
[[1, 'sdsa asdasd']]

In V7, becuse there is no = 1, sql Errors and Form does not open:

All keys: ['__expanded', '__fields', '__order', '__filters', '__limit', '__offset']
{'__expanded': True, '__fields': ['title', 'id'], '__order': [], '__filters': [['id', 1, 1]], '__limit': 0, '__offset': 0}
queries: SELECT "demo_albums"."title", "demo_albums"."id" FROM "demo_albums" AS "demo_albums" WHERE "demo_albums"."id" = %s
ERROR - Error: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s' at line 1

Both v5 and v7 has:

def on_open(item, params):

print("=== RAW PARAMS FROM CLIENT ===")

print(f"__fields: {params.get('__fields')}")

print(f"__limit: {params.get('__limit')}")

print(f"__offset: {params.get('__offset')}")

print(f"All keys: {list(params.keys())}")

....

print(f"queries: {sql}")

print(rows)

No idea how to fix this. It can be a '__client_request' tho. I think its is a bug.
It would be great to have this feature, but I already spent 3 days on this...