database problem

[Traveler]

Hi all,

This has absolutely nothing to do with gamedev, but I figured it's delphi so I give it a try as I'm kinda stuck with an issue. Here's the background. I'm currently in the process of migrating a Sybase database holding well over 100 tables and a couple million records to SQL Server. That in itself is not all that hard. The tricky part is that all primary and foreign key fields in the Sybase database are of type varchar (yeah I know, dont ask me why ???) and need to become uniqueidentifiers.

Ok, so here's the problem. I have a TTable component attached to table Users on the Sybase side and a second TTable component attached to the destination table Users on the SQL Server side.
On the Sybase side I'm able to retrieve all fields using for example something like


for i := 0 to table1.Fields.Count - 1 do (...)

However, on the SQL Server side, when I do something similar I don't get the same number of fields. And when I try to do something like:


showmessage(table2.FieldByName('usr_id').AsString) ;

an error appears Table2: Field 'usr_id' not found.

Apparently Delphi is ignoring all uniqueIdentifier fields. Does anyone have an idea how to get around this or what I need to do access these fields?

Thanks a lot!

Oh btw, this problem only occurs during runtime. Whenever I set the TDatasource and TTable during design time I am able to access them. Unfortunately I can't use this method, because I need to able to change tables during runtime...

edit: typo

[Legolas]

Just guessing, but can somehow the table name be case sensitive on SQL Server depending on collation?

[jdarling]

For SQL Server stay away from standard tables and injected SQL (permissions problems arise) and either go straight to the ADO connection object or use TADoStoredProcedure (I think is what its called). Check your collation and make sure its latin-8 or some other NON-case sensitive version.

You should also run a test and make sure that all of your properties or field names are initialized and populating properly, here is some basic code that I use in one project that's been modified to be used as a debug routine:


FProc := TADOStoredProc.Create(nil);
FProc.Connection := GetGlobalConnection;
FProc.ProcedureName := 'YourProcedureNameGoesHere';
i := 0;
FProc.Parameters.Refresh;
while(i<FProc.Parameters.Count)do
begin
Memo1.Lines.Text.Add(FProc.Parameters[i].Name);
inc(i);
end;


As for migrating from one DB to another, its much faster to script out your SyBase schema and data (separately as a CSV) and then use Bulk Insert (or similar) to do the actual import. Make sure you have proper unique identifiers and keys on EVERY table even if you don't think it needs one. This way you can rebuild your links by looking at the origin key and the new key then binding those instead of the origin (extremely fast if done properly). You can also still use the origin if you need to.

BTW: The reason your SyBase tables probably have VarChar indexes is a version thing. I don't remember exactly what version, but way back about v3 (when SQL Server and SyBase were the same product, because MS hadn't bought out the SyBase codebase yet) this was the "way to do it". I'd have to say your working on some very old code.

PS: If this isn't a personal project and is well funded I can recommend some high end consultants that do this type of stuff daily. Best in the biz as far as I'm concerned.

- Jeremy

[Traveler]

Ahh, you are the best :)

I've been over this for most of the afternoon last Friday trying to figure out why it wouldn't work. I should have gone for ADO from the very start, but figured the standard DB components would work okay as well.

Anyway, it's no personal project btw, and there are quite a bit of specific requirements. I wish I could use scripts to export all data, but tbh thats really no option as.

Thanks again!

[noeska]

Databases can be quite usefull with gamedevelopment.
Think of storing all your game data in an embedded database like sqlite.