Help getting started with a simple app using SQL please

jonhn · Post by **jonhn** » Sun Sep 22, 2019 8:58 pm

Hi all,
I have started on trying to move my 20yo VO application to X#, (which would certainly be impossible for me to do without the support and encouragement from the Devteam)
My VO app uses DBFCDX and I would like to learn how SQL works with X# and get ready to rewrite all the database access and management when my application is moved over, if I have to.

I have been playing with the sample applications in the XIDE, but I am stuck.

As a test/learning application, I want to create a simple SQL database, import some equipment data from a CSV and then allow the user to search and filter on various criteria displaying the information - kind of like the door colors example I found in the Pearls (or Examples?) section.

There is probably already a sample in the forum somewhere that will help me- can anyone point me to it please, or some other basic information for the X# / .NET beginner?

When large sections of my old VO app have been "exported" successfully I will be able to study how the code has changed from VO to X#, how to create a new database and import from a CSV and basic stuff which will give me some insight (I hope) but for now I am finding it tricky.

With DBFCDX I create a simple DBF like the following:

aStruct := {{"SUBURB" , "C",25,0 }, ;
{"DELIVERY" , "N",10,0 }, ;
{"HOURS" , "N",6,2 }, ;
{"KMS" , "N",7,2 }, ;
{"TOLLS" , "N",6,2 }}

IF DBCREATE("SUBURBS",AStruct, "DBFCDX", TRUE,,,FALSE)
DBCREATEINDEX("SUBURBS","SUBURB",{||_field->SUBURB},FALSE)
DBCLOSEAREA()
ENDIF

Any advice on who to do the same thing with SQL and then reading and writing to it would be great.

Thank you.
Jonathan

jonhn · Post by **jonhn** » Mon Sep 23, 2019 9:24 pm

Maybe my question should be broader... "IF I should, not HOW do I";
I am planning the next phase for my application, once it is moved from VO to X#. It is a non-commercial - my company uses it extensively and I have about 10 other "customers" using it in their businesses.

There are about 65 DBF databases used - some used more than others. Is it worth converting it all to SQL?

My reasons for first thinking that it IS;
1) DBF databases are viewed as "legacy" systems and SQL is faster/more secure (-BTW I have NEVER had any loss of data, and only 2 or 3 corrupt databases caused by power cuts or network faults -the last one over 10 years ago. A corrupt FPT memo file recently that had to be rebuilt, but I would rate my findings as almost 100% reliable DBFCDX system.)
2) Important for web facing portals (which I want to develop)
3) I am stuck on ReportPro 2.12 (came with VO2.6 or 7) and I think later versions require SQL.

A lot of the DBF files are just created on the fly, or temporary files for invoicing, or used as intermediate for generating a report so maybe I can leave a lot of those alone and only convert the main customer, equipment and history files into SQL?

Or, maybe there is nothing wrong with DBFCDX for en existing, old application?

There is a lot of historical discussion in the VO groups... Is the general consensus still the same?
https://groups.google.com/forum/#!searc ... DXfTpKp9oJ

Thanks!

FFF · Post by **FFF** » Mon Sep 23, 2019 10:18 pm

If your 2) will become reality and "important", and it can't be solved otherwise, it might be worth to "jump". If not, and your scenario is not going to change drastically, i wouldn't consider the work really necessary. As it's not commercial, i wouldn't bother a lot about "legacy" or not (- even MS, after telling for years that Winforms is "old" and dead, changed their mind and will support it in Core

)

When you have a invoice or similiar licence proof for you RP2, you might provide it to Robert and get access to the Rp2.33, compiled against the new X# rumtime, which probably will see improvements in the time to come (see roadmap), so no dead end at all.

I certainly wouldn't consider a "mixed" scenario. If you bite the bullet to learn SQL, then use it.- I'm tempted for quite some time to do the same, jumping to Postgres, which is amazingly powerful, free, constantly evolving, while still easy to install and maintain...

