November 29, 2021, 11:44:25 PM

News:

Own IWBasic 2.x ? -----> Get your free upgrade to 3.x now.........


Database Select

Started by billhsln, May 20, 2021, 11:18:47 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

billhsln

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
When all else fails, get a bigger hammer.

LarryMc

Thanks for posting.
I deleted the post in the other area.
LarryMc
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

billhsln

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
When all else fails, get a bigger hammer.

Andy

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.
 :)

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

billhsln

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
When all else fails, get a bigger hammer.

LarryMc

May 22, 2021, 01:00:47 PM #5 Last Edit: May 22, 2021, 07:53:48 PM by LarryMc
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.
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

billhsln

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
When all else fails, get a bigger hammer.

LarryMc

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.
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

billhsln

May 22, 2021, 08:22:55 PM #8 Last Edit: May 22, 2021, 08:26:49 PM by billhsln
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
When all else fails, get a bigger hammer.

LarryMc

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.
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

LarryMc

You can browse it and you'd think you were browsing the forum.
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

billhsln

Do you have a small sample that I might be able to look at?

Bill
When all else fails, get a bigger hammer.

LarryMc

I got so fed up with it I chunk all the code a good while back. LOL
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

billhsln

How about the Database?

Bill
When all else fails, get a bigger hammer.