Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 6 of 6 total
Thread Converting to ElevateDB
Fri, May 16 2014 7:25 AMPermanent Link

Matthew Jones

A quick post on my conversion of a working program to ElevateDB from DBISAM.

First, read the migration guide in the manual - lots of good info there.

Generally, it has been a matter of switching from TDBISAMTable to TEDBTable in my
code. The unit changes from dbisamtb to edbcomps.

Sessions, I used a lot in threads. Changed it to call a function instead which is
simply:

   Result := TEDBSession.Create(nil);
   Result.AutoSessionName := true;
   Result.LocalConfigPath := g_xStartupConfig.DatabasePath;
   Result.LoginUser := 'Administrator';
   Result.LoginPassword := 'EDBdefault';
   Result.Open;

The intialisation code (I don't have any componets on data modules) is:

   szDatabaseLocation := g_xStartupConfig.DatabasePath;
   szDatabaseTemp := szDatabaseLocation + '\Temp';
   Engine.TempTablesPath := szDatabaseTemp;
   Engine.ConfigPath := szDatabaseLocation;
   ForceDirectories(szDatabaseTemp);

   g_xThreadSession := PrepareSession(1);

The database path contains the config files and catalog files that were created in
the ElevateDB Manager, and then the tables that I migrated across. Migration was
basically easy, but one of my tables failed due to a block size not being big
enough. So I went to DBSys and altered the table to make it bigger and it was
migrated just fine.

Dynamically creating a query changed only in that the database is specified by name
and not path now. The name is that in the catalog file.
   Result := TEDBQuery.Create(nil);
   Result.SessionName := m_xThreadSession.SessionName;
   Result.DatabaseName := g_xStartupConfig.DatabaseName;
       // g_xStartupConfig.DatabasePath;


So now it compiles and runs. But it doesn't work! SQL, lovely standard language.
The nice thing about standards is there are so many of them. My main query had:

SELECT * FROM MyTable WHERE (not mtHidden)

Most items are not hidden, so they have a NULL value. NULLs are handled differently
("properly"), so that needs changing to

SELECT * FROM MyTable WHERE ((not mtHidden) OR (mtHidden IS NULL))

Now works fine. There is a session option that allows you to control this and have
it like it was, but I'd rather move forward.

Next, TOP 10 should now be RANGE 1 TO 10.

INSERT needs to change to remove the SET word. I have a TSQLBuilder object that
does this stuff for me, so one conditional and I can have it make the valid SQL for
either system.
       
DateTime values need to have the word TIMESTAMP before their text - I wrote a
function to do this and swapped out the Engine.BxDateTimeToAnsiStr calls (which
need to be Engine.BxDateTimeToSQLStr now).

Hmm, GUID now seems to have a space at the end. Two spaces. Not sure about this. I
guess it is expecting the { } brackets. I have added a Trim to the reading of the
values, but I probably have to think about this. Okay, still not right, so I
changed the table to have VARCHAR with 38. That trims the value returns. Only
slight problem is I tried 36 and it set all the values to NULL with no opt-out.
Good job just test data.

Final thing is Unicode. Most of my data is just strings, so no hassle. Turns out
one is loaded from the web, and as a parameter, so the code is:
xWorkQuery.ParamByName('3DRESULT').LoadFromStream(xData, ftMemo);
This is being interpreted as Unicode, but isn't. The manager application shows it
as chinese text, which was the clue I needed. Okay, nip via a TStringStream and
convert to String, all good.

I started this conversion yesterday having only briefly looked at the manuals. Now
all done, and I've spent perhaps 6 hours on it total. I will be using ElevateDB as
my go-to database from now on I think, but perhaps still be maintaining my old code
that is deployed. It just didn't seem right to think that one day I might convert,
when the cost of converting today is limited to two computers.

Good stuff.

/Matthew Jones/
Fri, May 16 2014 8:32 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Matthew

>SELECT * FROM MyTable WHERE (not mtHidden)
>
>Most items are not hidden, so they have a NULL value. NULLs are handled differently
>("properly"), so that needs changing to
>
>SELECT * FROM MyTable WHERE ((not mtHidden) OR (mtHidden IS NULL))

Better yet set a default of TRUE in the table definition. You'll need to run a one time bit of sql to set all the null ones to false but that's then done.

>Hmm, GUID now seems to have a space at the end. Two spaces. Not sure about this. I
>guess it is expecting the { } brackets. I have added a Trim to the reading of the
>values, but I probably have to think about this. Okay, still not right, so I
>changed the table to have VARCHAR with 38. That trims the value returns. Only
>slight problem is I tried 36 and it set all the values to NULL with no opt-out.
>Good job just test data.

There is a GUID field type now. Not sure how its stored and wether it gives you any benefits or not.


>when the cost of converting today is limited to two computers.

Please explain. I don't understand.

Roy Lambert
Fri, May 16 2014 9:07 AMPermanent Link

Matthew Jones

> Better yet set a default of TRUE in the table definition. You'll
> need to run a one time bit of sql to set all the null ones to false
> but that's then done.

Defaults? Defaults? I don't want to use any of those new fangled benefits of
ElevateDB!      Good idea. 8-)

> There is a GUID field type now. Not sure how its stored and wether
> it gives you any benefits or not.

Indeed it was a GUID field that got transferred, and while it says it is a 38
character VARCHAR, it pads with spaces. Using a VARCHAR instead fixes it for my
purposes, and that will do for now. Basically, I think it wants a GUID string to
have the {} brackets.

> >when the cost of converting today is limited to two computers.

Basically, the application (my new WebBuilder web shop) is currently not deployed
anywhere that matters. Thus if I rip the old database out and put in a new one, it
doesn't matter. Once the shop is live and selling things, changing the database
would be expensive. Given I would like to replicate from the shop for backup, it
made sense to do the move to ElevateDB asap. The urge was there, and it took a day.
Sweet.

/Matthew Jones/
Fri, May 16 2014 9:26 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Matthew


I find I'm using more SQL(I class the DEFAULTs in with that)  than I ever did in DBISAM, and it is useful for some things. Parts of it I still hate with a vengeance but with the subclassed table component that sorted my fixations on NULL and emptystring are the same and VARCHARs should be RTRIMed.

When you finally get your head round it its a bit of a shock to see just how much is in there.

I don't think I'll ever be one of the SQL with and for everything brigade but with the programming language Tim's built in I am finding it useful and am using scripts and triggers.


Roy Lambert
Fri, May 16 2014 12:13 PMPermanent Link

Matthew Jones

Indeed, I've seen ElevateDB can do a lot more, and I look forward to pushing it
hard.

/Matthew Jones/
Fri, Nov 14 2014 5:32 AMPermanent Link

macc2010

(Matthew Jones) wrote:

Indeed, I've seen ElevateDB can do a lot more, and I look forward to pushing it
hard.

/Matthew Jones/
Image