Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am tasked to convert an old Clarion App to new one under SQL Server 2012.

All I have is 200 tables with TPS extension which I can open only by program called TopScan. (No odbc, no technical documentation, no support).

I am making an C# program which is going to extract data to SQL.

Structure of data I can see in texts like this

                   FILE,DRIVER('TOPSPEED'),NAME('C:\bIRA2013\BIRA_ARHIMED\PART.TPS')
PART:K_SIFPAR        KEY(+PART:SIFPAR),NOCASE,PRIMARY
PART:K_ZIRO          KEY(+PART:ZIRO),DUP,NOCASE
PART:K_NAZIV1        KEY(+PART:NAZIV1),DUP,NOCASE
PART:K_GRUPA         KEY(+PART:GRUPA),DUP,NOCASE
PART:K_PORBR         KEY(+PART:PORBR),DUP,NOCASE
PART:K_KOMER_SIFPAR  KEY(+PART:KOMER,+PART:SIFPAR),DUP,NOCASE
PART:K_BANKA_SIFPAR  KEY(+PART:BANKA,+PART:SIFPAR),DUP,NOCASE
PART:K_GRAD_SIFPAR   KEY(+PART:GRAD,+PART:SIFPAR),DUP,NOCASE
PART:K_NACPL_SIFPAR  KEY(+PART:NACPL,+PART:SIFPAR),DUP,NOCASE
PART:K_NACPLD_SIFPAR KEY(+PART:NACPLD,+PART:SIFPAR),DUP,NOCASE
PART:OPIS            MEMO(2000)
RECORD               RECORD
PART:SIFPAR            STRING(10)
PART:ZIRO              STRING(20)
PART:PORBR             STRING(13)
PART:PORBRBIH          STRING(10)
PART:NAZIV1            STRING(40)
PART:NAZIV2            STRING(40)
PART:GRUPA             STRING(10)
PART:KUP               STRING(2)
PART:DOB               STRING(2)
PART:ADRESA            STRING(30)
PART:GRAD              STRING(25)
PART:PBROJ             STRING(5)
PART:TEL1              STRING(15)
PART:TEL2              STRING(15)
PART:FAX               STRING(15)
PART:OSOBA             STRING(30)
PART:RABAT             DECIMAL(5,2)
PART:RABATD            DECIMAL(5,2)
PART:CASSA             DECIMAL(5,2)
PART:CASSAD            DECIMAL(5,2)
PART:NACPL             STRING(2)
PART:NACPLD            STRING(2)
PART:KOEF              DECIMAL(5,4)
PART:DVO               LONG
PART:BANKA             STRING(6)
PART:VEZDOK            STRING(10)
PART:IZJAVA            STRING(10)
PART:KOMER             STRING(3)
PART:EMAIL             STRING(40)
PART:WWW               STRING(60)
PART:ID                STRING(2)
PART:D                 LONG
                     END
                   END

I think that ROWS between RECORD and END representing structure of table. When I run through all tables I find following types of data as in list

STRING(10) --many sizes (10,20,100,200)
CSTRING(256) --many sizes (2000,3000)
LONG  
DECIMAL(7,4) 
BYTE 
REAL
GROUP

How should I map these types to SQL Server types?

For me DECIMAL is only direct hit, but I am not so shure even for that.

Why I am so puzzled is that I can't find corresponding type for DATE.

Sub question is , How Clarion holds dates in tables ?

share|improve this question
    
There IS a DATE datatype in SQL Server 2012 - I don't see any issue there.... –  marc_s Sep 13 '13 at 8:11
    
@marc_s I know that there is DATE in SQL but I can't find way Clarion holds dates and in what type of data is reserved for date fields –  adopilot Sep 13 '13 at 9:53
add comment

2 Answers

Personally, I would buy the odbc driver and import all the files with the SQL wizard http://www.softvelocity.com/drivers/tsodbc.htm

share|improve this answer
add comment

Tough task.

I would assume the worst and create:

  • strings(x) as nvachar(x)
  • Long as BigInt
  • Dec(x,x) as Dec(x,x)

After that you'll have to look at the data in each column individually and make decisions on your findings.

Dates might be stored as strings. (MS is guilty of this as well in TypePerf.)

share|improve this answer
    
Bad habits to kick : mis-handling date / range queries - you should not store dates as strings - you're just asking for trouble that way. Use the DATE or DATETIME datatypes! –  marc_s Sep 13 '13 at 11:18
    
I completely agree. Dates should always be stored in a date datatype. –  Dennis Post Sep 13 '13 at 12:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.