Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
12
Erster Beitrag:
vor 1 Monat, 1 Woche
Letzter Beitrag:
vor 1 Monat
Beteiligte Autoren:
Arie, Fabrice Harari, Stefan Bentvelsen, steve erts

HFSQL22 - convert to MSSQL

Startbeitrag von Arie am 09.01.2018 14:39

Hi,

I am converting my database from HFSQL to MSSQL. The IT-guys of one of the companies which uses my software don't like rare and unknown databases as hfscl is, they want a more mature (:)) database so to speak.

I loose the automatic modification but that's not a big issue.

I wonder if someone did this transition before. So I have a few questions:
1. will I run into known issues?
2. Any handy tool to convert/import all data from HFSQL to MSSQL?
3. Is it advisable to go for the NATIVE driver right from the start?

One of the issues I ran into already is that a LOT of fields are NOT defined as "null authorized", which causes troubles. I just opened and ticked hundreds of fields in the analysis to change that :sneg: (Looks like these specific fields/tables came from previous WD versions back to WD9 or so).

Antworten:

Hi Arie,

I've made a procedure in my database management module ones to convert the data in my HFSQL database to scripts for MSSQL. One script per file. For each record is written a SQL statement.

May be, you can use it?

von Stefan Bentvelsen - am 09.01.2018 16:22
Hi Arie,

1. will I run into known issues?

Yes, if you are using ANYTHING that is HF specific (array of items, stored procedures, halias, etc)

2. Any handy tool to convert/import all data from HFSQL to MSSQL?

You can write yourself very easily... hlistfile to loop on the files, 2 connexions, one to each DB, halias to use the files in the secondary DB, loop to read all records in one file of one DB and write it in the matching file in the other DB...

3. Is it advisable to go for the NATIVE driver right from the start?

Yes, as your code will work much better that way and speed will be better too.

Best regards

von Fabrice Harari - am 09.01.2018 17:56
Stefan,

some code would be nice.

I did a small test with the sqlmanager data import wizard, using the HFSQL OLEDB driver to get access to my current data. But it crashes at the very first table, when the import starts reading records :sneg:

von Arie - am 10.01.2018 08:14
Fabrice,

writing an small conversion tool is an option indeed.
I will have to disable all contraints before and also set the IDENTITY_INSERT to FALSE, otherwise all auto-id-fields will be re-numbered.

von Arie - am 10.01.2018 08:17
And what about switching to UNICODE at this point. Would that be smart right now and/or in general?

von Arie - am 10.01.2018 08:18
Hi Arie,

In a memorytable with all the files from the analyse I can select the files to export.
The following code treats each selected file:


liFilesTotal is int
liFilesCopied is int
liMaxRecs is int = 1000
liTelRecs is int
lsTempDir is string
lsDirectory is string
lsItems is string
lsItemNames is string
lsItemInfo is string
lsKey is string
lsSql is string
lsSqlQry is string
lsSub is string
lbAllFiles is boolean=True
lbCopyOk is boolean=True
lbGevuld is boolean
lbEersteRec is boolean

IF YesNo(No, "Wilt u de geselecteerde bestanden kopieren naar een lokatie op de pc ?") THEN
lsTempDir = fTempPath()
lsDirectory = fSelectDir(lsTempDir, "Selecteer de doelmap ...")

IF lsDirectory > "" THEN
HourGlass(True)
HClose("*")

// Bepaal het aantal geselecteerde bestanden
FOR EACH ROW liIndex OF TABLE_Files
IF COL_Select THEN liFilesTotal ++
END

FOR EACH ROW liIndex OF TABLE_Files
IF COL_Select THEN
liFilesCopied++
Gauge(liFilesCopied,liFilesTotal)

lbGevuld = False //; lbEersteRec = true
lsKey = ""
lsItems = HListKey(COL_NAAM[liIndex],psAnalyse,psAnalysePassword,hLstDetail)
FOR i = 1 _TO_ StringCount(lsItems,CRLF) + 1
IF ExtractString(ExtractString(lsItems,i,CRLF),5,TAB) = "1" THEN
lsKey = ExtractString(ExtractString(lsItems,i,CRLF),1,TAB)
BREAK
END
END

