Liberty BASIC Community Forum
« Eye On SQL »

Welcome Guest. Please Login or Register.
Dec 14th, 2017, 6:31pm


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


« Previous Topic | Next Topic »
Pages: 1 2 3 4  Notify Send Topic Print
 veryhotthread  Author  Topic: Eye On SQL  (Read 1903 times)
meerkat
Junior Member
ImageImage


member is offline

Avatar




PM

Gender: Male
Posts: 67
xx Re: Eye On SQL
« Reply #30 on: Jul 26th, 2017, 07:25am »

If anyone is interested I added:
- CSV export
- CSV import
- sorts any field on browsing a table.

More on the way...

Dan..
User IP Logged

meerkat
Junior Member
ImageImage


member is offline

Avatar




PM

Gender: Male
Posts: 67
xx Re: Eye On SQL
« Reply #31 on: Jul 30th, 2017, 10:50am »

You can now generate a LB program from sql commands.
When you select Sql you can enter a sql command.
3 options are available;
1. do SQL will execute the sql command.
2. do CSV will produce a CSV file
3. do GEN will create a LB program.
The output LB program has the structure to change the variable types and their sizes.

Tested as follows:
1. downloaded the cal.db file form the site.
2. Loaded it i to the system.
3. Selected it from the Database selection
4. Entered the following sql command
Code:
SELECT  
s.schNum, 
s.assetNum, 
s.schEnd as preEnd, 
s1.schBeg as nxtBeg, 
((strftime('%s',s1.schBeg) - strftime('%s',s.schEnd)) / 60) as laps, 
(strftime('%w',s.schEnd))   as dowBeg, 
asset.descr as aDescr, 
asset.assetType 
FROM  schTime as s 
JOIN  asset 
ON    asset.assetNum = s.assetNum 
JOIN  schTime as s1 
ON    s1.assetNum = s.assetNum 
AND   s1.schBeg > s.schEnd 
AND   ((strftime('%s',s1.schBeg) - strftime('%s',s.schEnd)) / 60) >= 100 
AND   s1.schBeg = (SELECT min(s2.SchBeg) 
FROM  schTime   as s2 
WHERE s2.assetNum = s.assetNum 
AND   s2.schBeg > s.schEnd) 
AND   s.schBeg >= '2009-05' LIMIT 10
 


What does it do.
The cal.db system has "assets" and "schTime" .
Assets are things you want to schedule such as conference rooms, projectors etc.
schTime is a list of time the assets are already scheduled.
This sql command hunts for open slots available for the amount of time you need the asset, starting on a particular time and listing only a certain number of open slots.

You could easily modify the LB program to request from the user what asset they want and when they want it and how long they need it and how many openings to report. This can be done by making the 100 value a variable such as needTime$. Change the limit of 10 to a variable as numSlots$. Change '2009-05' to a variable of begDate$



User IP Logged

metro
Full Member
ImageImageImage


member is offline

Avatar




PM

Gender: Male
Posts: 194
xx Re: Eye On SQL
« Reply #32 on: Jul 30th, 2017, 11:11pm »

Not sure if its something I've done huh
downloaded cal.db and latest version of ionSQL, the db will load in ionSQL, however the tables are not shown when the db is selected.
Same with 2 previous versions of ionSQL
all other db's will display table schema within ionSQL.

good news though... query result is printed
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: Eye On SQL
« Reply #33 on: Jul 31st, 2017, 07:07am »

OOPS!

Thanks for pointing that out.

I was playing and altering cal.db. I thought I was using the one located at the download, but I was using a copy located somewhere else.

The correct one should be there now. If that still has problems let me know.

BTW.. The asset file has a assetNum and a asset type. So you could schedule by type, or a specific asset. For example if you wanted a conference room but didn't care what conference room it was you could ask for all conference rooms by searching the type instead of the asset number.

Thanks again for the heads up..

How is your forecasd for stock picks going?
User IP Logged

