Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
23
Erster Beitrag:
vor 1 Jahr, 6 Monaten
Letzter Beitrag:
vor 1 Jahr, 6 Monaten
Beteiligte Autoren:
StanleyBarnett, Arie, DW, Fabrice Harari, Al, Allard, Mr Black

Importing MSSQL to HFSQL Client/Server

Startbeitrag von StanleyBarnett am 03.07.2016 08:49

Hi,

Can someone help me (step-by-step) in importing a MSSQL table that is greater than 2gb into HFSQL c/s?

All attempts I've made creates a 2gb max HFSQL classic table. Then after it gets created, I change its type to client/server with the 2gb switch set to "greater than 2gb". Then a 2nd import overwrites it back to a 2gb max classic file. I've tried it every way I can think of with no success.

Thanks, Stanley

Antworten:

Hi,

How do you manage connections?

I think you just need to connect to MSSql copy table then connect to hyperfile cs and copy the table again.

I gusee if you are not connected to anything windev will create a hyperfile file.


regards
Allard

von Allard - am 03.07.2016 14:12
Hi Allard,

Quote
Allard
How do you manage connections?


Thru WinDev's import wizard's ide. To establish the connection to MSSQL, I've tried both Native access and ole db. Both ways creates 2gb max and classic tables. Instead I need c/s and greater than 2gb files. Also Allard, you ask about the connection... Why would that matter?


Quote
Allard
I think you just need to connect to MSSql copy table then connect to hyperfile cs and copy the table again.


Not sure what you mean, please elaborate... Are those steps performed within WinDev? Note that I'm technically trying to import the data, not copy.

Thanks, Stanley

von StanleyBarnett - am 03.07.2016 17:55
Hi Stanley,

it looks like you are talking about importing from the analysis, while Allard is talking about importing in your code...

From your test, it looks like the analysis automatic import is limited to 2GB... So Allard's solution is probably the only one...

Import the file descriptions in your analysis (without the data) as HF classic files with more than 2 GB

then in your code, create an alias (halias) of each file you want to import, declare that the alais is using mssql connection (with hchangeconnexion) then loop on the mssql file (hreadfirst/next) and write in the HF classic file (hadd)

ALSO: in order to speed up the process, deactivate the indexes on the HF files, and reactivate/reindex at the end of the import, it will be much faster this way

Best regards

von Fabrice Harari - am 04.07.2016 15:48
Hi Fabrice,

Quote
Fabrice
Import the file descriptions in your analysis (without the data) as HF classic files with more than 2 GB


I do not want classic, instead I need c/s. The import always creates 2gb max files, so how do I "as HF classic files with more than 2 GB"? I know I can edit each table after the import and set the 2gb switch. Is that what you are talking about? If so, is there a way to set them all at once instead of opening each table's description? I hope so, otherwise I'll be 10 times slower...

Quote
Fabrice
then in your code, and then loop on the mssql file (hreadfirst/next) and write in the HF classic file (hadd)


I do not have any code, nor do I know where to begin. I do understand the concept. You say "and write in the HF classic file" I need c/s instead of classic, so how does that change your approach? Different commands used for c/s?

Any examples of what I'm trying to do?

Is this one of them PC Soft's half-baked solutions. I would surely expect that their import tool to be complete and robust and able to handle all the options... or am I expecting too much? I have wasted way too much time on what should be a trival task, with still no positive results.

So far, I'm dissatisified with my results with PC Soft's tools... I really want to make this work, after all I spent a lot of money and time with it. I know my frustration is showing!

Thanks Fabrice,

von StanleyBarnett - am 04.07.2016 22:55
Hello Stanley

HF Classic is the default file structure. HFCS is just a wrapper around a set of HF Classic files.
So do the import using classic files and then import them into your HFCS data base.

Regards
Al

von Al - am 04.07.2016 23:03
Stanley. It sounds like you are almost there. You already know that if you are going to do this without code and use wizards instead, then it's a three step process.

1. Import the Analysis descriptions - no data.
2. Edit the Analysis descriptions, change type to HFCS and set 2 gig management where needed
3. Import the data using the wizard.

