Liberty BASIC Community Forum
« SQLite Program Generator »

Welcome Guest. Please Login or Register.
Nov 24th, 2017, 10:51am


Rules|Home|Help|Search|Recent Posts|Notification


« Previous Topic | Next Topic »
Pages: 1 2  Notify Send Topic Print
 hotthread  Author  Topic: SQLite Program Generator  (Read 850 times)
meerkat
Junior Member
ImageImage


member is offline

Avatar




PM

Gender: Male
Posts: 67
xx SQLite Program Generator
« Thread started on: Jun 9th, 2017, 08:33am »

This app will generate a liberty basic program to maintain SQLite tables.
You give the program the location of your database and the table you want maintained and it generates a c:\table_name.bas program.

The generated program.
- Lists the info at 25 lines per page
- Go to [Prev] or [Next] page
- Go directly to a certain page
- Sort on any field
- Drill down. Can search with wild card(*).
--- xx*=field begins with xx
--- *xx field ends with
--- *xx* field contains xx
- Add, change, delete, and add like to maintain your tables.
- Create .csv spread sheet for any page or all pages on drill down
- Auto sequence selected fields. It reuses numbers not just adding to the end.

You can get lb_gen.bas here http://kneware.com/libertybasic/

There is also a SQLite DB called liberty.db. It only has one file called state with the states of the US and population etc.

It's my first program.. so I'm sure there are better ways to list data.
Any suggestions or changes are welcome.

--adding--
JOINS
Adding Drop Downs for field changes.
Re size windows
reports

This is new so report problems and bugs.

Thanks for the help
Dan

« Last Edit: Jun 9th, 2017, 09:44am by meerkat » User IP Logged

metro
Full Member
ImageImageImage


member is offline

Avatar




PM

Gender: Male
Posts: 194
xx Re: SQLite Program Generator
« Reply #1 on: Jun 9th, 2017, 7:56pm »

Thanks for sharing Dan

others will need the Dll to try it out... download from here
https://basic.wikispaces.com/file/view/SQ3_4_LB%20%281%29.zip/501314144/SQ3_4_LB%20%281%29.zip

User IP Logged

Win10 64 HP laptop + desktop Mint Linux 64bit
CarlGundel
Administrator
ImageImageImageImageImage


member is offline

Avatar

author of Liberty BASIC


Homepage PM


Posts: 2833
xx Re: SQLite Program Generator
« Reply #2 on: Jun 9th, 2017, 8:03pm »

Yes, thank you very much Dan. SQLite has been on my mind of late, and your contribution is timely.

Here is the link to actual page on wikispaces where the DLL is located.

https://basic.wikispaces.com/SQ3_4_LB.dll+Found%21%21%21

Perhaps your code could be added as a contribution to the Liberty BASIC Programmers Encyclopedia?

-Carl
« Last Edit: Jun 9th, 2017, 8:05pm by CarlGundel » User IP Logged

author of Liberty BASIC
http://www.libertybasic.com
meerkat
Junior Member
ImageImage


member is offline

Avatar




PM

Gender: Male
Posts: 67
xx Re: SQLite Program Generator
« Reply #3 on: Jun 10th, 2017, 05:50am »

I don't know how to add it to the library. I'll look into what it is and how to do it. But it's fine with me.

I'm adding stuff to the program, so it's a work in progress. Maybe I'll wait a couple weeks so I can get the bugs out, clean it up, and add some stuff.
The latest version is always available at the link above. And it is changing.

Since it does generate basic code, it can be altered to anyone's exact needs.

I still don't know if I'm listing stuff correctly. Maybe someone can show me the right or better way. I don't like the way it list, but maybe that's the best or only way. Wish there was the ability to set a value in buttons so you can have multiple buttons with the same handle. Or is there?

And since I'm a newbe, I'm sure there are bugs and better ways to do this.

A helpful hint and a caution. I did find when I looked at SOLite meta data, it does not have perfect data. For example, the sql field in the sqlite_master table of the tbl_name record is a mess. Be careful.

Any help or suggestions will be appreciated...
Good luck.
Dan


User IP Logged

CarlGundel
Administrator
ImageImageImageImageImage


member is offline

Avatar

author of Liberty BASIC


Homepage PM


Posts: 2833
xx Re: SQLite Program Generator
« Reply #4 on: Jun 13th, 2017, 9:54pm »

I am very receptive to the idea that the new version of Liberty BASIC Pro should at least have a really nice SQLite example program, if not more.

Is your example based on code from the wikis? How much of the code to call the DLL is new from scratch?

-Carl
User IP Logged

author of Liberty BASIC
http://www.libertybasic.com
meerkat
Junior Member
ImageImage


member is offline

Avatar




PM

Gender: Male
Posts: 67
xx Re: SQLite Program Generator
« Reply #5 on: Jun 14th, 2017, 08:03am »

Quote:
Is your example based on code from the wikis? How much of the code to call the DLL is new from scratch?


For SLQite I couldn't find anything that helped in the wikis I saw some code from metro and went from there.
I knew I had to list the DB tables with the same data on each line. I found a grid example from Janis and others.
I hoped for a way to list the same line over and over with buttons, textboxes etc that could be reverenced by a value such as I in the FOR I = 1 to numRows. Maybe I don't understand how to list yet, but it would have been really easy if there was some way to have a "#handle + I" and one branch for #handle and you could get the "I". The only way I could figure it out was to use the position of "Y" using graphics.

