In SQL Server, you can run queries across database tables following the
syntax you mention if you have permissions to access both databases and
they are on the same server. I presume the same is true on other (not all)
database engines. If you have the SQL, then use the executesql feature and
you are golden.
If you need to supply query parameters at run time, build a runner.
In a module or controller...
SCORM_FIND = """
SELECT TOP 1
a.history_id,
a.highest_score,
a.history_status,
b.factorA,
b.factorB
FROM
DB1.SCORM a inner join DB2.SCORMFactor b on a.id = b.id
WHERE
a.registration_id = ?
"""
from gluon.contrib.pypyodbc import ProgrammingError
from gluon import *
import traceback
def run(sql, *params, **kw):
res = SQLResponse()
if sql == None:
res.setError('SQL was None')
return res
try:
kwargs = dict(placeholders=params, as_dict=True)
if 'no_return' in kw and kw['no_return'] == True:
kwargs['as_dict'] = False
res.rows = current.db.executesql(sql, **kwargs)
except Exception, e:
res.setError(str(e), traceback.format_exc(), current.db._lastsql)
return res
res.success = True
return res
class SQLResponse(object):
def __init__(self, rows=[], success=False, error=None):
self.rows = rows
self.success = success
self.error = dict(msg=None, trace=None)
self.setError(error)
def setError(self, error, rawStack=None, sql=None):
if error == None:
return
self.success = False
self.error['msg'] = error
if rawStack != None:
self.error['trace'] = rawStack.split("\n")
if sql != None:
self.error['sql'] = sql
In your controller:
lookupRes = sql.run(sql.SCORM_FIND, registrationId)
I did not write this so although it seems pretty strait forward. We use
this quite often when we need to do things a little outside of the dal but
still get all the honey in the dal. I suspect that since the SQL is
declared, you get some marginal bumps at run time as well.
On Monday, August 28, 2017 at 12:04:29 PM UTC-4, Artem wrote:
>
> Hello !
> Hope someone can help . Thanks in advance !
> I have two database :
> db1 = DAL('sqlite://first.sqlite')
> db2 = DAL('sqlite://second.sqlite')
> with tables :
> db1.define_table('table1',
> Field('id',requires=IS_NOT_EMPTY()),
> Field('pid',type='integer'),
> Field('title',type='string'),
> )
> and
> db2.define_table('table2',
> Field('id',requires=IS_NOT_EMPTY()),
> Field('pid',type='integer'),
> Field('data',type='string'),
> )
> How to execute sqlite join ,something like:
> sql ="SELECT db1.id, db1.title,db2.data FROM db1.table1 INNER JOIN
> db2.table2 ON db2.table2.pid == db1.table1.pid"
> db1.executesql(sql) doesn't work
>
>
>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
