This is a little program I created to do a SQL SELECT and display the results in a ListView. You enter a full SQL statement in the RichEdit, then click the 'Run SQL'. This uses an ODBC database, to use another Database just change OpenDB subroutine.
Note: If you are doing an aggregate make sure to add an 'AS', otherwise it will show the aggregate function in the header line of the ListView.
Ex: Select first_name, last_name, sum(hours*rate) from employees
Will show 'first_name', 'last_name', 'sum(hours*rate)'
So use: Select first_name, last_name, sum(hours*rate) as salary from employees
Will show 'first_name', 'last_name', 'salary'
Also, "SELECT * from employees" will not work, because I need the field names to populate the ListView.
Any questions, just let me know.
Bill
Thanks for posting.
I deleted the post in the other area.
LarryMc
Thanks, I have never done any thing in Aurora not sure why I put the program there. I am going to put it to a Senior moment.
Hopefully someone can use that program for something. I use it to do a quick select to display the data from work. Thought it could be of help to those learning SQL or those who want to do something fancy with ListView's or maybe working with text.
Bill
Bill,
If / whenever you get some time, maybe you'd like to consider posting some basic tutorials in that section on databases / SQL.
I for one would be very interested.
Just an idea.
Andy.
:)
The problem is that the basics should be the same between all databases, but every variation is a little different. There are things you can do in ACCESS that don't work anywhere else.
Examples:
Show 3 digit integer with leading 0's in ACCESS - FORMAT(grn,"000") in SQL SERVER - RIGHT('000' + CAST(grn AS varchar),3).
ACCESS - iif(diff='==',' ','(C)'), SQL SERVER - CASE WHEN diff='==' THEN ' ' ELSE '(C)' END.
ACCESS is non standard SQL, whereas SQL SERVER is pretty much standard.
If any one needs help with SQL, I am always willing to help. I have created some very wild queries over the years.
One of the odd things, most versions of SQL have a way to get the tables, table fields, the field type and lengths, but ACCESS only seems to allow you to pull up the table names, at least on the 2007 version. Not sure about the newest version, but I don't have access to that version.
Bill
I have the CodinMonkeys database. I was going to try a make an app in html where people browse the pages much like when it was on line. The access and html was eating my lunch.
Let me know what I can do to help. I have done lots of odd stuff in ACCESS and its version on SQL. I am guessing mostly what you need is selects.
Bill
I meant5 CodingMonkeys (which I own) and not CoderCreations. I'm correcting my post.
My main problem was extracting the info (well not so much that) but formatting it so that it looked like you were looking at the old offline Pyxia database.
What DB format does the Pyxia Database use?
Are there any of those goofy BLOB fields in the file? I have never figured out how to extract them. Luckily I mostly deal with standard text fields, nothing over 255 chars.
I was planning on fooling with SQLLite to pull info out of the Ditto DB (clip board utility that I use). I have gone in and shorted the 'short description' to make it more useful (made better descriptions, since some of the clips I store are about the same for the first 80 chars).
Bill
I think they use dSQLlite. But the real magic of what they did is the formatting of the output as far as I'm concerned.
You can browse it and you'd think you were browsing the forum.
Do you have a small sample that I might be able to look at?
Bill
I got so fed up with it I chunk all the code a good while back. LOL
How about the Database?
Bill