These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

EVE Technology Lab

Forum Index

EVE Forums » EVE Technology and Research Center » EVE Technology Lab » EVESqlToMdb - Export SDE to an ADO DBMS

Topic is locked indefinitely.

EVESqlToMdb - Export SDE to an ADO DBMS
Author

Previous Topic Next Topic

Hel O'Ween

Men On A Mission

Likes received: 161

#1 - 2015-11-07 12:07:47 UTC | Edited by: Hel O'Ween

I've got a new tool which may or may not help some of you guys extracting data out of the SDE (after you've created a "complete" MS SQL SDE, I recommnd Desmont's excellent EVEMonSDEExternalsToSql for doing that) for use in your own applications.

Let's start off with the misleading name. I'm using MS Access as the DBMS for EWA, that's where the name stems from. But in theory any ADO database should work, though I haven't tested it.

Here's an excerpt from the ReadMe, explaining its purpose:

Quote:

EWA uses a MS Access database as its local storage. CCP provides its SDE (mainly) as an SQL dump. The obvious task at hand: copy the necessary data from SQL database to the Access database. With a few standard DB tools, this is a task taking ~ 15-20 minutes. Which was all good while CCP had its 2x a year release cycle. Now with the 6 week release cycle, those few minutes do add up. Long story short - time to "toolize"(tm) that task.

Which brings us to EVESqlToMdb. Sure, I could quickly throw together some script, hard-coding all the tables and columns.
But experience shows that those quick'n'dirty hacks come back to bite you sooner or later and that fixing this stuff takes longer in the long run than doing it right in the first place.

EVESqlToMdb therefore was designed in a way so that I can cope with database changes by just changing its XML configuration/mapping file and be done with it, once the application itself works OK.

Project location: EVESqlToMdb on Github
Direct link to the binaries

EVEWalletAware - an offline wallet manager.

655
Desmont McCallock Likes received: 655	#2 - 2015-11-09 12:35:01 UTC While I'm at it, would you be interested me adding ability to the new tool version, exportation to MS Access Database?