robert · Post by **robert** » Tue Sep 24, 2019 6:45 am

John,

I am afraid that your question is way to broad. There have been many many sessions about moving from DBF to SQL on VO conferences in the past.
About 3): I am not sure why you are stuck on ReportPro 2.12. There are newer versions of ReportPro and all of them work and will work with DBF files.

Robert

jonhn · Post by **jonhn** » Tue Sep 24, 2019 8:36 am

Thank you Robert, (and Karl),
You are right - I will keep looking through the archives and try to find a "how to" on using SQL from X#. If anyone can point me right at something that has a simple example of creating a SQL database from X# in core or VO dialect it would be great!
And, Report Pro - yes, again thank you, I will buy a newer version and see how I get on.

I think, after reading Karl's advice on Postgres / MySQL and researching it a little more, I will try MySQL to begin with.
Thank you all.

FFF · Post by **FFF** » Tue Sep 24, 2019 9:01 am

Two misconceptions: You don't have to "buy" a newer RP - simply contact Robert.
And, imho, don't start wandering in MySql land. PG is better, especially, it has REALLY great docs. The windows install is a job < 5min, even i managed it

- got to https://www.postgresql.org/download/windows/ and fetch the installer..., will run on your machine with no measurable impact.

wriedmann · Post by **wriedmann** » Tue Sep 24, 2019 9:14 am

Hi Jonathan,
do yourself a favor and don't use MySQL if you have yet to start.
MySQL has lost it's "free" status years ago since it has been acquired by Microsoft.
If you would like to remain in the Microsoft world, you can use the free Microsoft SQL Server Express https://www.microsoft.com/en-us/sql-ser ... -downloads (this version is limited by database size and connected users), or otherwise use PostgreSQL (true free database server, multiplatform, not limited in any way).
To work with an SQL database, you need some management tool.
For the MS SQL Server there is the SQL Server Managment Studio, for others you can find others (I'm using Navicat Premium Essentials because it supports all SQL servers I need - Oracle, MySQL, MS SQL, PostgreSQL, SQLite), but there are some free tools too.
Of course you can build your own tools, but I would not reccomend that.
Wolfgang
P.S. If you can afford to wait a few months: wait for the new ADO.NET based VO compatible SQL classes that will work with the VO compatible GUI classes

lumberjack · Post by **lumberjack** » Tue Sep 24, 2019 9:35 am

Hi Jonathan

jonhn wrote: I will keep looking through the archives and try to find a "how to" on using SQL from X#. If anyone can point me right at something that has a simple example of creating a SQL database from X# in core or VO dialect it would be great!
I will try MySQL to begin with.

Have a look in the Pearls Forum, Phil Hepburn did some postings regarding Entity Framework to create SQL databases with X#.
Under the Help tab, there is also a link to work Phil has done. The XSharp Documentation (User driven) might also have some info. I wrote a "bare bones" article for UsingVulcan that never made it (due to no 2nd edition). Maybe I should ask Robert to upload it here. Was about some DBF to SQL migration tricks. No rocket science, but it might give some ideas.

And as Karl and Wolfgang indicated, just skip MySQL, go for PostgreSQL.

jonhn · Post by **jonhn** » Tue Sep 24, 2019 9:36 am

OK, PostgreSQL it is.
Thank you all for the suggestion and advice. and... I just noticed there is a new build up today... thank you for all the hard work on that!

FFF · Post by **FFF** » Tue Sep 24, 2019 1:30 pm

jonhn wrote:OK, PostgreSQL it is.

Good decision

FTR, i use for quite some time DBMigration.Net from fishcodelib.com. Comes free with small restrictions, and for a small amount, you get full power and a lifelong licence. Let's migrate from almost any format to any format with some clicks. E.g. from DBF/CDX/FPT to PG, migrates the indices and memos.

Main menu