Calling a Variable in an SQLite Select command

[Lonnie]

Hi everyone,

Thanks in advance for your help! A relative newbee here building an app for my senior softball league. I have an SQLite database with 4 tables I want to link with relationships and issue filter statements(plus a few other tables): orgs(organizations), users, events and teams. The first card of the app is a login page. It connects to the database and your access level is based on login info. The 2nd card contains a data grid with a list of organizations. You hilite a line and click on a Select button which stores the org name and ord_id to global mem vars. The name of the org is then inserted on all cards that follow in the headers and the command "go card crdEvents", the 3rd card, is issued. This part works fine.

The Events card contains another data grid with a list of events which are contained in the events table, events for all organizations, separated by the org_id field.

I want to use an SQL select command to populate the data grid, dgEvents, and filter the events by the org_id, stored in the gOrd_id mem var. I tried, among other things:

local tSQL
function getSQL pTable
switch pTable
case "events"
put "SELECT id, orgs_id, year, eventno, eventname, startdate, enddate" && \
"FROM events WHERE orgs_id = gOrg_Id" && \
"ORDER BY eventno ASC" into tSQL

I get the error "Database Error: no such column: gOrg_Id

Then on the next card, crdTeams, I want to populate a data grid with a list from the teams table filter by the org_id and event_id. So I need a 2 condition WHERE clause, something like:

case "teams"
put "SELECT id, orgs_id, events_id, teamname, manager, phone, email" && \
"FROM teams WHERE 'orgs_id' = gOrg_Id AND events_id = gEvents_Id" && \
"ORDER BY teamname ASC" into tSQL

Can someone help with the proper syntax or another approach to this? I'd be most grateful!

Thanks,

Lonnie

[Lonnie]

I got it ... the double filter:

put "SELECT id, orgs_id, team_id, event_id, eventno, set, teamno, team, division, bracket," && \
"pool. rank, wins, lossed, tie, totdiff, oppscore, totscore" && \
"FROM tteams WHERE orgs_id = " && gOrgs_Id && " AND WHERE events_id = " && gEvents_Id && \
"ORDER BY eventno ASC, set ASC, teamno ASC" into tSQL

I can't tell you how many variations I tried. It looks so simple once you get it!!

Thanks,

Lonnie

[Klaus]

Hi Lonnie,

yes, you passed the strings gWhatever_Id etc. and not their values.

Constructiong strings for database access is a bit tricky as you have experienced.
I always do a final -> answer tSQL during development to check that everything is correct.


Best

Klaus

[Lonnie]

I must fess up ... Klaus you are absolutely correct my code with the 2 "WHERE"'s didn't work!!

Here's the code that actually works ...

put "SELECT id, orgs_id, teams_id, events_id, sets_id, trn_id," && \
"eventno, setno, teamno, teamname, division, teamid, bracket," && \
"pool, rank, wins, losses, tie, totdiff, oppscore, totscore" && \
"FROM tteams WHERE orgs_id = " && gOrgs_Id && " AND events_id = " && gEvents_Id && \
"ORDER BY eventno ASC, setno ASC, teamno ASC" into tSQL

I fought this for some time with another issue. One of my field names in most of the table I use is "set". I'm not sure if this is a forbidden field name but once I changed this to "setno" it seems to have worked.

I'm having no luck getting this to load into a data grid so I'll submit that as another issue.

Thanks Everyone