Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
5
Erster Beitrag:
vor 6 Jahren, 9 Monaten
Letzter Beitrag:
vor 6 Jahren, 9 Monaten
Beteiligte Autoren:
Steven Sitas, Erik Schwarz, Paulo Oliveira

WinDev 16: from HF C/S to MSSQL Server

Startbeitrag von Steven Sitas am 10.11.2011 08:39

Hello,
Lets say that I have a WinDEV app that uses a HF C/S database.
Now I want to CHANGE the app so I can use a MSSQL database instead.

I can do it easy (changing the connection) - no problem here ...

But what about the DATA?

Is there an easy way to copy ALL the data from an HF C/S database to an IDENTICAL MSSQL database?

Steven Sitas

Antworten:

Hi Steven,

I solved it that way:

Export function: Exports all files into csv text files
Import function: Imports from that files

The functions always use the current connections.
This works in both directions.
Importing into mssql needs to check the datatypes and sometimes a special formatting, especially with datetime fields. Also you need to set identity off, if you have automatic identifiers in your files.

export code extract:

FileID = fCreate(".\ImportExport\" + sDateiname + ".txt",foRead)
IF FileID = -1 THEN
Info("Datei kann nicht erzeugt werden")
RETURN
END
//
HReadFirst(sDateiname)
WHILE NOT HOut
sZeile = HRetrieveRecord(sDateiname,";")
fWriteLine(FileID,sZeile)
HReadNext(sDateiname)
END
fClose(FileID)


import to hf is easy:

HImportText(sDateiname,".\ImportExport\" + sDateiname + ".txt","",";",hCopyAutoId+hImpNoDelimiter)


import to mssql is some more complex:

// Feldliste ermitteln
//
sFeldliste = HListItem(sDateiname,hLstDetail)
sFeldlisteSql = ""
nIndex = 1
sFeld = ExtractString(sFeldliste,nIndex,CR)
WHILE sFeld EOT
//
IF nIndex > 1 THEN
sFeldlisteSql += ","
END
sFeldlisteSql += ExtractString(sFeld,1)
arrFeldtyp[nIndex] = ExtractString(sFeld,3)
//
nIndex += 1
sFeld = ExtractString(sFeldliste,nIndex,CR)
END
//
sZeile = fReadLine(FileID)
nSatznummer = 1
WHILE sZeile EOT AND sZeile ""
SqlCommand = "set identity_insert " + sDateiname + " on" + CRLF + ...
"insert into " + sDateiname + "(" + sFeldlisteSql + ")" + CRLF + ...
"values ("
//
// Values erzeugen
//
nIndex = 1
sFeld = ExtractString(sZeile,nIndex,";")
WHILE sFeld EOT
//
IF nIndex > 1 THEN
SqlCommand += ","
END
//
SWITCH arrFeldtyp[nIndex]
CASE 11 : // time
IF sFeld "" THEN
sFeld = "2008-01-01 " + sFeld[[1 TO 2]] + ":" + sFeld[[3 TO 4]] + ":00.000"
END
CASE 14 : // date
IF sFeld "" THEN
sFeld = sFeld[[1 TO 4]] + "-" + sFeld[[5 TO 6]] + "-" + sFeld[[7 TO 8]] + " 00:00:00.000"
END
CASE 24 : // datetime
IF sFeld "" AND sFeld "00000000000000000" THEN
sFeld = sFeld[[1 TO 4]] + "-" + sFeld[[5 TO 6]] + "-" + sFeld[[7 TO 8]] + " " + ...
sFeld[[9 TO 10]] + ":" + sFeld[[11 TO 12]] + ":" + sFeld[[13 TO 14]] + "." + sFeld[[15 TO 17]]
ELSE
sFeld = ""
END
END
SqlCommand += "'" + ExtractString(sFeld,1) + "'"
//
//
nIndex += 1
sFeld = ExtractString(sZeile,nIndex,";")
END
//
SqlCommand += ")" + CRLF + "set identity_insert " + sDateiname + " off" + CRLF
HExecuteSQLQuery(Sqlname,"TimeTouchOnSQL",hQueryWithoutCorrection,SqlCommand)
//


The line formatting the datetime maybe need modified depending on SQL Server version and used datetime format

Hope this helps

Erik

von Erik Schwarz - am 10.11.2011 10:19
Hi Steven,

i am exporting to csv files and importing them again.
In between changing the connection
Works in both directions.

the formatting of datetime while importing to mssql maybe need to adapt on mssql version and format used there.

Some code extracts here:


EXPORT to csv file

FileID = fCreate(".\ImportExport\" + sDateiname + ".txt",foRead)
IF FileID = -1 THEN
Info("Datei kann nicht erzeugt werden")
RETURN
END
//
HReadFirst(sDateiname)
WHILE NOT HOut
sZeile = HRetrieveRecord(sDateiname,";")
fWriteLine(FileID,sZeile)
HReadNext(sDateiname)
END
fClose(FileID)




IMPORT TO HF is easy

HImportText(sDateiname,".\ImportExport\" + sDateiname + ".txt","",";",hCopyAutoId+hImpNoDelimiter)

IMPORT TO MSSQL is some more complex

// Feldliste ermitteln
//
sFeldliste = HListItem(sDateiname,hLstDetail)
sFeldlisteSql = ""
nIndex = 1
sFeld = ExtractString(sFeldliste,nIndex,CR)
WHILE sFeld EOT
//
IF nIndex > 1 THEN
sFeldlisteSql += ","
END
sFeldlisteSql += ExtractString(sFeld,1)
arrFeldtyp[nIndex] = ExtractString(sFeld,3)
//
nIndex += 1
sFeld = ExtractString(sFeldliste,nIndex,CR)
END
//
sZeile = fReadLine(FileID)
nSatznummer = 1
WHILE sZeile EOT AND sZeile ""
SqlCommand = "set identity_insert " + sDateiname + " on" + CRLF + ...
"insert into " + sDateiname + "(" + sFeldlisteSql + ")" + CRLF + ...
"values ("
//
// Values erzeugen
//
nIndex = 1
sFeld = ExtractString(sZeile,nIndex,";")
WHILE sFeld EOT
//
IF nIndex > 1 THEN
SqlCommand += ","
END
//
SWITCH arrFeldtyp[nIndex]
CASE 11 : // time
IF sFeld "" THEN
sFeld = "2008-01-01 " + sFeld[[1 TO 2]] + ":" + sFeld[[3 TO 4]] + ":00.000"
END
CASE 14 : // date
IF sFeld "" THEN
sFeld = sFeld[[1 TO 4]] + "-" + sFeld[[5 TO 6]] + "-" + sFeld[[7 TO 8]] + " 00:00:00.000"
END
CASE 24 : // datetime
IF sFeld "" AND sFeld "00000000000000000" THEN
sFeld = sFeld[[1 TO 4]] + "-" + sFeld[[5 TO 6]] + "-" + sFeld[[7 TO 8]] + " " + ...
sFeld[[9 TO 10]] + ":" + sFeld[[11 TO 12]] + ":" + sFeld[[13 TO 14]] + "." + sFeld[[15 TO 17]]
ELSE
sFeld = ""
END
END
SqlCommand += "'" + ExtractString(sFeld,1) + "'"
//
//
nIndex += 1
sFeld = ExtractString(sZeile,nIndex,";")
END
//
SqlCommand += ")" + CRLF + "set identity_insert " + sDateiname + " off" + CRLF
HExecuteSQLQuery(Sqlname,"TimeTouchOnSQL",hQueryWithoutCorrection,SqlCommand)
//


Hope this helps

Erik


von Erik Schwarz - am 10.11.2011 10:26
thanks Erik,

I was hoping for something more "automagic" ...

Steven Sitas

von Steven Sitas - am 10.11.2011 12:33
Yuo can read from one db and write to the other using 2 connections.
something like this:

WOUT is Data Source
wanalise is string
wfile is string
wanalise=HListFile(hLstSorted)
wfile=ExtractString(wanalise,firstRank,CR)
WHILE wfile EOT
Message(wfile)
IF NOT HAlias({wfile},WOUT) THEN
EndProgram("Erro no HAlias - "+CR+wfile+CR+ErrorInfo())
END
IF NOT HChangeName(WOUT,wfile) THEN
EndProgram("Erro noHChangeName - "+CR+wfile+CR+ErrorInfo())
END
IF NOT HChangeConnection(WOUT,"GESVENGRH_OUT") THEN
EndProgram("Erro no HChangeConnection - "+CR+wfile+CR+ErrorInfo())
END
IF NOT HDeleteAll(WOUT) THEN
EndProgram("Erro no HDeleteAll - "+CR+wfile+CR+ErrorInfo())
END
HReadFirst({wfile})
WHILE NOT HOut()
HCopyRecord(WOUT,{wfile})
IF NOT HAdd(WOUT) THEN
EndProgram("Erro no HAdd - "+CR+wfile+CR+ErrorInfo())
END
HReadNext({wfile})
END
HClose({wfile})
HCancelAlias(WOUT)
HCancelDeclaration(WOUT)
wfile=ExtractString(wanalise,nextRank,CR)
END


von Paulo Oliveira - am 10.11.2011 14:16
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.