Written on Monday, November 26, 2007 by Edwin Sanchez
Microsoft Data Transformation Services (DTS) is a good tool bundled in SQL Server. One of the things you can do with it is to upload data from different file formats like text file and excel file into SQL Server. In this post, I will try to log my experience in comparing it to the program I created in db4o to transfer data from text/excel file to db4o.
I tried to upload 2 files in SQL Server: one is a text file and the other is an excel file. I uploaded it as is based on the columns in the files. The 2 files eventually became tables in SQL Server. I performed a query left joining the 2 tables to find out if an item does not exist in the other. The result was 1,033 records.
My db4o program's objective is to have the same results as in the SQL version. But to my surprise, the results are not the same. First, it gave me more than double the number of records I got from SQL Server. I found out that there are some information in the file that has spaces after them. For example: I'm expecting "FOO" but the data is "FOO " (with one space character after). So I tried to trim them. After that, the result is still not the same in terms of numbers. There are 3 records more in the result from the db4o program. After some scrutinization, I have found out that I forgot an important element: the data is not consistent if it's lower or uppercase strings. (See related post here) I tried converting them all to uppercase and finally, the results are the same.
Here are the lessons learned:
1. DTS removes trailing spaces in your data. Your db4o program should do the same to obtain the same result.
So that means, it "looks" like you uploaded it in SQL Server "as is" but actually, all data are trimmed to remove the spaces. I proved that by inspecting the data in SQL Server with the len() function. It's not the same as the source data in text and excel files.
2. Remember that db4o/C# is case-sensitive.
Maybe this has not fully internalized in me and I forgot it again.
Hope this helps if you have this kind of stuff in your work.
------------------------------------------------------------------------------------------------------
Related Posts:
Posted in
db4o,
DTS
|
Written on Monday, November 26, 2007 by Edwin Sanchez
Since I started my project using db4o, I get to encounter things that I was not accustomed to do. I used to do Visual Basic 6 on MS SQL Server, then VB.Net 2003 on the same database product and lastly C#(still on MS SQL Server). I would like to list the things out here that might help new entrants in the object-database world and C# with similar experience as mine:
1. db4o/C# is case-sensitive
I'm used to SQL Server's case-insensitiveness (although you can make it case-sensitive too by changing the settings. See SQL Server 2000 Books Online under the topic How to create a case-sensitive instance of SQL Server 2000 (Setup)). When comparing strings, take note of this or your going to have lunch and dinner dates with the debugger. So those migrating from VB/SQL tools, expect this since Java and C# are case-sensitive.
2. Get out of the relational mind-set
There are many posts on this so I won't go any further explaining.
3. Good performance depends on YOU
Some people may not agree with me but db4o may not perform properly if your code is slow. db4o is very fast as proved by the pole position survey. If you quickly jump to coding, process tons of objects and not reading the documentation on the best practices to ensure performance, then your code will crawl like a snail. I sometimes make my own mistakes by forgetting that performing connections and executing queries inside a while loop is a bad news. This is not in the docs but the db4o core team need not to put it there since this is a bad practice even in the relational world.
4. Missing Features?
Check the documentation or the community forum on specific things that you need. If it's not there, do a work around for a while and much better, contribute to the community if you have great ideas. Things will get better soon, and the core team is very agile. And this is open source. Features will get better by community contributions.
That's it for now. I'll try adding some more as I move on to my quest.
Good day to all
Posted in
Databases,
db4o,
Programming
|
Written on Monday, November 26, 2007 by Edwin Sanchez
I just started my own blog to establish presence. I will post on subjects regarding programming, development, db4o, the industry I'm in, my interests and other cool stuff that I find across the internet. I will post my schedule from time to time when needed.
Posted in
General
|