I'm adding stuff that allows Joins for listing, and drop downs (combo boxes) for data entry. But the inability to open multiple DB's is making it difficult.

The interface in Run Basic is great. Too bad it's not possible to copy that. And a interface similar to what HTML looks like it would solve the display problems..

EDIT: actually a ODBC interface would solve more problems

Overall it's not bad however!

« Last Edit: Jun 14th, 2017, 08:49am by meerkat » User IP Logged

metro
Full Member
ImageImageImage


member is offline

Avatar




PM

Gender: Male
Posts: 194
xx Re: SQLite Program Generator
« Reply #6 on: Jun 16th, 2017, 06:25am »

Thanks for the update Dan, I've been using lb_Gen.bas_New
and love where this is heading. Will save a lot of time coding.

Have you considered using a ListView for displaying table rows
I do like the Firefox add-on for sqlite
http://resources.infosecinstitute.com/wp-content/uploads/120911_2158_Understandi7.png
a layout similar to the above would look cool.

ListView Code here
https://lbpe.wikispaces.com/Listview%2C+trapping+double+click

download for msghook.dll on this post
http://libertybasic.conforums.com/index.cgi?board=api&action=display&num=1497271206
« Last Edit: Jun 16th, 2017, 06:34am by metro » User IP Logged

Win10 64 HP laptop + desktop Mint Linux 64bit
meerkat
Junior Member
ImageImage


member is offline

Avatar




PM

Gender: Male
Posts: 67
xx Re: SQLite Program Generator
« Reply #7 on: Jun 16th, 2017, 06:38am »

Thanks metro
Look good. I'm out of town for a few days. Will check it out when I get back
« Last Edit: Jun 16th, 2017, 06:40am by meerkat » User IP Logged

meerkat
Junior Member
ImageImage


member is offline

Avatar




PM

Gender: Male
Posts: 67
xx Re: SQLite Program Generator
« Reply #8 on: Jun 22nd, 2017, 2:55pm »

For those interested I made a few changes to the program.

I added Referential integrity with Drop Downs.
I also added Joins for listing joined data. The join use LEFT JOINS, but you can change the generated program.

I ran a few test, and everything seems to work. But it's new and any errors will be appreciated.

I added some docs that may help.
There is a test DB called liberty.db with a few test tables.
Again it's available at: http://www.kneware.com/libertybasic/

User IP Logged

Cor
New Member
Image


member is offline

Avatar




PM

Gender: Male
Posts: 27
xx Re: SQLite Program Generator
« Reply #9 on: Jun 25th, 2017, 07:44am »

Your most recent zip file is only 4 bytes, ie no content.
User IP Logged

meerkat
Junior Member
ImageImage


member is offline

Avatar




PM

Gender: Male
Posts: 67
xx Re: SQLite Program Generator
« Reply #10 on: Jun 25th, 2017, 08:51am »

on Jun 25th, 2017, 07:44am, Cor wrote:
Your most recent zip file is only 4 bytes, ie no content.


Thanks Cor,
Hmmm! Not sure what happened. All I did was zip the bas programhuh

Made a lot of changes in the last couple days..
User IP Logged

meerkat
Junior Member
ImageImage


member is offline

Avatar




PM

Gender: Male
Posts: 67
xx Re: SQLite Program Generator
« Reply #11 on: Jun 25th, 2017, 10:26am »

Made some bug fixes. Nothing major. But worth a download.
User IP Logged

cor
New Member
Image


member is offline

Avatar




PM

Gender: Male
Posts: 6
xx Re: SQLite Program Generator
« Reply #12 on: Jul 6th, 2017, 4:54pm »

Have you tried newer versions of the SQLite dll? I have in the past and one or two more recent versions are also workable with the wrapper.

I am currently testing your generator and its output. I haven't looked at the database yet to see how its content matches your output but I will. All observations I have I will report to you. Do you want them via PM or via the forum?
User IP Logged

cor massar, Dutch member located in Germany
meerkat
Junior Member
ImageImage


member is offline

Avatar




PM

Gender: Male
Posts: 67
xx Re: SQLite Program Generator
« Reply #13 on: Jul 6th, 2017, 7:40pm »

Quote:
Have you tried newer versions of the SQLite dll?


I have not.. I understand Carl is writing some interface to databases built into Liberty Basic. When that happens I'd like to rewrite it using native code in LB.

I'm new to LB. This is my first program. I used it as a way to learn LB. So I'm sure there are lots of better ways of doing stuff than the way I did it.

I just wrote my second program to manage databases. You can find it here with the title "eye on SQL" - ionSQL.

Thanks for the input...
Dan
User IP Logged

cor
New Member
Image


member is offline

Avatar




PM

Gender: Male
Posts: 6
xx Re: SQLite Program Generator
« Reply #14 on: Jul 7th, 2017, 04:26am »

I'd like to see your ionSQLite but I need an address for that. Hope you'll post it.

Cor
User IP Logged

cor massar, Dutch member located in Germany
Pages: 1 2  Notify Send Topic Print
« Previous Topic | Next Topic »

Rules|Home|Help|Search|Recent Posts|Notification

Donate $6.99 for 50,000 Ad-Free Pageviews!

| |

This forum powered for FREE by Conforums ©
Sign up for your own Free Message Board today!
Terms of Service | Privacy Policy | Conforums Support | Parental Controls