[Implemented] GetDatabaseNumber()

[USCode]

Implemented as DatabaseColumIndex()

A function like GetDatabaseNumber(#Database, column name) might be useful;

Then you could get database columns using the column's "name" as opposed to it's number. Maybe something like this:

With myDBStructureVar
\FIELD1 = GetDatabaseString(#Database, GetDatabaseNumber(#Database, "FIELD1"))
\OTHERFIELD = GetDatabaseString(#Database, GetDatabaseNumber(#Database, "OTHERFIELD"))
...

Probably not the most efficient way to do things but later if you added a NEW field to your DB table somewhere other than the bottom then you don't have to renumber the 2nd parameter to all your GetDatabaseString() calls ... like you would in the following example if you added a column to your table between FIELD1 and OTHERFIELD:

With myDBStructureVar
\FIELD1 = GetDatabaseString(#Database, 1)
\OTHERFIELD = GetDatabaseString(#Database, 2)
...

[IdeasVacuum]

Hi USCode
For now, Instead of using hard-coded numbers in the function, define the column names as constants, then you have only one area of code to modify and the code body is a bit easier to read too.

Code: Select all

;Original
#FIELD1     = 1
#OTHERFIELD = 2

\FIELD1 = GetDatabaseString(#Database, #FIELD1)
\OTHERFIELD = GetDatabaseString(#Database, #OTHERFIELD)

;Edited
#FIELD1     = 1
#NEW FIELD  = 2
#OTHERFIELD = 3

\FIELD1 = GetDatabaseString(#Database, #FIELD1)
\OTHERFIELD = GetDatabaseString(#Database, #OTHERFIELD)

[USCode]

Thanks IdeasVacuum, I appreciate the feedback.
However you did give me an idea for something more flexible and self-updating, albeit a bit slower, that I'm going to try out ...

[Kiffi]

i have build a little Helper-Proc to get the values by passing the fieldname:

Code: Select all

Procedure.s GetDatabaseStringByFieldname(DB, Fieldname.s)
  Protected Counter
  For Counter = 0 To DatabaseColumns(DB) - 1
    If LCase(DatabaseColumnName(DB, Counter)) = LCase(Fieldname)
      ProcedureReturn GetDatabaseString(DB, Counter)
    EndIf
  Next
  Debug Fieldname.s + " not found"
EndProcedure

myFieldValue.s = GetDatabaseStringByFieldname(myDB, "myFieldname")

so i do not take care of the order of fields in my SQL-Query.

Greetings ... Kiffi

[IdeasVacuum]

That is top-notch Kiffi

[swhite]

In order to make this more efficient it would be better if this could be done once when the table is opened and the results stored in a MAP. Here is a sample I tried using ODBC. I think I would create a function that opens the table and then automatically loads the fields so they could be used to retrieve the values.

Code: Select all

If UseODBCDatabase()
   If OpenDatabase(1,"AMProAdv","","")
      If DatabaseQuery(1,"Select * From AMSched")
         NewMap AMSched.i()
         For ln=0 To DatabaseColumns(1)
            Debug DatabaseColumnName(1,ln)
            AddMapElement(AMSched(),DatabaseColumnName(1,ln))
            AMSched() = ln
         Next
         ForEach AMSched()
            Debug MapKey(AMSched())
            Debug AMSched()
         Next 
         While NextDatabaseRow(1)
            Debug GetDatabaseString(1,AMSched("DATE"))+" "+GetDatabaseString(1,AMSched("TIME1"))
         Wend
         FinishDatabaseQuery(1)
      EndIf
      CloseDatabase(1)
   EndIf
EndIf

[Kiffi]

In order to make this more efficient it would be better if this could be done once when the table is opened and the results stored in a MAP.

good solution! Thanks!

Greetings ... Kiffi

[Fred]

Just for the record, the following lines:

Code: Select all

AddMapElement(AMSched(),DatabaseColumnName(1,ln))
AMSched() = ln

can be written

Code: Select all

AMSched(DatabaseColumnName(1,ln)) = ln

AddMapElement() is almost never needed, unless you do very specific stuff.

[swhite]

Thanks I did not know that.

Simon