Hi All,

Sorry for these rubbish questions ... but I can't seem to figure them out myself!

I am converting DBISAM apps to EDB.

I have DBISAMQueries returning lists into DBGrids. Users then add a small amount of data to each row (i.e. a new value for some line-item, or click a checkbox).

The app then iterates the grid & uses a separate Query to update tables with an UPDATE statements & params drawn from data in the grid.

--

I cannot make my DBGrid editable using EDB. With DBISAM a read-only result set could be set read / write, but setting the

RequestSensitive and ReadOnly properties in EDB my grid remains read-only. I realise that the result is CANNED, I just want the user to be able to edit the grid.

Adam

Adam

>RequestSensitive and ReadOnly properties in EDB my grid remains read-only. I realise that the result is CANNED, I just want the user to be able to edit the grid.

You can't. All queries in ElevateDB are read only. What you have to do is create either a temporary or memory table. I use memory tables already and I'm in the process of my first use of a temporary table. Tim gave me a script for the in-memory tables and I can easily let you have that, or wait a few days and I'll be posting TnlhTemporary - a table component designed to create the temporary file from sql almost as though you were using a query. I've already got it working I just need to refine it so that the full range of ParamByName().Asxxx are catered for.

If you can't wait then here's a sample script


SCRIPT(IN tblName VARCHAR, IN ProjectID INTEGER)
BEGIN

DECLARE Creator VARCHAR;
DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt;
DECLARE ResultCursor SENSITIVE CURSOR FOR ResultStmt;

PREPARE InfoStmt FROM 'SELECT * FROM Information.TemporaryTables WHERE Name=?';
OPEN InfoCursor USING tblName;
IF (ROWCOUNT(InfoCursor) > 0) THEN
 EXECUTE IMMEDIATE 'DROP TABLE "'+tblName+'"';
END IF;
CLOSE InfoCursor;

SET Creator = 'CREATE TEMPORARY TABLE ' + tblName + ' AS ' +
'SELECT
C._ID,
Site._SiteID,
C._Name,
Site._Switchboard,
RCF(_PostCode,_Town,'', '') AS _Where,
CAST(RCF(''T/O: '' +  T._Turnover,''Staff: '' + S._Staff,'', '') AS VARCHAR(75)) AS _Size,
CAST(RCF(IFNULL(M._MktDesc,IFNULL(SC._Description,SG._Description,SC._Description),M._MktDesc),O._Description,'', '') AS VARCHAR(102)) AS _CompanyType ,
(SELECT COUNT(_fkContacts) FROM Calls WHERE _fkProjects = ' + CAST(ProjectID AS VARCHAR) + ' AND Calls._fkSites = Sites._SiteID) AS _EiP '+
'FROM
Sites Site
JOIN Companies C ON Site._fkCompanies = C._ID
LEFT OUTER JOIN SandT S ON C._fkSandT_Staffing = S._Band
LEFT OUTER JOIN SandT T ON C._fkSandT_Turnover = T._Band
LEFT OUTER JOIN Markets M ON C._fkMarkets = M._ID
LEFT OUTER JOIN OrgType O ON C._fkOrgType = O._ID
LEFT OUTER JOIN SICGroup SG ON C._fkSICGroup = SG._ID
LEFT OUTER JOIN SICCode SC ON C._fkSICCode = SC._ID
WHERE
_SiteID IN (SELECT _fkSites FROM Calls WHERE _fkProjects = ' + CAST(ProjectID AS VARCHAR) +') WITH DATA';
 
EXECUTE IMMEDIATE Creator;

EXECUTE IMMEDIATE 'CREATE INDEX  "Name" ON "'+tblName+'" (_Name)';
EXECUTE IMMEDIATE 'CREATE INDEX  "EiP" ON "'+tblName+'" (_EiP)';
EXECUTE IMMEDIATE 'CREATE INDEX  "Where" ON "'+tblName+'" (_Where)';

END


Roy Lambert [Team Elevate]

>>You can't. All queries in ElevateDB are read only.

HUM seems a bit hard-core that you can't just create an editable canned query

Still the solution is not too tough ... if the SELECT SQLStr is passed in as a second param beside the tblName you can actually have that 1 procedure sitting on the DB & just call it whenever you want a writeable result set.

It should only take me a few minutes to set that up.

Adam


I've just uploaded my TnlhTemporary component to the extensions ng. Its in alpha but it works on the two temporary tables I wanted to be able to create. You can treat it pretty much like a DBISAM canned query. It inherits from my TnlhTable component so comes with its behaviour as well.

Basically it just simplifies the process of creating and hooking up a temporary table - one thing I haven't tried yet is persistent fields but I'll probably do that later on today - I can't see any reason why they shouldn't work but I never trust those sneaky computers.

Roy Lambert

Adam


And it works fine with persistent and calculated fields. The only problem (common to scripts) is lack of any ability to drive a progressbar.

Roy Lambert

>>I've just uploaded my TnlhTemporary component to the extensions ng.

COOL Roy! Thanks. I will have a look at it in the coming week.

--

Tim / Roy.

I have a unit I have written which allows my apps to address either DBISAM databases or EDB databases just by switching a single property.

It has allowed me to migrate apps between the 2 databases very easily.

It is my own pretty rough, hacky code, but I would be happy to post it up if you think people might find it useful.

Adam


Post it! Things like that are ALWAYS useful in some shape or form.

Roy Lambert

Adam,

<< I have a unit I have written which allows my apps to address either
DBISAM databases or EDB databases just by switching a single property.

It has allowed me to migrate apps between the 2 databases very easily.

It is my own pretty rough, hacky code, but I would be happy to post it up if
you think people might find it useful. >>

Yes, please.

--
Tim Young
Elevate Software
www.elevatesoft.com