[RESOLVED] MS SQL Database Question

[Foxy69]

I have a PervasiveSQL database running on a Linux platform and the front end app is written in VB.Net 2003. I now want to create an addon app on a Pocket PC for stock taking.

I intend to dump the stock details out from PervasiveSQL into a CSV file. This will be copied onto the Pocket PC device and the app on the device will import the CSV file into a SQL CE database.

All that is fine but my biggest concern is that the procedure takes up double the amount of disk space i.e. I will end up with the CSV and SDF files on the device.

Is there anyway of building the SDF on the PC or can anybody suggest a better method.

Thanks

[Shaggy Hiker]

Well, it seems that there are a couple options. The most obvious is that the app can delete the CSV off the device once the data has been imported.

Also, why not use XML? Importing that seems like it might be smoother than CSV files, but of course, it won't save you a thing in size.

[Foxy69]

Deleting the CSV still means at some point I have two files that could be of quite large size and I was worried about space on the device. I would have liked to keep it as trim as possible.

Saying all that, I will need to create a CSV file after the stock take to later import the counts into the Pervasive database.

XML I thought would actually take more space beacuse of all the tags?

If there's no solution I'll stick with what I know, I just wondered if there was a way of creating the SDF on a PC and copying it onto the device. This would save on time and also space.

[Shaggy Hiker]

There is a solution for SQLServer to SQLServerCE, but I am under the (possibly mistaken) belief that it only works between those two. SQLServerCE has a way to replicate SQLServer, which is a direct connection. I haven't tried it with anything else because I read that it was only between those two. You might look at that, but I'm not optimistic about it.

I would expect XML to take up extra space, as well, however, it might be easier to create and extract...which leads on to this:

If there is a problem having the whole thing on there at once, which will only be big if both are big. The next solution would be to move it one line at a time. This would be PAINFUL if you have to do the copying manually, but it wouldn't be that big an issue if you had a program on the desktop that chunked up the output file, and the app on the PDA took individual records, or small groups of records.

This isn't all that tough. I have a couple of classes I wrote to do just that in a background thread. The classes seem to work particularly well for small amounts of data, so individual records would do well. However, I have only lightly tested the classes. You can have them if you'd like, but they are "as is", and will have to be tested more thoroughly.

[Foxy69]

I think its best to just dump the CSV file to the device and import it on the device. If I do end up struggling for space I could look into batching the export/import to x'000 records at a time.

Thanks