Hel O'Ween Men On A Mission Likes received: 161	#3 - 2015-11-09 17:27:39 UTC Sure, Desmont, if you feel so, I'd be more than happy to use it. I guess all the pure spreadsheet guys using Excel might also benefit from a MS Access SDE conversion. (That's why I made this one public in the first place) EVEWalletAware - an offline wallet manager.


Desmont McCallock Likes received: 655	#4 - 2015-11-09 17:57:02 UTC OK, then. The tool provides also export in CSV.

Hel O'Ween

Men On A Mission

Likes received: 161

#5 - 2015-11-09 18:04:56 UTC

Which flavor of CSV?

The real one, were Values are Separated by Comma?

Or the ugly bastard child we've got in Germany, which uses semicolon as a separater, because the comma is our fractional separator and thus would screw up "normal" CSV?

What I'm trying to say: CSV is not CSV everywhere ... and that's why stay away from this file format as far as I can.

Friends don't let friends use CSV. Cool

EVEWalletAware - an offline wallet manager.


Desmont McCallock Likes received: 655	#6 - 2015-11-09 18:18:38 UTC Semi-colon, exactly because of comma usage as thousands or decimal digit separator.


Desmont McCallock Likes received: 655	#7 - 2015-11-11 10:51:04 UTC I have add a link in my thread to the produced MS Access db. Please be so kind and check it out for any errors. I know that EWA doesn't need all those tables, but this is the next thing on my TODO list, adding ability to choose the exported tables.

Hel O'Ween

Men On A Mission

Likes received: 161

#8 - 2015-11-11 15:37:21 UTC

Quote:

I have add a link in my thread to the produced MS Access db. Please be so kind and check it out for any errors.

Erhm ... that's in .accdb ... I'm still using .mdb.

OK, so quick conversion to MDB, importing my EWA tables into it , opening a few data windows ... looks good! No errors thrown by EWA, no (obvious) missing EVE data. Big smile

Quote:

I know that EWA doesn't need all those tables, but this is the next thing on my TODO list, adding ability to choose the exported tables.

It's not just whole tables I omit. I'm also dropping lots of unused columns from tables EWA uses, that's why I'm using the XML config approach for this tool: being able to specify only those tables/columns which are actually needed.

EVEWalletAware - an offline wallet manager.

Desmont McCallock

Likes received: 655

#9 - 2015-11-11 17:23:19 UTC

If I change the extension to 'mdb' I still can open it up with Access 2013.
Is there something special you do to convert it to 'mdb'? (I don't have much experience with Access as I don't use it myself)
I just used 'accdb' as the file extension because I'm using 2007-2013 version of Access.

I just finished implementing the ability to specify certain tables (which in your case will be a significant bonus) but for selecting specific columns there is not much I will do, sorry.

Well, I don't expect from you to use the tool per say for EWA, but having an extra option was not a bad idea.

Hel O'Ween

Men On A Mission

Likes received: 161

#10 - 2015-11-11 17:45:45 UTC

Desmont McCallock wrote:

Is there something special you do to convert it to 'mdb'? (I don't have much experience with Access as I don't use it myself)
I just used 'accdb' as the file extension because I'm using 2007-2013 version of Access.

No big deal, just do "Save as ..." and pick "Access 2000-2003 (*.mdb)".

While Access >= 2007 can happily work with both, Access <= 2003 can only do .mdb. But that's still the majority of Access files out there. And meanwhile there are plenty of free tools (like MDB Viewer Plus) which let you work on those. No need to purchase a copy of Access.

I'll stick with MDB, as the improvments in .accdb don't apply to EWA.

Are you using ADO(.NET) for your tool? If so, the difference in creating MDB vs. ACCDB is "just" the Provider (and file name/extension, ofc):


' ACCDB
Provider=Microsoft.ACE.OLEDB.12.0

vs.

' MDB
Microsoft.Jet.OLEDB.4.0

EVEWalletAware - an offline wallet manager.

Desmont McCallock

Likes received: 655

#11 - 2015-11-11 18:05:13 UTC | Edited by: Desmont McCallock

Hel O'Ween wrote:

No big deal, just do "Save as ..." and pick "Access 2000-2003 (*.mdb)".

Exactly what I used to see any diffs.

Hel O'Ween wrote:

While Access >= 2007 can happily work with both, Access <= 2003 can only do .mdb. But that's still the majority of Access files out there. And meanwhile there are plenty of free tools (like MDB Viewer Plus) which let you work on those. No need to purchase a copy of Access.

I'll stick with MDB, as the improvments in .accdb don't apply to EWA.

You may wonna consider moving to sqlite.

Hel O'Ween wrote:

Are you using ADO(.NET) for your tool? If so, the difference in creating MDB vs. ACCDB is "just" the Provider (and file name/extension, ofc):


' ACCDB
Provider=Microsoft.ACE.OLEDB.12.0

vs.

' MDB
Microsoft.Jet.OLEDB.4.0

Aha! Yeap I'm using the ACCDB provider. Didn't knew about the Jet DBEngine. I fiddle around to see if I can implement that in via a switch.

Tool's code is available here if you want to take a look.

Hel O'Ween

Men On A Mission

Likes received: 161

#12 - 2015-11-11 22:26:28 UTC

Desmont McCallock wrote:

You may wonna consider moving to sqlite.

I'm not aware of a reliable ADO provider for VBA/VB6 ...

Quote:

Aha! Yeap I'm using the ACCDB provider. Didn't knew about the Jet DBEngine. I fiddle around to see if I can implement that in via a switch.

http://www.connectionstrings.com/ is a quite nice resource for looking up those (obscure) providers.

EVEWalletAware - an offline wallet manager.

Desmont McCallock

Likes received: 655

#13 - 2015-11-11 23:31:56 UTC | Edited by: Desmont McCallock

Hel O'Ween wrote:

I'm not aware of a reliable ADO provider for VBA/VB6 ...

Say what? No offence there but the only person I know what still use VBA/VB6 is like 60 years old.

Hel O'Ween wrote:

http://www.connectionstrings.com/ is a quite nice resource for looking up those (obscure) providers.

Nice!

It turns out that I can't use the Jet DBEngine on a 64bit application to create an 'mdb' db file. And I can't go on 32bit because I loose the ability of creating a 'accdb' db file (as I assume that ppl install the 64bit version of Access on their 64bit OS).
With that said I will try tomorrow to experiment further (will try to see if a 32bit version of Access 2007-2013 can do the trick).

If this doesn't succeed, I'm afraid that you are stuck with converting the 'accdb' to 'mdb'.

Hel O'Ween

Men On A Mission

Likes received: 161

#14 - 2015-11-12 11:30:15 UTC | Edited by: Hel O'Ween

Desmont McCallock wrote:

Hel O'Ween wrote:

I'm not aware of a reliable ADO provider for VBA/VB6 ...

Say what? No offence there but the only person I know what still use VBA/VB6 is like 60 years old.

I'm closer to that age as you might imagine ... Cool

Also: EWA is now 7 years old. And while a rewrite in .NET would ofc be fine, I'm simply lacking the time (and to a certain extended the motivation) to do so.

Quote:

Have a look at this: http://stackoverflow.com/questions/1991643/microsoft-jet-oledb-4-0-provider-is-not-registered-on-the-local-machine

EVEWalletAware - an offline wallet manager.

Desmont McCallock

Likes received: 655

#15 - 2015-11-12 11:55:49 UTC | Edited by: Desmont McCallock

Hel O'Ween wrote:

Have a look at this: http://stackoverflow.com/questions/1991643/microsoft-jet-oledb-4-0-provider-is-not-registered-on-the-local-machine

I have already been through all relative articles in StackOverflow and CodeProject. The problem is that I can read mdb and accdb with the ACE engine (in 64bit) but can't create both dbs.

I'm currently working on a workaround I found, using COM libraries provided by VS (Microsoft Jet and Replication Objects 2.6 Library & Microsoft ADO Ext. 2.8 for DDL and Security). This works fine in x86, even compacting db works and I'm currently figuring out how to make it work with accdb.

Desmont McCallock

Likes received: 655

#16 - 2015-11-12 14:36:53 UTC | Edited by: Desmont McCallock

Rejoice! I finally have it working.

* Tool will need to be 32bit but that's a minor drawback.
* Tool will have Jet as the default engine for Access.
* In order to use ACE, it will be available via switch.
* If Access Database Engine (x86) is not detected it will prompt you to download it from https://www.microsoft.com/en-us/download/details.aspx?id=13255

Special EWA edition: msaccess-EWA-EveStaticData-201511121649.zip


Hel O'Ween Men On A Mission Likes received: 161	#17 - 2015-11-12 17:07:35 UTC Desmont McCallock wrote: Special EWA edition: msaccess-EWA-EveStaticData-201511121649.zip EVEWalletAware - an offline wallet manager.