My brain is not working today (coughing, dribbly nose etc) and I'm sure the answer will be obvious once I'm told. Two databases & one query - how do I get the query to work?

object DB: TEDBDatabase
   DatabaseName = 'DB'
   Database = 'NLH-Live'
   SessionName = 'TfRSession'
   Left = 24
   Top = 112
 end

 object Memory: TEDBDatabase
   DatabaseName = 'Memory'
   Database = 'Memory'
   SessionName = 'TfRSession'
   Left = 24
   Top = 160
 end

 object RecentlyOpened: TEDBQuery
   DatabaseName = 'Memory'
   SessionName = 'TfRSession'
   SQL.Strings = (
     'SELECT'
     '_fkProjects,'
     '_LastAccess,'
    
       '(IF(_LastAccess IS NOT NULL THEN CAST(CAST(CURRENT_TIMESTAMP - _' +
       'LastAccess AS INTERVAL DAY) AS INTEGER) ELSE 99999))  AS _LongAg' +
       'o,'
     'P._Name,'
     'P._Type,'
     'P._fkOrderBook,'
     'IF(P._Status = '#39'L'#39' THEN '#39'Live'#39
     'ELSE IF(P._Status = '#39'D'#39' THEN '#39'Dead'#39
     'ELSE IF(P._Status = '#39'H'#39' THEN '#39'Hold'#39' ELSE '#39#39')))AS _Status, '
     '_fkUsers'
     'FROM'
     'mruProjects'
     'JOIN "DB"."Projects" P ON _fkProjects = P._ID'
     'WHERE'
     '_fkProjects <> :Schedule'
     'ORDER BY'
     '_LongAgo')
   Params = <
     item
       DataType = ftUnknown
       Name = 'Schedule'
       ParamType = ptInput
     end>
   Left = 320
 end


Roy Lambert

Roy,

<< My brain is not working today (coughing, dribbly nose etc) and I'm sure
the answer will be obvious once I'm told. Two databases & one query - how do
I get the query to work? >>

Save me some time - what part isn't working ?   The compilation or the
execution ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


I'm trying to get it to work in EDBManager. I have the database Memory, I've added the table mruProjects, typed in some data and I get something like the following

ElevateDB Error #700 An error was found in the statement at line 349 and column 41 (ElevateDB Error #401 The database DB does not exist)

ElevateDB Error #401 The schema NLH-Live does not exist

First one is obviously my mistake.

Before I got that far, for some reason, and I haven't been able to duplicate it yet I had an AV.

I'm running the sql from the Memory database. Running it from NLH-Live I get

ElevateDB Error #700 An error was found in the statement at line 349 and column 41 (ElevateDB Error #401 The database DB does not exist)

Since there are only 18 lines I think you're counting is a bit off


Roy Lambert

Tim


To (hopefully) make it easier can I have a simple example of how to join several fields from a table in in database DISK to a table in database MEMORY.


Roy Lambert

Roy,

<< I'm trying to get it to work in EDBManager. I have the database Memory,
I've added the table mruProjects, typed in some data and I get something
like the following

ElevateDB Error #700 An error was found in the statement at line 349 and
column 41 (ElevateDB Error #401 The database DB does not exist) >>

It would help if you posted the actual SQL statement that you're using.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>It would help if you posted the actual SQL statement that you're using.

I did in the TEDBQuery stuff in the first post, but here it is again - in clear this time


SELECT
_fkProjects,
_LastAccess,
(IF(_LastAccess IS NOT NULL THEN CAST((CURRENT_TIMESTAMP - _LastAccess)DAY AS INTEGER) ELSE 99999))  AS _LongAgo
_LongAgo,
P._Name,
P._Type,
P._fkOrderBook,
IF(P._Status = 'L' THEN 'Live'
ELSE IF(P._Status = 'D' THEN 'Dead'
ELSE IF(P._Status = 'H' THEN 'Hold' ELSE '')))AS _Status,
_fkUsers
FROM
mruProjects
JOIN "DB"."Projects" P ON _fkProjects = P._ID
WHERE
_fkProjects <> :Schedule
ORDER BY
_LongAgo


Roy Lambert

Tim


To ease my continued testing I created a disk based database and created the mruProjects table in there

Running this (corrected slightly from the last posting and with the real database name in) in EDBManager

SELECT
_fkProjects,
_LastAccess,
(IF(_LastAccess IS NOT NULL THEN CAST((CURRENT_TIMESTAMP - _LastAccess)DAY AS INTEGER) ELSE 99999))  AS _LongAgo,
P._Name,
P._Type,
P._fkOrderBook,
IF(P._Status = 'L' THEN 'Live'
ELSE IF(P._Status = 'D' THEN 'Dead'
ELSE IF(P._Status = 'H' THEN 'Hold' ELSE '')))AS _Status,
_fkUsers
FROM
mruProjects
JOIN "NLH-Live"."Projects" P ON _fkProjects = P._ID
WHERE
_fkProjects <> :Schedule
ORDER BY
_LongAgo

I get

ElevateDB Error #700 An error was found in the statement at line 348 and column 47 (Access violation at address 004FD862 in module 'edbmgr.exe'. Read of address 0000005C)

I'm not even prompted for the paramter value


Roy Lambert

Tim


More experimentation and I have a working query BUT I really do hope there's a better way. What I've come up with is:

SELECT
_fkProjects,
_LastAccess,
(IF(_LastAccess IS NOT NULL THEN CAST((CURRENT_TIMESTAMP - _LastAccess)DAY AS INTEGER) ELSE 99999))  AS _LongAgo,
(SELECT _Name FROM "NLH-Live".Projects P WHERE P._ID = _fkProjects) AS _Name,
(SELECT _Type FROM "NLH-Live".Projects P WHERE P._ID = _fkProjects) AS _Type,
(SELECT _fkOrderBook FROM "NLH-Live".Projects P WHERE P._ID = _fkProjects) AS _fkOrderBook,
(SELECT
IF(_Status = 'L' THEN 'Live'
ELSE IF(_Status = 'D' THEN 'Dead'
ELSE IF(_Status = 'H' THEN 'Hold' ELSE '')))FROM "NLH-Live".Projects P  WHERE P._ID = _fkProjects) AS _Status,
(SELECT _fkUsers FROM "NLH-Live".Projects P WHERE P._ID = _fkProjects) AS _fkUsers
FROM
mruProjects
WHERE
_fkProjects <> :Schedule
ORDER BY
_LongAgo


It works but a) its a lot more awkward to code b) I'm not sure of the performance implications on reasonable sized tables unlike the tiches I'm playing with and c) I'm going to have to store this in a string list somewhere so I can alter the database name from "NLH-Live" to whatever it is at run time.

Is there a way to a) get the join working and b) when inside an app is there a way to reference the database component in the app (in this case DB) and for your components to pick the right database from that?

Right off to the next hurdle.....

Roy Lambert