Solved

Database Experts!  Need your help please ...

Posted on 2002-05-20
18
191 Views
Last Modified: 2010-05-02
I have gotten assigned to creating an data export/import feature into a large database application (sort of a backup or to pass data from one unit to another).

We are using ADO and currently the database is built in Sybase (with plans on using SQL Server/Oracle).  Approx. 35+ tables (export and import speed is not an issue).

I have considered using XML because it included the database definition along with the data - but I am not an XML expert plus I am a little leary on added all the necessary references (project size?) - correct me if I am wrong.

But what I would like are some suggestions about exporting and importing data (comma-delimited, something new, XML)?  Any type of code or links for me try would be the best option.

Much appreciated!
0
Comment
Question by:PBuck
  • 5
  • 4
  • 4
  • +4
18 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 7021717
Personally I would probably stick to csv format. I don't know much about sybase but certainly SQL server can easily and rapidly import csv files. The database schema is not really important if I understand your situation as you are completely in control of both ends of the import/export and you presumably know in advance exactly what kind of structure(s) you are dealing with.
0
 
LVL 4

Author Comment

by:PBuck
ID: 7021742
ahhh .. good point about the structure.  I guess I am a little shy because I want to allow the user to choose which tables they are interested in exporting.

The selection process would not be an issue - but any suggestions about how to mark/flag the tables within csv.  Does ADO have any feature to help me with this ?

Thanks for the quick response
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7021751
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7021784
0
 
LVL 2

Expert Comment

by:vbDoc
ID: 7021947
I would use Microsoft Data Transformation Services (DTS). It comes on the SQL server disk. It can be used as a stand alone program and it's FREE and quick to set up. You can also schedule data transfers and it can eMail you with errors or problems in the transfer.

Regards,
vbDoc
0
 
LVL 4

Author Comment

by:PBuck
ID: 7021959
I am gonna take a few days here and have a look at the links above - plus check my options with csv files.