It would seem that at either step 2, you are not saving and generating the analysis prior to step 3 or in step 3 you are running the import descriptions wizard again instead of import data wizard.

Both of these wizards are qwerky, by passing options on second and third run. If this happens, try selecting and deselecting the use conversion check box to get the options to show while using the next and back buttons.

von Mr Black - am 05.07.2016 02:53
Hi Stanley,

personally I never use the build in wizards, because (as you are experiening now) they never act the way you want or need.

You say you don't know where to start when it comes to coding? Is that because you are new to Windev or new to writing code. Can you share that with us, so we can keep that in mind and give better answers.

Try putting this piece of code in a local procedure (start that with f9), or just behind a button on a new window (run your app and press the button). I would also put some breakpoint and follow the execution line by line, to see if it works.

I copy/pasted this from one of my projects, copying records from mssql to hf-classic. So it may need a tweak here and there. I also left out all kinds of errorhandling to simplify it.
Also notice the hCopyAutoId-option., otherwise all unique-keys are regenerated and you will loose relationship between for instance orders an orderlines. (it's the same as the "SET IDENTITY OFF" in MSSQL)

dsHFSQL is Data Source
cHFSQL is Connection
cHFSQL..Server = "localhost"
cHFSQL..Provider = hAccessHFClientServer
cHFSQL..Database = "your_db_name"
cHFSQL..User = "admin"
cHFSQL..Password = "??"

cMYSQL is Connection
cMYSQL..Server = "192.168.0.1"
cMYSQL..Provider = hNativeAccessMySQL
cMYSQL..Database = "your_db_name"
cHFSQL..User = "some_user"
cHFSQL..Password = "some_password"

HChangeConnection(Tbl_Orders,cMYSQL)

HAlias(Tbl_Orders,dsHFSQL)
HChangeConnection(dsHFSQL,cHFSQL)
HChangeName(dsHFSQL,Tbl_Orders..Name) // need this from classic to hfsql, otherwise the .FIC extension will become part of the filename. Nost sure if you need this now

FOR EACH Tbl_Orders
HCopyRecord(Tbl_Orders,dsHFSQL,hCopyAutoId)
HAdd(dsHFSQL)
END
HCancelAlias(dsHFSQL)

von Arie - am 05.07.2016 08:02
Ah I see you are using mssql. I misread that and thought you were on mYsql
cMSSQL is Connection
cMSSQL..Server = "192.168.0.1"
cMSSQL..Provider = hOledbSQLServer
cMSSQL..Database = "your_db_name"
cMSSQL..User = "some_user"
cMSSQL..Password = "some_password"

von Arie - am 05.07.2016 08:05
Hi Arie,

Quote
Arie
You say you don't know where to start when it comes to coding? Is that because you are new to Windev or new to writing code.


I am new to WinDev and new to writing code in WinDev/wlanguage. I am not new to writing code in VFP, as that is why I said I understand the process.

Thanks, Stanley

von StanleyBarnett - am 05.07.2016 14:33
Hi,

I still believe the import wizards should always work reliably and completely because

1. for a new customer that looking to switch to the new tools, it is most importantly for them to easily and quickly get their data into the new tool.

2. PC Soft brags about their "10 times faster", and for that to happen, it must be reliable and work as expected. I have abandoned Win/WebDev several times already because of this non-sense where things do not work as expected.

Did Clarion's import work as expected? I can say that VFP's import and export wizards worked flawlessly as well as MSSQLs.

Stanley

von StanleyBarnett - am 05.07.2016 14:51
Hi Arie,

Thanks for the script. I will try it and study all its contained commands and functions.

Also, does this script assume the destination hfsql c/s database already exists? If so, how is that done since their import only does classic? How does the script do the conversion from classic to c/s handled?

And how "in code" do I set the 2gb switch? To make it part of the script.

And I assume Tbl_Orders is a single table. If so, looks like a lot of work importing a whole database with a 100 or so tables where I would have to open and edit the script for each table. If so, PC SOFT NEEDS TO FIX THIS, or they risk losing customers like me.

Stanley

von StanleyBarnett - am 05.07.2016 15:07
Stanley,

look in the Help for HDescribeFile this might get you started.

DW

von DW - am 05.07.2016 17:18
Stanley,

you can use HLIstFile() to retrieve all table names

sDatabase is string = HListFile(cMSSQL)
FOR EACH STRING sFile OF sDatabase SEPARATED BY CR
run the script mentioned earlier
END

And I agree: all should work out of the box to be 10x faster. But that's not in our hands.
I have used quite a few programming languages and database tools. And I always end up with some home brewed tools-library, doing the things the way I want it.

von Arie - am 05.07.2016 17:36
Hi Arie,

Thanks for the script. I agree that once you have mastered the language or have special needs, then I too create scripts for special needs, but not for basic ad-hoc functionality (to quickly import a database with our data) into HFSQL from other sources.

Looks like importing within WX is no easy task, but a full blown coding task. This translates to 1000 time slower...

What I find truly amazing is that "this lack of core functionality" is acceptable by PC Soft's user group. Why does the community accept this half-baked behavior?

I'm on the verge of jumping ship because of silly stuff like this. But look at how much money and time I'd lose, so therefore I retry PC Soft$ wanting and trying to get it to work. This reminds me of when I was selecting a toolset back in the day. I tried VFP 3.0 and another much easier tool and finally chose the much easier tool because it looked like VFP would take too long to be productive. So I started writing a multimedia app with the easy tool and when almost completed I ran into a snag that the program was unable to overcome. So after all that time, I had abandoned it and look at the tool sets again and finally chose VFP as I knew that it was capable to achieve whatever I could throw at it. And it took me a while to become productive, but I was able to quickly deliver whatever the customer wanted, and in style...

Remembering that experience and these half-baked procedures, wizard and functions in WX is causing me to think that I may be duplicating a past bad experience just because of the learning curve... And now that Microsoft owns open source Xamarin looks like Visual Studio could be a really good development environment. Not to mention that there are tons of MS$ help, forums, 3rd party plugins, extensions, how-to white papers and videos mostly in English and tooling,

For a little while longer, I'll keep trying this WX stuff... I know there is the learning curve and that's OK, its the things that doesn't work or cumbersome and little to no English help or videos. There is also NO published list of broken functions and procedures from PC Soft$...

Thanks again, Stanley

von StanleyBarnett - am 06.07.2016 00:44
Hi Stanley

A few things...

1. If you have identified a bug, contact the SUPPORT and give them the exact reproduction protocol so that it CAN be fixed. I have never have to import a DB bigger than 2GB from inside the development tool (as I need to be able to import them later for my customers, I need my own code for that) and I'm guessing it's the same things for quite a lot of my colleagues.

2. If you want to test/learn, do it on a smaller DB. It's a very simple fix without any code to write.

3. I have been using this tool for more than 20 years and I have NEVER hit a snag that I was not able to overcome, work around, or get fixed by PCSoft... So changing tools like you describe may not be the only solution... Now if you are fixed in your ways and unable to be flexible, then by all means, M$ tools are for you...

Best regards

von Fabrice Harari - am 06.07.2016 11:18
Stanley,

Please change tools... It is apparent that you are inflexible and are unwilling to change. You criticize what you do not know when most of the time it is your fault for not knowing the product. Nobody has the magic pill that does what everyone wants so go find the one that does most of what you want it to do I put up with what it can not.

my 2 cents

DW

von DW - am 06.07.2016 11:35
Stanley,

I used one of the first versions of Oracle Forms. Big company, high quality databases but after a few months we find out their forms-tool is was rubbish. And redesigned the whole thing using Delphi (and still the Oracle database).
So who can tell if your decision today is still a good one tomorrow. I cannot.

Can you give us some insights about the software you want to build?
Data handling? Windev is an execellent choice for sure.
Games? No way.
But there is much in between.

I also see you are on the US Google Windev group. When it comes to warm up PCSoft for the non French market I believe they are doing a great job. So try to get some help from them, when you find things which cannot be done. Also the US distributor should play a role here (and they do)

Then we will help you find out how things CAN be done, which is the main goal of this forum if you ask me.

von Arie - am 06.07.2016 13:10
DW,

Quote
DW
Please change tools... It is apparent that you are inflexible and are unwilling to change.


I probably am inflexible and unwilling to accept tools that
1. has little training,
2. bugs are not quickly fixed,
3. costs me lots of time in work-arounds,
4.

DW, want to make me an offer on what I've paid $3300 USD for? Includes WinDev21, WebDev21, WinDev Mobile21, WAS, and MSSQL Native Access...


Quote
DW
You criticize what you do not know when most of the time it is your fault for not knowing the product.


How am I supposed to "know the product" with such little English training materials available. Well, I guess its my fault for not knowing how to read and speak French? And yes, I have successfully done a lot of the basic English tutorials which caused me to start trying the techniques with my own data, hence this import issue whereas WinDev's import is completely failing. Nothing is mentioned in any othe tutorials regarding this thread. 5 months ago I was trying to import it in version 20 and I was having the same issues and I eventually gave up on it. I was wanting to test performance on a mixed full text plus other fields search on a 45gb table.

Quote
DW
Nobody has the magic pill that does what everyone wants so go find the one that does most of what you want it to do I put up with what it can not.


I agree, none does it all, however I do expect the software to be reliable and solid with good support and training (English). And bugs quickly fixed, and a published bug list with work-arounds.

Thanks,
Stanley

von StanleyBarnett - am 07.07.2016 04:28
Hi Fabrice,

Quote
Fabrice
If you have identified a bug, contact the SUPPORT and give them the exact reproduction protocol so that it CAN be fixed.


I cringe at the thought of PC Soft's support. Too many middle men to get an answer and it takes weeks. I started a support incident with them on June 9 and it is still unresolved. I submitted screenshots, a Visual Studio dump file and a registry export file.

I will submit a new request for this and we will see what happens.


Quote
Fabrice
If you want to test/learn, do it on a smaller DB.


I always use large tables (in terms of size and record count) for testing as generally performance is where the issues come into play. Poorly constructed queries and mal-formed indexes always runs fine on small tables.


Quote
Fabrice
It's a very simple fix without any code to write.


Not sure what you mean here. Please explain...


Quote
Fabrice
Now if you are fixed in your ways and unable to be flexible, then by all means, M$ tools are for you...


If fixed in your ways and unable to be flexible means accepting software where I've spent 30-40 iterations of trying to get my 10+gb MSSQL data tables into a non file based (c/s) hfsql database, and all iteration has issues, then I'm probably inflexabile. As far as M$ is concerned, their stuff works as advertised and if a bug surfaces it is quickly fixed. Not to mention tons of training materials and a user base into the millions.

I chose PC Soft as it looks like a good single code base toolset, but these issues are killing the appeal...

Thanks, Stanley



Thanks, Stanley

von StanleyBarnett - am 07.07.2016 05:03
Hi MrBlack,

Quote
Mr Black
1. Import the Analysis descriptions - no data.
2. Edit the Analysis descriptions, change type to HFCS and set 2 gig management where needed
3. Import the data using the wizard.


I need to know where in the menu structure to do #1 and #3 above. No problem with #2.

When selecting "Import the descriptions from files/tables" menu item in the Analysis tab, the next window is titled "Importing Data Files" and after running it I always get and see live records when viewing them using WDMap. I expect to see no data in WDMap after running #1. So it would be helpful if you would point me to the menu option responsible for items #1 and 3. If you are referring to the same place, then how is the "no data" set?

Also both Import menu options leads to the place. Version 21 65s#

Thanks for your help...

Stanley

von StanleyBarnett - am 07.07.2016 06:03
Hi Arie,

Quote
Arie
Can you give us some insights about the software you want to build?
Data handling?


I need to do desktop, web and mobile apps and therefore have already purchased them all as well as WAS and MSSQL Native.

Data will come from registry, .ini, VFP, MSSQL and delimited files.

Thanks, Stanley

von StanleyBarnett - am 07.07.2016 06:09
Hi Arie,

You have been most helpful by providing solutions.

Thanks, Stanley

von StanleyBarnett - am 07.07.2016 06:12
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.