Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 11 to 17 of 17 total |
Getting Started - Used DBISAM For A Long Time |
Fri, Jan 6 2012 1:22 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Episode 2
I forgot one thing if you supply the complete package - you'll need to supply the config file as well. You may also still need to set the database path if its not the same as you set up in ElevateDB. If you can't or don't want to supply the files and you need to create them in your exe the process is roughly as follows: To get started you'll need the TEDBEngine and TEDBSession components. As before set Set UseLocalSessionEngineSettings to True in the engine. Set the session details programmatically and open it. This will create the config file. You can then use the session to create the database.. session.EXECUTE('CREATE DATABASE fred PATH ''x:\y\z'''); then you can set the database properties and use its EXECUTE method to create the tables. The tables can be created using a standard query using the session name set and pointing to the Configuration database. You can use ElevateDB to see what the syntax is for creating a table but remember ElevateDB queries only executes one piece of sql unlike DBISAM where you could have several bits of sql separated by a semicolon. This means either use a script or have several queries for creating indices apart from the primary key. Does the table exist now falls into two parts - does the table exist and does the data exist. If you're bothered check the latter first using FileExists because if the table exists and you try and open it the data files will be created. To see if a table exists you now have to query the database catalog. Set up a query - point it at the database and run a query: SELECT * FROM Information.Tables WHERE Name = 'whatever' check to see if any rows are returned. If so the table exists, if not it doesn't. I have a couple of little utility routines function DoesTableExist(chk: TnlhTable): boolean; var Checker: TEDBQuery; begin Checker := TEDBQuery.Create(nil); try Checker.SessionName := chk.SessionName; Checker.DatabaseName := chk.DatabaseName; Checker.SQL.Text := 'SELECT * FROM Information.Tables WHERE Name = ' + QuotedStr(chk.TableName); Checker.ExecSQL; Result := Checker.RecordCount > 0; finally Checker.Close; Checker.Free; end; end; and function IsTableInDatabase(const tName: string; tDatabase: TEDBDatabase): boolean; var Checker: TEDBQuery; begin Checker := TEDBQuery.Create(nil); try Checker.SessionName := tDatabase.SessionName; Checker.DatabaseName := tDatabase.DatabaseName; Checker.SQL.Text := 'SELECT * FROM Information.Tables WHERE Name = ' + QuotedStr(tName); Checker.ExecSQL; Result := Checker.RecordCount > 0; finally Checker.Close; Checker.Free; end; end; and function IsTableThere(const sName, dName, tName: string): boolean; var Checker: TEDBQuery; begin Checker := TEDBQuery.Create(nil); try Checker.SessionName := sName; Checker.DatabaseName := dName; Checker.SQL.Text := 'SELECT * FROM Information.Tables WHERE Name = ' + QuotedStr(StripCtrlChars(tName)); Checker.ExecSQL; Result := Checker.RecordCount > 0; finally Checker.Close; Checker.Free; end; end; If you're doing it in a script - here's the script Tim supplied to create an in-memory table slightly enhanced. SCRIPT (IN SQLStatement VARCHAR, IN TableName VARCHAR, IN IdxSet VARCHAR) BEGIN DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt; DECLARE ResultCursor SENSITIVE CURSOR FOR ResultStmt; PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?'; OPEN InfoCursor USING TableName; IF (ROWCOUNT(InfoCursor) > 0) THEN EXECUTE IMMEDIATE 'DROP TABLE "'+TableName+'"'; END IF; CLOSE InfoCursor; PREPARE ResultStmt FROM 'CREATE TABLE "'+TableName+'" AS '+SQLStatement+' WITH DATA'; EXECUTE ResultStmt; IF IdxSet IS NOT NULL THEN PREPARE ResultStmt FROM 'CREATE INDEX Idx ON "'+TableName+'"('+IdxSet+')'; EXECUTE ResultStmt; END IF; END Roy Lambert [Team Elevate] |
Fri, Jan 6 2012 1:45 PM | Permanent Link |
Rick Hillier | Hi Roy,
Thank you very much for the help... I think I can get it going from here. It should be no problem to include the configuration files with my app. Checking for table existence looks fairly straight forward from your examples. I did manage to get to the table creation point (not checking, just trying it in code)... and I get a message telling me that the configuration is read only..... I'll dig into your examples... I am sure that once I get it figured out, the rest should fall into place. I'm looking forward to getting going with EDB 2! Thanks again, >>> Rick <<< (as he gets his mastercard out to pay his support contracts ) |
Fri, Jan 6 2012 2:53 PM | Permanent Link |
Chris Holland SEC Solutions Ltd. Team Elevate | I forgot that one Roy, I have several thousand bits of SQL in my
application that use UPPER to compare strings. Chris Holland [Team Elevate] On 06/01/2012 18:13, Roy Lambert wrote: > Rick > > > Just remembered another one - in DBISAM you used UPPER or LOWER to indicate case insensitivity in a query - ElevateDB you can set the collation in the table creation or use COLLATE in a query. > > > Roy Lambert > |
Fri, Jan 6 2012 3:11 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Rick,
<< I did manage to get to the table creation point (not checking, just trying it in code)... and I get a message telling me that the configuration is read only..... >> Any time that you see that error message, it means that your query component is pointing to the system-defined Configuration database instead of the actual database. In general, you should consider using the TEDBSession.Execute method for executing DDL statements that create/alter/drop Configuration objects such as databases, users, jobs, etc., and the TEDBDatabase.Execute method for executing DDL statements that create/alter/drop database objects. It's easier than having to set up a TEDBQuery every time: http://www.elevatesoft.com/manual?action=viewtopic&id=edb2&product=delphi&version=7&topic=Creating_Configuration_Objects http://www.elevatesoft.com/manual?action=viewtopic&id=edb2&product=delphi&version=7&topic=Creating_Database_Objects Also, regarding shipping the configuration file: just remember that the configuration file can contain paths for things like databases, external modules, and stores, so you'll need to take into account modifying these accordingly using the appropriate ALTER statement during installation/startup. In general, my recommendation would be to *not* ship the configuration file, especially if you're only going to be creating a single database and leaving the user security alone for the most part. In such a case, the code in the CDCollector demo application will suffice for checking to see if the database exists, and if not, creating it. Now, for the database catalog: it is perfectly fine to ship the database catalog with the application since it never contains path information and is completely self-contained. I hope that helps, -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Jan 6 2012 3:18 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Rick,
<< Please ignore most of what I said in my previous post above... I think I have this figured out... but the login thing concerns me still... if I install my executable on a new computer, will the login always be the defaults if there is no database admin program available? >> The user names/passwords that are available for login are dependent upon the configuration file, which is where they are stored. If the configuration file was just created by EDB because it didn't exist, then it will contain the default users/roles noted here: http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=User_Security under "Default Users and Roles". In addition to my other response, if you don't plan on using user security and only use a single database, then you might want to consider using a virtual configuration file instead. A virtual configuration file is stored in memory instead of on-disk, and because of this, it needs to be populated at application startup with the necessary databases, etc. But, it does allow you to ship an application that doesn't require a configuration path setting or an on-disk configuration file, and will allow you to manage the user security as you see fit in your application. You can find out more information on that feature here: http://www.elevatesoft.com/manual?action=viewprop&id=edb2&product=delphi&version=7&comp=TEDBEngine&prop=ConfigMemory -- Tim Young Elevate Software www.elevatesoft.com |
Sat, Jan 7 2012 7:02 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
>Now, for the database catalog: it is perfectly fine to ship the database >catalog with the application since it never contains path information and is >completely self-contained. For some reason my first reading of that was "completely self satisfied" Roy Lambert |
Sat, Jan 7 2012 7:02 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Chris
>I forgot that one Roy, I have several thousand bits of SQL in my >application that use UPPER to compare strings. I try and remember to make sure any CHAR or VARCHAR columns are set to ANSI_CI. Works a charm. Roy Lambert |
« Previous Page | Page 2 of 2 | |
Jump to Page: 1 2 |
This web page was last updated on Thursday, September 26, 2024 at 10:04 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |