May 19, 2024, 03:15:52 AM

News:

IonicWind Snippit Manager 2.xx Released!  Install it on a memory stick and take it with you!  With or without IWBasic!


New Utility

Started by LarryMc, October 06, 2014, 09:25:10 AM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

LarryMc

Thinking about creating new utilities for the Tools menu

1. Database builder
would allow user to create mdb file and add tables and fields directly, or
generate the code to do the same so it can be pasted in a file, or
both.

2. Create an SQL query based upon selections from multiple dropdowns and then have the proper coding automatically generated so it can be pasted into an application
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

Bill-Bo

LarryMc,

That sounds great. I'm interested in databases, and I
use a few. Always looking for a better one.

Looking forward to what you come up with.

Have a good day,

Bill

LarryMc

Attached is my first stab at the layout for the database builder utility
This would be the dialog for creating the tables and fields in a mdb file.

As in all alphas, things are subject to change

comments are welcome.
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

Doc

Hey Larry.
It's been quite awhile since I did any programming with IWB, so I really don't have a horse in this race... BUT... I would think that any tool to assist with working with database projects would be very welcomed! Once upon a time, I would have offered a gigantic neck hug  and $$$ for such a tool.  :-*   :D   ;D

As a general comment though, I never really understood why the MDB format was chosen for use with IWB in the first place. My (unheeded) argument has always been that SQLite is almost the defacto standard for desktop DB applications, is well supported and still allows the data to be used -as is- on many other platforms if required.

-Doc-

LarryMc

Placing this little project on hold for now.

The reason is that I need to finish the help file for the IWBasic2.5 beta.
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

LarryMc

This topic was in the IWB2.5 board and I've moved it over here to the Database board.
It's still on hold because  I still need to finish the work on IWB's help file.

But what I did do is spend and hour or so trying a little different approach.

In my 1st layout I was using a listview to hold the names of the tables in the database and another listview to hold the field information. for each table as the table was added/selected.

I got to thinking; I was going to have to really jump through some hoops to make editing things work the way I sort of envisioned.

Then DUH!!!  It seems this fella spent a great deal of time writing this whatchamacallit called IWGrid.  Maybe I should give it a try.

Below is a screen shot of the GUI using 2 grids in place of the 2 listviews.
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

Brian

Like it!

Will there be the facility to select which driver you wish to use?

Brian

LarryMc

I haven't allowed space for it in my GUI but sure, why not.
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

LarryMc

Quote from: LarryMc on December 26, 2014, 06:26:23 AM
I haven't allowed space for it in my GUI but sure, why not.

The GUI would look something like this
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

Brian

Good stuff!

I know you're only messing, but shouldn't "Select proper driver..." read "Select appropriate driver..." ?

I know I'm being pedantic, but you can let me off, seeing its Christmas!

Brian

LarryMc

Quote from: Brian Pugh on December 26, 2014, 07:35:12 AM
I know you're only messing, but shouldn't "Select proper driver..." read "Select appropriate driver..." ?

??? ??? ???
Santa should have brought you some glasses.
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

Brian

Ha ha - my eyes aren't that bad!

LarryMc

LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

LarryMc

After playing around a bit I decided that in this case my IWGrid wouldn't do EXACTLY what I wanted it to do so I went back to using a customized listview setup.

The attached screenshots represent the way the utility is going to look.  I'm pretty much locked into this now.
So now I'll start working on the internal mechanics of what happens when.
I think it will be pretty neat when I get through.
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

LarryMc

I know from previous experience that different  drivers sometimes require additional parameters in the DBCONNECT statement.

If everyone would post those they have used or those they are aware of the more I will be able to incorporate into the utility and the more useful the utility will become.
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

Brian

Hi, Larry,

Happy New Year to you and your wife - hope you are well

Here's some that I've used before, and some that were "on file"
Bit of a minefield, as the strings can vary quite a lot. I would be just inclined to do a basic connection string,
based on the type of driver that has been selected - you can't cater for every eventuality

The 32-bit drivers on 64-bit PCs are still there, even though they might not show in the
Data Sources control panel

Looking good, though!

Brian