DTS does not sound like an option if the remote site is using something other than SQL Server?  Correct me if I am wrong ... but can that be used to transport Oracle data to Oracle or even Sybase for that matter?  I am unfamiliar with DTS  :-(

Thanks again all ...
0
 
LVL 2

Expert Comment

by:vbDoc
ID: 7021969
DTS will convert from X to X. Doesn't have to be SQL Server. You could convert to Database to CSV and then convert CSV to another Database. Great little tool.

0
 
LVL 17

Expert Comment

by:inthedark
ID: 7023865
I have a number of web-sites using the same software.  Each time I added new tables/fields/indexes I had to update all sites remotely before new software versions could be installed.

The problem with DTS is it didn't create indexes and it couldn't handle Identities (Autonumber's etc.), mabe it could but like everything Microsoft how?
The problem with XML is didn't have CRC so you can't be sure the data is good. CSV didn't hold enough of the advanced schema. Also if the data was to pass via public services I wanted to be encrypted.

I created a function to export the schema and data from all or selected tables. I created an output in a kinda text format with CRC on each record to ensure data is good.  I created the import so that new tables, fields and indexes would be automatically, after a backup.  The system would automatically amend field types so that the databases schemas were identical. I thought that this would take just a few hours to complete but it took weeks.  

It took ages because SQL server's documentation was so obscure, where did it hold the Identity data for a table? Where were the constraints held? Etc, etc, etc....

Now I can drop data from SQL server to a text document, to MS Access and back.  I am sorry but it took so long I could not give away the code but I would be interested in an arrangement.

I even created a client server app. that could work across the public Internet dropping encrypted records between servers.
0
 
LVL 17

Expert Comment

by:inthedark
ID: 7023920
I know this is beyond where you were thinking but with some thought you can take a huge jump.  The other spin-off from using your own proprietary format is that you can create business objects. I wanted to be able to simply copy a load of associated data onto a clipboard and paste the data.  Also if an application fails the object can then be sent by e-mail to the programmer.  If the file type is registered on his system the data gets updated into his test database. So I wrote the e-mail part as an integrated feature for rapid solution of faults.

So I created some object tools that can load all of the data (from records sets/files) with a few simple statements. I have completed the DAO part of this tool but I also want to make it work for ADO too.

Example specificaion for a transaction object:

[Object]
Name=Transaction
Title=Saracs Transaction
;Normal file extension
EXE=SIN

[Transaction]
;Sales ledger Transaction Details are stored in
Table=l S/L Item Headers
Key=SL Tran Ref
DescriptionField=SL Comment
; List the tables which are linked
Linkage1=S/L Lines
Linkage2=B/L Entries
Linkage3=N/L Entries
Linkage4=Payments
Linkage5=S/L Account
Linkage6=Event
MasterKey1=SL Tran Ref
ChildKey1=SL Tran Ref

[S/L Lines]
;Item lines are found here
Table=l Item Lines
LinkageKeys=1
MasterKey1=SL Entry
ChildKey1=SLL Entry

[B/L Entries]
;B/L (Accounts Payable) entries linked with this sale are strored here
Table=l B/L Item Headers
LinkageKeys=1
MasterKey1=SL Entry
ChildKey1=SLL Entry

[N/L Entries]
; General Ledger entries are found here
Table=l N/L Entries
LinkageKeys=1
MasterKey1=SL Entry
ChildKey1=NL Entry
Linkage1=N/L Accounts

[N/L Accounts]
; Nominal Account details here
Table=l N/L Accounts
LinkageKeys=1
MasterKey1=NL A/C
ChildKey1=NA A/C

[S/L Accounts]
;Customer Data
Table=t Company File
LinkageKeys=1
MasterKey1=SL A/C
ChildKey1=CO Ac No

[Payments]
; When this invoice was paid/part paid
Table=l S/L Payment Details
LinkageKeys=1
MasterKey1=SL Entry
ChildKey1=PD ItemEntry
Key=PD Entry
Linkage1=Payment Batch

[Payment Batch]
; Other items paid with this entry
Table=l S/L Payment Details
LinkageKeys=1
MasterKey1=PD Entry
ChildKey1=PD Entry
Linkage=Payment Batch

[Event]
; Sales analysis Events details
Table=t Venues
LinkageKeys=1
MasterKey1=SL Event
ChildKey1=VF Event Code
Linkage=Group

[Group]
; Sales Group Details
Table=t Sales Groups
LinkageKeys=1
MasterKey1=VF Group
ChildKey1=Sales Group


0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 4

Author Comment

by:PBuck
ID: 7024196
Impressive.  Very interesting concept inthedark and I have no doubt that this project took a few weeks to accomplish.  Sounds like you covered many bases and made it as robust as data archiving/backup can be :-)

This side project of mine is more of a bonus add-on that some must have tool.  We thought it it would be very pro-active to be able to backup data or pass records to another instance of our application.  So I think I will keep it simple for now - but maybe down the road I will re-think about a tool such you as suggested.

I will still look over things in the next few days ... thanks again everyone!
0
 
LVL 17

Expert Comment

by:inthedark
ID: 7025864
Start with a vision and go there one pace at a time.
0
 
LVL 17

Expert Comment

by:inthedark
ID: 7025895
p.s. My concept started with a respect for the ease of use of the IIS Session and Application objects.  The idea was that in your program you could place stuff into an object which can then exist in a multiple of forms. A database is an excellent medium for recalling information, I wanted to add the same functionality to a Sub or Function's variables so that they can be exported.  In other words you don't need recordsets any more you just build your transaction and then fire it in a direction.  It became so cute to be able to copy/drop/view all variables within a Sub or Function - so the spin-offs just keep coming.

0
 

Expert Comment

by:michaeljs
ID: 7027032
This might be a little too simplistic.  But, with ADO you can persist the data as XML or in a format that can only be read by ADO.  The nice thing is that you can open this file that is created up with ADO in another application as an ADO recordset.  Once you have it in a recordset, you can put that recordset into a table on the other side.  If you would like to see some sample code on this, I have some that I did for something similar.  I used XML instead of the ADO format, but either works.
0
 
LVL 17

Accepted Solution

by:
inthedark earned 250 total points
ID: 7029051
Yes michaeljs the ADO recordset.save is very interesting and an excellent but simple way of exporting/importing data. Sadly Microsoft missed out the important parts of the table schema so did not provide a solution to my problem. Further if you have millions of records the files can be huge as each field name is re-stated in each record.  Although this may sound trivial, in a 50 field recordset it could add up to .6GB per million records and more than double the file size.  But on the other hand the ADTG format uses just a single character as a field terminator and therefore produces efficient files. So michaeljs, for just data transfer, your proposal must be the quickest solution. Move the files into a zip file and you could get CRC and data security.

Dim GF As New zGF   ' general functions
Dim ADO As New zADO ' handy ADO functions
Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset
Dim ok
Dim SQL As String

Dim DestFolder As String

DestFolder = "D:\ExportTest\"

GF.CheckSlash DestFolder ' put \ on end if they forgot

On Error Resume Next
MkDir Left(DestFolder, Len(DestFolder) - 1)
Kill DestFolder + "\*.adtg"
On Error GoTo 0

ADO.RegisterConnectionFile "D:\CustData\OB\$WEB\odbc.txt"
ok = ADO.ConnectOK(CN) ' open connection

ReDim Tables(0) As String
Tables = ADO.GetTables(CN)
Dim tc As Long

Screen.MousePointer = vbHourglass
DoEvents
For tc = 0 To UBound(Tables)
    SQL = "Select * from [" + Tables(tc) + "];"
    ok = ADO.OpenRSROOK(CN, RS, SQL) ' open readonly recordset
    If Not ok Then
        MsgBox ADO.GetLastError(CN)
    Else
        RS.Save DestFolder + "\" + Tables(tc) + ".adtg", adPersistADTG
        RS.Close
        Set RS = Nothing
    End If
    DoEvents
Next tc

Screen.MousePointer = vbDefault
CN.Close
MsgBox "Done"
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7128808
Any progress?
0
 
LVL 4

Author Comment

by:PBuck
ID: 7140997
Hate to admit this ... but I just started on this last weekend.  I am gonna try the pure ADO route for both import and export (testing against Sybase and SQL Server). Since I have control of the tables, I should be able to utilize a header function for the import function to read and know which table the data is for.

I built the GUI skeleton to let the user choose which table (or all) etc.  So now I need to add the guts of the program and see how things fly.

Until I come across a successful function I will then award the points.  Thanks everyone for all your patience!
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7348072
This question appears to be abandoned. A question regarding it will be left in the CleanUp
area; if you have any comment about the question, please leave it here.

Unless there is objection or further activity, one of the moderators will be asked to accept the comment
of <inthedark >.

DO NOT ACCEPT THIS COMMENT AS AN ANSWER.
0
 
LVL 5

Expert Comment

by:Netminder
ID: 7373305
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now