IF lsKey > "" THEN
liTelRecs = 0
IF HReadFirst(COL_NAAM[liIndex],lsKey) THEN
WHILE NOT HOut(COL_NAAM[liIndex])
IF liTelRecs = 0 THEN
lsSql = "SET IDENTITY_INSERT " + COL_NAAM[liIndex] + " ON;" + CRLF
lsSql += "INSERT INTO %1 (%2) VALUES"
lsItems = HListItem(COL_NAAM[liIndex],psAnalyse,psAnalysePassword,hLstNormal)
lsItemNames = Replace(lsItems,CRLF,",")
lsSql = StringBuild(lsSql,COL_NAAM[liIndex],lsItemNames)

lsItems = HListItem(COL_NAAM[liIndex],psAnalyse,psAnalysePassword,hLstDetailPlus)
lbEersteRec = True
END
liTelRecs++

// Exporteer hier het record naar een SQL-opdracht
lsItemNames = ""
FOR i = 1 _TO_ StringCount(lsItems,CRLF) + 1
lsItemInfo = ExtractString(lsItems,i,CRLF)
SWITCH ExtractString(lsItemInfo,3,TAB)
CASE "2","26","30","23","29" // "text" items
lsSub = {COL_NAAM[liIndex] + "." + ExtractString(lsItemInfo,1,TAB),indItem}
lsItemNames += [","] + "'" + Replace(lsSub,"'","`") + "'"
CASE "15" // "text" memo
IF Left({COL_NAAM[liIndex] + "." + ExtractString(lsItemInfo,1,TAB),indItem}) [= "{\rtf1" THEN
lsSub = RTFToText({COL_NAAM[liIndex] + "." + ExtractString(lsItemInfo,1,TAB),indItem})
ELSE
lsSub = {COL_NAAM[liIndex] + "." + ExtractString(lsItemInfo,1,TAB),indItem}
END
lsSub = Replace(lsSub,"'","`")
lsItemNames += [","] + "'" + Replace(lsSub,[CRLF,CR],"' + CHAR(13)+CHAR(10) + '") + "'"
CASE "1","3","4","5","6","7","8","9","12","13","17","19","20","22","27","28" ,"31" // int + reals + boolean (incl. currency)
lsItemNames += [","] + {COL_NAAM[liIndex] + "." + ExtractString(lsItemInfo,1,TAB),indItem}
CASE "14" // Date
IF DateValid({COL_NAAM[liIndex] + "." + ExtractString(lsItemInfo,1,TAB),indItem}) THEN
lsSub = {COL_NAAM[liIndex] + "." + ExtractString(lsItemInfo,1,TAB),indItem}
lsItemNames += [","] + "'" + lsSub[[1 TO 4]] + "/" + lsSub[[5 TO 6]] + "/" + lsSub[[7 TO 8]] + "'"
ELSE
lsItemNames += [","] + "NULL"
END
CASE "11" // Time
IF Left({COL_NAAM[liIndex] + "." + ExtractString(lsItemInfo,1,TAB),indItem}) > "" _AND_ ...
TimeValid({COL_NAAM[liIndex] + "." + ExtractString(lsItemInfo,1,TAB),indItem}) THEN
lsSub = {COL_NAAM[liIndex] + "." + ExtractString(lsItemInfo,1,TAB),indItem}
lsItemNames += [","] + "'" + lsSub[[1 TO 2]] + ":" + lsSub[[3 TO 4]]
IF Length(lsSub) >= 6 THEN lsItemNames += ":" + lsSub[[5 TO 6]]
IF Length(lsSub) >= 8 THEN lsItemNames += ":" + lsSub[[7 TO]]
lsItemNames += "'"
ELSE
lsItemNames += [","] + "NULL"
END
CASE "24" // Date + time
IF DateTimeValid({COL_NAAM[liIndex] + "." + ExtractString(lsItemInfo,1,TAB),indItem}) THEN
lsSub = {COL_NAAM[liIndex] + "." + ExtractString(lsItemInfo,1,TAB),indItem}
lsItemNames += [","] + "'" + lsSub[[1 TO 4]] + "/" + lsSub[[5 TO 6]] + "/" + lsSub[[7 TO 8]] + " "
lsItemNames += lsSub[[9 TO 10]] + ":" + lsSub[[11 TO 12]] + ":" + lsSub[[13 TO 14]] + ":" + lsSub[[15 TO 17]]
lsItemNames += "'"
ELSE
lsItemNames += [","] + "NULL"
END
CASE "25" // Duration
lsItemNames += [","] + "NULL"
CASE "16","18","21" // Binary memo,image,etc.
lsItemNames += [","] + "NULL"
OTHER CASE

END
END
IF lbEersteRec THEN
lbEersteRec = False
lsSql += [CRLF] + "(" + lsItemNames + ")"
ELSE
lsSql += "," + [CRLF] + "(" + lsItemNames + ")"
END
lbGevuld = True

IF liTelRecs >= liMaxRecs THEN
lsSqlQry += [CRLF] + lsSql + ";" + CRLF
lsSql = ""
liTelRecs = 0
END

HReadNext(COL_NAAM[liIndex],lsKey)
END
END
END

IF lbGevuld THEN
IF lsSql > "" THEN
lsSqlQry += [CRLF] + lsSql + CRLF
lsSql = ""
liTelRecs = 0
END
IF NOT fSaveText(lsDirectory + ["\"] + COL_NAAM[liIndex] + ".sql",lsSqlQry) THEN
Error(ErrorInfo())
END
END

lsSqlQry = ""

END
END
HourGlass(False)

// Wis de progressbar
Gauge()

IF lbCopyOk THEN
Info("Alle geselecteerde bestanden zijn correct geëxporteerd !")
ELSE
Error("Er zijn fouten opgetreden tijdens het exporteren,","mogelijk zijn niet alle geselecteerde bestanden geëxporteerd !")
END
END
END





von Stefan Bentvelsen - am 10.01.2018 10:56
Stefan thanks, looks good!

von Arie - am 10.01.2018 12:05
Hi Arie,

Quote
Arie
And what about switching to UNICODE at this point. Would that be smart right now and/or in general?


First, define SMART! :-)

More seriously, I would definitely NOT do that at the same time...

Either start by passing your HF DB in unicode and your whole program with it THEN TEST TEST TEST THEN migrate everything to mssql...

Switching to unicode is NOT trivial (by example, you'll probably have tons of execution error when passing parameters to some functions).

Now, the real question is: do you NEED unicode.
If you are working with android and iOS, then the answer is maybe.
if you are working with 'exotic' languages or plan to do so in the future, then the answer is YES.
Otherwise, no.

Best regards

von Fabrice Harari - am 10.01.2018 13:21
Fabrice, thank you.
No unicode now for me.

von Arie - am 10.01.2018 15:29
Hi Arie,

You might want to look at a program like

https://flowheater.net/en/

It's very handy for converting data between disparate formats.

You should be able to set up an ODBC connection to HFSQL and then write directly to SQL SERVER


I use it a lot for doing data conversions when I bring on new clients.

von steve erts - am 10.01.2018 17:33
FYI.

In sqlserver you have the option to "SET IDENTITY INSERT ON/OFF" to be able to set the key-fields to a specific value. I need that when copying data from a HFSQL to MSSQL.

I did some testing with the default driver AND the native sql driver of Windev.
I turns out the NATIVE driver (I have a license for that) takes cares of the above automatically when specifying the hSetAutoid-option when adding records, i.e.

Tbl_Orders.OrderID = 1234
Tbl_Orders.CustomerName = "Windev"
HAdd(Tbl_Orders,hSetIdAuto)

The regular (oledb) driver does not support this 'feature'.

von Arie - am 16.01.2018 12:11
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.