meerkat
Junior Member
ImageImage


member is offline

Avatar




PM

Gender: Male
Posts: 67
xx Re: Eye On SQL
« Reply #34 on: Aug 9th, 2017, 5:48pm »

Bug Fix.

I'm always surprised by sqlite options.
As many of you know, Firefox maintains information in sqlite tables.
They use a primary key to keep track of their rowid.
So if you load some of their files into the system it will give you an error if you try to delete/change their data. This has been fixed.

You may be interested to see what information Firefox has in their files:
cookies.sqlite
places.sqlite
content-prefs.sqlite
formhistory.sqlite
webappsstore.sqlite


Any problems... Let me know..
User IP Logged

clausnilsson
New Member
Image


member is offline

Avatar




PM


Posts: 7
xx Re: Eye On SQL
« Reply #35 on: Oct 6th, 2017, 10:36am »

Apologies from a newbie.

I am trying out the ionSQL.bas.

I have put both SQ3_4_LB.dll and sqlite3.dll in both windows/system32/ and in the directory where I run the .bas. I get the error: General Error - Couldn't open 'sqlite3.dll'.

Why, I wonder?
User IP Logged

Rod
Global Moderator
ImageImageImageImageImage


member is offline

Avatar

Graphics = goosebumps!


PM

Gender: Male
Posts: 5589
xx Re: Eye On SQL
« Reply #36 on: Oct 6th, 2017, 11:18am »

Generally speaking you should not place anything in the windows system directory. This needs to be handled by an installer with the necessary permissions.

Windows fiercely protects its system directories.

So you should be able to access .dlls placed in the project directory. Show us the line of code you use to open the .dll and print of DefaultDir$ just before you try to access the .dll. Is it the directory you expect? Is it a protected windows directory?
User IP Logged

clausnilsson
New Member
Image


member is offline

Avatar




PM


Posts: 7
xx Re: Eye On SQL
« Reply #37 on: Oct 7th, 2017, 1:18pm »

I read somewhere that windows/system32 was the place to put dlls. But I also put them in the project directory as it did not help to put them in system32.

By entering
print DefaultDir$
end
it is confirmed that it is the project directory.

Other than that temporary change it is ionSQL.bas:

Open "SQ3_4_LB.dll" for DLL As #sq3 ' open SQ3_4_LB.dll
calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long ' was sqlite3.dll loaded by SQ3_4_LB.dll
msg$ = Winstring(result) ' LastMessage would be "General Error - Couldn't open 'sqlite3.dll'"
if instr(msg$,"Error") then ' If we have an error
notice msg$ : close #sq3 ' close sqlite and end program
end
end if

Maybe I need to be more clear: DefaultDir$ is as expected, but I get the error eventhough both dll's are in the project directory and a non-edited version of Eye On SQL is in the same directory. Please, can anyone think of a reason? And what to do?
« Last Edit: Oct 17th, 2017, 1:15pm by clausnilsson » User IP Logged

meerkat
Junior Member
ImageImage


member is offline

Avatar




PM

Gender: Male
Posts: 67
xx Re: Eye On SQL
« Reply #38 on: Oct 12th, 2017, 09:48am »

Got a little time and combined the program generator into ionSQL.
You can now generate a LB program to maintain a selected table:
1. list the data in the table with sorts on any field
2. Drill down with wild card searches
3. do Add, Change, and Delete

For those using Run Basic, I wrote a RunBasic ionSQL program. I tried to make them as similar as possible. But because of language differences there are a few differences.

You can download at:
http://kneware.com/libertybasic/
directory:
ionSQL for Liberty basic
RunBasic for RunBasic
RunBasic has some setup and docs at ionSQL.html

Not sure, but maybe this should be sent to the library. Maybe someone has some time to do this??


User IP Logged

tenochtitlanuk
Board Moderator

member is offline

Avatar




Homepage PM

Gender: Male
Posts: 1171
xx Re: Eye On SQL
« Reply #39 on: Oct 16th, 2017, 05:58am »

