• Welcome, Guest. Please login.
 
September 22, 2019, 05:27:13 am

News:

Welcome to the SQLitening support forums!


Why does this work in SQLite but not SQLitening?

Started by JoeByrne, September 15, 2008, 03:27:10 pm

Previous topic - Next topic

JoeByrne

September 15, 2008, 03:27:10 pm Last Edit: September 15, 2008, 04:03:43 pm by JoeByrne
I can issue the following select command in SQLite3.exe (the command line utility) and get the results I'm looking for, but in SQLitening, it throws an error -13.  Since the error is <0, I'm assuming that the issue is not with SQLite, but within the implementation of slSEL?

     SQL$ = "SELECT " & _
            "   Families.FamilyID," & _
            "   Members.FamilyID," & _
            "   Families.FamilyName," & _
            "   Members.FirstName," & _
            "   Members.MemberType," & _
            "   Members.EMail," & _
            "   Members.Phone," & _
            "   Families.Home_Phone" & _
            " FROM" & _
            "  Families" & _
            "  INNER JOIN Members ON (Families.FamilyID=Members.FamilyID)" & _
            " ORDER BY" & _
            "   Families.FamilyName," & _
            "   Members.MemberType"
     
     e& = slSEL(SQL$,%rSet1)


I am 110% sure all of the table and column names are typed correctly so I'm at a loss as to why this doesn't work.  I'm using the last v5 release.

The error states "Invalid column name or number.  So I exported the columns using SQLitening's slGetTableColumnNames  just to match what it sees with the command above.  The results are:

---Families---
FamilyID
FamilyName
LoginName
PW
Home_Phone
Anniversary
Photo
ShowInDir
IsActive
AgreeTOS
IsApproved
CreateDate
ChangeDate

---Members---
FamilyID
MemberID
MemberType
FirstName
DOB
PhType
Phone
EMail
AFCMember
Baptised
AcctStatus
Photo


Using the following code:
=================================
s$ = slGetTableColumnNames("families")
REPLACE $NUL WITH $CRLF IN s$
f& = FREEFILE
OPEN "columns.txt" FOR APPEND AS #f&
PRINT #f&, "": PRINT #f&, "---Families---": PRINT #f&, ""
PRINT #f&, s$
s$ = slGetTableColumnNames("members")
REPLACE $NUL WITH $CRLF IN s$     
PRINT #f&, "": PRINT #f&, "---Members---": PRINT #f&, ""
PRINT #f&, s$
CLOSE #f&

So it really seems like a bug in SQLitening to me. 

Fred Meier

You have hit on a deficiency (my opinion) in SQLite3.

You are requesting FamilyID twice.  Qualifying if with the table name
makes SQLite3 happy but presents a problem for SQLitening as follows. 

SQLitening asks SQLite for the column names after the Select statement is
prepared.  SQLite3 only retuns the unqualified names(no table qualifier),
therefore returning FamilyID twice.  SQLitening must raise an error
because later when you ask for the value of FamilyID using slFN it would
not know which one to return. 


The solution is to assign a different column name in the Select statement as follows:
Note that the "as" is optional.


     SQL$ = "SELECT " & _
            "   Families.FamilyID as WhatEverYouWant_1," & _
            "   Members.FamilyID as WhatEverYouWant_2," & _
            "   Families.FamilyName," & _
            "   Members.FirstName," & _
            "   Members.MemberType," & _
            "   Members.EMail," & _
            "   Members.Phone," & _
            "   Families.Home_Phone" & _
            " FROM" & _
            "  Families" & _
            "  INNER JOIN Members ON (Families.FamilyID=Members.FamilyID)" & _
            " ORDER BY" & _
            "   Families.FamilyName," & _
            "   Members.MemberType"



So, if you get error -13 on a Select you probable need to assign unique column names.

JoeByrne

hummm.....

Ok.  Can you expand on this a bit more:

Families.FamilyID as WhatEverYouWant_1," & _
Members.FamilyID as WhatEverYouWant_2,"

What is "whateveryouwant_?"   ?????

JoeByrne

September 15, 2008, 04:12:32 pm #3 Last Edit: September 15, 2008, 04:35:36 pm by JoeByrne
Quote
SQLite3 only retuns the unqualified names(no table qualifier),
therefore returning FamilyID twice.


I get the idea, but there must be a way to get SQLite to return the table qualifiers.  I say this because using SQLMasetro, I get the proper results as well.  So somehow SQLMasetro and SQLIte3 must some how be able to identify the table qualifier, no?

In fact, if I send the command as:  slEXE(SQL$,"E") it works just fine too..... I'm so confused :(

I could be wrong, but isn't it pretty common to use the same field name across multiple tables when designing a relational system?  It seems to me that this type of select will be very common.

Fred Meier

I will check all the latest SQLite3 API's for returning the qualifying
table name.  Something may have been added since I coded the slSel many
moons ago.  Will let you know.  If there is a way to get the table name
then will change in next release. 

Regardless, can not get read of error -13 on slSel cause you can ask for
the "real" same column more than once.  SQLite3 will handle just fine but
SQLitening will have to raise error.  But that is probable a true error.


JoeByrne

Thanks Fred.

I decided to work around this, so I changed the column name Member.FamilyID to Member.Family_ID

This should be a properly different column name, right?  (FamilyID and Family_ID)??

I still get the same error though.  I even blew the database away and recreated it.

It could be I'm missing something (again :D).  What I'm doing is keeping a 'Rolodex' database.  The 'Families" table has the general information about a family.  The "Members" table contains the specifics of each family member.

In order to tie them together, I use an auto-increment value in Families and then store that as part of the key in the Members table.  In my mind, these two columns need to have the same column name, but maybe they don't.

In any case, getting a 'subset' of the two tables requires the 'JOIN', correct?

Is there a better, different way to get "some from table A" and "some from table B" so long as there is a common column value between the two?

Fred Meier

Your solution sounds right to me.  I would not change the column name of one of the ID's.

Post or Email me the two Create statements you used to create the two
tables and your Select statement so I can run here and try to determine
what is happening. 

JoeByrne

September 15, 2008, 05:33:54 pm #7 Last Edit: September 15, 2008, 06:17:24 pm by JoeByrne
Thanks Fred.

If you want to drop me an email at:  Joe@ccSolutions.net I'll send you a zip with the all the code and data I am using to convert the old Tsunami db to the SQL db.  That's probably the best way to look at this one.

Added: It seems like the problem might be in the fact that FamilyID is part of the Key in the second table.  When I changed the code to read first from Table 1 (Family) and then try to get the corresponding data in Table 2 (Members) using "WHERE FamilyID=" and the value from Table 1, SQLitening still puked.  I couldn't get anything to work until I took FamilyID out of Table 2 (members) AS A KEY and made it just another column.  So now I can loop through Table 1 (Family), get the FamilyID value, and read from Table 2 (members) WHERE FamilyID is the value obtained from Table 1 (Family).