pDB=dbConnect("Microsoft Text Driver (*.txt; *.csv)",schemaPath,"")

pDB=dbConnect("Microsoft Excel Driver (*.xls)",fileName,"ReadOnly=0")

pDB=dbConnect("Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)",startPath+fName61,"ReadOnly=0")

pDB=dbConnect("Microsoft Access Driver (*.mdb)",mdbPath+"BookShop.mdb","",win)

pDB=dbConnectDSN(\"XYZ CORP\",\"UID=President;PWD=Private\")

pdb=dbConnect("Microsoft dbase VFP Driver (*.dbf)","c:\\_TMG Projects","SourceType=DBF;Exclusive=No;Collate=Machine")

pDB=dbConnect("SQL Server","","SERVER=s06.winhost.com;DATABASE=DB_44925_test2;UID=DB_44925_test2_user;PWD=*******")

pDB=dbconnect("Microsoft Excel Driver (*.xls)","c:\gppgmlib\JWalk.xls","DriverID=790;FIRSTROWHASNAMES=0;READONLY=FALSE")

pDB=dbConnectDSN("TMG","")

LarryMc

LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

LarryMc

I haven't given up on the utility. With my medical problems(and my current vision problems) I have only been doing the bare minimum of programming.
When I get my vision corrected I'll try to get this done.
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

billhsln

I hope you have not given up on this project.  I think it would be worthwhile for many of our more inexperienced users.

Willing to help in any way I can.

Bill
When all else fails, get a bigger hammer.

LarryMc

To tell the truth I had forgot all about it.

I've got one more woodworking project I'm working on (a rocking chair for a friends 2year old daughter) and then I'll see if I can get back to it and get it finished during these hot west Texas summer days.

Thanks for the reminder
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

billhsln

Love the sun, can't stand the heat.  In the 100's here in Ft Worth.

Bill
When all else fails, get a bigger hammer.

LarryMc

Same here in Abilene
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

LarryMc

Well, it's taking me a while to get back up to speed; 1) up to speed in coding again (it's been a while) and 2) on this specific project that I haven't worked on in almost 4 years.

I figured out what my stumbling block was before.  How best to handle the following from a coding standpoint:

Have 2 lists
the 1st list is of the table(s) in a given database and consists of one column
the2nd list contains a list of the fields in the table selected in the 1st list. This list contains 3 columns.
The 1st column is the field name, the 2nd is the field type and the 3rd is the field size.

As it turns out the best means of implementation is to use 2 listviews.

The 1st listview (for the tables) is modified so that when a cell is selected for editing a subclassed edit control is superimposed over the cell for editing the entry and initialling the field list.

The 2nd listview(for the fields)is modified so that when the 1st cell is selected for editing a subclassed edit control is superimposed over the cell for editing the name entry.

      when the 2nd cell is selected for editing a subclassed Combobox control is superimposed over the cell for selecting the type entry.

      when the 3rd cell is selected for editing a subclassed edit control(restricted for numerics only)  is superimposed over the cell for editing the size entry.

I now have that part constructed.

Now begins the part of what happens when, like:

switching between the editing of 2 tables
making sure a field is completed before moving to next field
and that's not counting the creating of code and the actual files












LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

LarryMc

Need a little help to save me some time
QuoteCOUNTER [(1,1)
BINARY
CHAR
DATE
DOUBLE
FLOAT
INTEGER
LONGTEXT
SMALLINT
TIME
TIMESTAMP
VARCHAR
MEMO

I need to know if this is a complete list of all the field types I need to be concerned with initially.
I need to know which fields have a fixed size and what that size is so I can try to automate that entry.
If there is a max for any variable entry I need to know that.

And as a bonus
If someone can write a typical code string for creating each of the fields I would appreciate that also.

Any and all help would be appreciated.

I just interested in getting this utility working with .mdb files first; then I will look at addressing other file types.

Again, thanks in advance for any and all help.
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

Andy

Larry, I found this site which describes the fields, if that is any use to you.

https://eggerapps.at/mdbviewer/docs/en/field-types.html

Andy.
Day after day, day after day, we struck nor breath nor motion, as idle as a painted ship upon a painted ocean.