Server or address problem? I can't connect to the address given..
User IP Logged

meerkat
Junior Member
ImageImage


member is offline

Avatar




PM

Gender: Male
Posts: 67
xx Re: Eye On SQL
« Reply #40 on: Oct 16th, 2017, 08:49am »

on Oct 16th, 2017, 05:58am, tenochtitlanuk wrote:
Server or address problem? I can't connect to the address given..


Sorry. I have a dual speed modem. Kinda crazy, but I have 2 wifi interfaces plugged into USB ports. One is port 80 (for server functions) and the other gets whatever port it can. Using one for downloads and port 80 for internet. I acidently unplugged port 80.

Should be ok now.. Let me know if there is still a problem. It may be slow since I'm doing a 6hr download.

Good luck.. Dan
User IP Logged

tenochtitlanuk
Board Moderator

member is offline

Avatar




Homepage PM

Gender: Male
Posts: 1171
xx Re: Eye On SQL
« Reply #41 on: Oct 16th, 2017, 11:49am »

Thanks! Worked fine. Looking forwars to seeing how it works- I've been meaning to look at 'proper' databases for some time.
User IP Logged

meerkat
Junior Member
ImageImage


member is offline

Avatar




PM

Gender: Male
Posts: 67
xx Re: Eye On SQL
« Reply #42 on: Oct 16th, 2017, 12:24pm »

on Oct 16th, 2017, 11:49am, tenochtitlanuk wrote:
Looking forwars to seeing how it works


You're welcome. I tested about everything I can think of. But as you know you can't catch everything.

This was my first LB program, so was surprised that I haven't heard of many buggs. I'm sure there is a better way than they way I wrote it.

The one I wrote in Run Basic to match the LB program, I think is better. LB really isn't set up very well for DB type stuff like forms, grids, and data checking on forms. But it works..

Good luck.. I'm out of town for a week so don't expect bug fixes this week.

User IP Logged

Chris Iverson
Administrator
ImageImageImageImageImage


member is offline

Avatar

20% Cooler


Homepage PM

Gender: Male
Posts: 2289
xx Re: Eye On SQL
« Reply #43 on: Oct 21st, 2017, 8:08pm »

Well, let's start with the basics.

https://basic.wikispaces.com/file/detail/SQ3_4_LB+%281%29.zip


This file is an archive that includes SQLite, the SQ3_4_LB DLL, a test program utilizing both, and the source code for SQ3_4_LB(I believe).


Extract the entire "test program" folder somewhere, double-click the SQ3_4_LB_test.bas file to open it in LB, and try to run it.

Does this run successfully?

It does for me.


If it does for you as well, then there's a problem in your code that's preventing it from loading the SQlite DLL properly.

If this also doesn't run, then there may be issues in your Windows environment that's preventing proper DLL linking.
User IP Logged

"Do you believe in destiny?" - Pyrrha Nikos, RWBY
"With what wish will your Soul Gem shine?" - Kyubey, Puella Magi Madoka Magica
Cor
New Member
Image


member is offline

Avatar




PM

Gender: Male
Posts: 27
xx Re: Eye On SQL
« Reply #44 on: Oct 23rd, 2017, 08:45am »

clausnilsson,

I had the same thing happen to me.

I had played with ionsql for several hours yesterday. Restarted LB several times during that time and it came up with ionsql everytime. When playtime was over I saved the .bas file in the subdir I had been working in all the time (contains both necessary dlls). Then I 'saved as' to another (backup) subdir.

This morning I had your situation. Couldn't get past not loading sqlite. I turned on the default variables and noticed that the CommandLine$ did not have the correct value. Then I checked where my ionsql was loading from. That was from the backup dir. I reloaded ionsql from the save subdir and everything was ok. Perhaps you are loading your ionsql from a non-save directory.

This might just be an error in LB.

Hope this helps you.
User IP Logged

Pages: 1 2 3 4  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