returned DB fields

[kokenge]

I know this is a very simple problem, but I keep running against it again and again when getting database information.

Don't know if I'm the only one having this problem. If so, how has everyone else solved it?

Currently the only way I know to get data from the database is by extracting each data item using the word$() command. Unfortunately it is extremely limited. You basically have to know the position of every field being returned. Using the wild card SELECT * returns everything and you have to know the position of the fields in the database. If you ever change the database then your word$() commands in all your programs need to be changed.
To get around it you basically have to mention all the fields in the SELECT statement. Even that requires a lot of work, because every time you add a JOIN or COUNT(*) or anything you have to make sure what you SELECT matches exactly what you extract with the word$() command.
Basically you spend more time than you should to do simple Database extractions.
I wrote a simple call routine that I use to get data returned from the sql columnnames$() call. No matter how I change the query or the database it always works. Even the columnnames$() call is a problem because it does not tell you the table it came from. If you use the same table more than once in the sql query the columnnames$() will show the same column name more than once.

To get around it I select the table name as 'tableName.' in the query to distinguish the different tables. Then use the 'tableName.' to place in my subroutine and it then knows the difference between the same names.

So basically I need to know how everyone else solves how to know the difference between the same column names other than what I've done. It's not nice but works.

For example, even vary simple queries cause problems. Take for example a blog I wrote that does exactly the same thing as the blog we use here.
I took some of the complex stuff out of the query to present here.
I need to know :
o The forum and it's information,
o The topic within the forum and it's information
o How many post have been made against the topic "count()"
o The first post "fpost" and it's info and user "fuser"
o The last post "lpost" and it's info and user "luser"
Notice the table names in single quotes to know the difference of like column names.


forumDb$ = "C:\rbp101b2\projects\blog_project\data\blog.db"
sqliteconnect    #sql,  forumDb$  ' Connect to the DB
sql$ = "
SELECT
'forum.',forum.*,
'topic.',topic.*,
'post.',post.*,
'fpost.',fpost.*,
'fuser.',fuser.*,
'lpost.',lpost.*,
'luser.',luser.*,
count(post.topicNum) as numPost
FROM forum

LEFT JOIN topic ON topic.forumNum = forum.forumNum

LEFT JOIN post ON post.topicNum = topic.topicNum

LEFT JOIN post AS fpost
ON fpost.postDate = (SELECT MIN(fp.postDate)
FROM post as fp
WHERE fp.topicNum = topic.topicNum)

LEFT JOIN user AS fuser ON fuser.userNum = fpost.userNum

LEFT JOIN post as lpost
ON lpost.postDate = (SELECT MAX(lp.postDate)
FROM post AS lp
WHERE lp.topicNum = topic.topicNum)

LEFT JOIN user AS luser ON luser.userNum = lpost.userNum

GROUP BY forum.forumNum,post.topicNum
ORDER BY forum.forumSeq,forum.forumNum,topic.topicNum,post.postNum
"

#sql execute(sql$)

colNames$   = #sql columnnames$()
print colNames$
wait


Is there anything in the works for RunBasic to solve this??

Thanks for the help..

[Carl Gundel - admin]

I'm certainly open to suggestions. Do you have some specific ideas for syntax to fix this?

-Carl

Aug 23, 2008 12:36:20 GMT -5 kokenge said:

I know this is a very simple problem, but I keep running against it again and again when getting database information.

Don't know if I'm the only one having this problem. If so, how has everyone else solved it?

Currently the only way I know to get data from the database is by extracting each data item using the word$() command. Unfortunately it is extremely limited. You basically have to know the position of every field being returned. Using the wild card SELECT * returns everything and you have to know the position of the fields in the database. If you ever change the database then your word$() commands in all your programs need to be changed.
To get around it you basically have to mention all the fields in the SELECT statement. Even that requires a lot of work, because every time you add a JOIN or COUNT(*) or anything you have to make sure what you SELECT matches exactly what you extract with the word$() command.
Basically you spend more time than you should to do simple Database extractions.
I wrote a simple call routine that I use to get data returned from the sql columnnames$() call. No matter how I change the query or the database it always works. Even the columnnames$() call is a problem because it does not tell you the table it came from. If you use the same table more than once in the sql query the columnnames$() will show the same column name more than once.

To get around it I select the table name as 'tableName.' in the query to distinguish the different tables. Then use the 'tableName.' to place in my subroutine and it then knows the difference between the same names.

So basically I need to know how everyone else solves how to know the difference between the same column names other than what I've done. It's not nice but works.

For example, even vary simple queries cause problems. Take for example a blog I wrote that does exactly the same thing as the blog we use here.
I took some of the complex stuff out of the query to present here.
I need to know :
o The forum and it's information,
o The topic within the forum and it's information
o How many post have been made against the topic "count()"
o The first post "fpost" and it's info and user "fuser"
o The last post "lpost" and it's info and user "luser"
Notice the table names in single quotes to know the difference of like column names.


forumDb$ = "C:\rbp101b2\projects\blog_project\data\blog.db"
sqliteconnect    #sql,  forumDb$  ' Connect to the DB
sql$ = "
SELECT
'forum.',forum.*,
'topic.',topic.*,
'post.',post.*,
'fpost.',fpost.*,
'fuser.',fuser.*,
'lpost.',lpost.*,
'luser.',luser.*,
count(post.topicNum) as numPost
FROM forum

LEFT JOIN topic ON topic.forumNum = forum.forumNum

LEFT JOIN post ON post.topicNum = topic.topicNum

LEFT JOIN post AS fpost
ON fpost.postDate = (SELECT MIN(fp.postDate)
FROM post as fp
WHERE fp.topicNum = topic.topicNum)

LEFT JOIN user AS fuser ON fuser.userNum = fpost.userNum

LEFT JOIN post as lpost
ON lpost.postDate = (SELECT MAX(lp.postDate)
FROM post AS lp
WHERE lp.topicNum = topic.topicNum)

LEFT JOIN user AS luser ON luser.userNum = lpost.userNum

GROUP BY forum.forumNum,post.topicNum
ORDER BY forum.forumSeq,forum.forumNum,topic.topicNum,post.postNum
"

#sql execute(sql$)

colNames$   = #sql columnnames$()
print colNames$
wait


Is there anything in the works for RunBasic to solve this??

Thanks for the help..

[kokenge]

Wow! Probably a lot of ways to solve this.
I can think of a few, but would be glad to get anything.
---------------------------------------------
Option 1.
Create something similar the columnnames$() that also includes the table name, such as tableFields$()
columnnames$() only returns the column name. So if you had two tables with column name seqNum in table 'a' and 'b' the column names would show seqNum twice as (seqNum, seqNum).
The tableFields$() function would return a.seqNum and b.seqNum.
Now we know what table each field came from when displaying headings.

Then create a extract that is not positional like the word$() function such as tableFieldData$.
Then we could use a.seqNum$ = #sql tableFieldData$('a.seqNum').
Now no matter how we change the sql command, or the database this works. That is if you don't delete the field from the DB or change the spelling of the field.
--------------------------------------
Option 2.
Using the tableFields$() function and giving RB the ability to create field names on the fly.
We could write the following code.
dbTableFields$ = #sql tableFields$() ' this would return table.field names
i = 1
while word$(dbTableFields$,i,",") <> ""
x$ = word$(dbTableFields$,i,",") ' when i = 1 x$ = a.seqNum and when i = 2 x$ = b.seqNum
x$$ = #sql tableFieldData$(x$). ' the $$ turns the value a.seqNum into a variable a.seqNum$
i = i + 1
wend
This is a much simpler system since no matter what you do to the DB or how you change your sql query it always creates the variables for you.
---------------------------------------
The second options also works when getting CSV data from a spread sheet.
Normally the first line of a CSV spread sheet has the column names.
using the similar dynamic x$$ we can dynamically create a insert statement.
-----------------------------------

I'm sure there are better ideas the these.
However these are two that come to mind, but I'll post more options if I come up with something.

Thanks for the help..
Dan

[neal]

I like the idea of a function to get the column values as the current method of using word$ requires that you have a delimiter character which you know will not appear in your data.

Using "select *" is considered bad form in SQL for all the reasons mentioned in the first post. By explicitly listing every column in the select, column aliases can be used to work around any problems with columns of the same name from multiple tables...

Instead of "select a.*, b.* from a, b"

Use "select a.seqNum seqNumA, b.seqNum seqNumB from a, b"

I know it's more typing, but I think listing the column names in the select (as opposed to using *) makes it easier to read and maintain the code since it is obvious which columns in the table are actually being used. But then I'm an old fuddy duddy about those kinds of things....