Returning Numeric Values from a Database Column

Everything else that doesn't fall into one of the other PB categories.
swhite
Enthusiast
Enthusiast
Posts: 726
Joined: Thu May 21, 2009 6:56 pm

Returning Numeric Values from a Database Column

Post by swhite »

Hi

Purebasic has a number of functions for returning numeric values from a database (i.e GetDatabaseLong(), GetatabaseDouble() etc.). However this is not very convenient when the table structure changes from Long to Double for example. Now the Purebasic code has to be modified to handle the different type. So I was wondering if there was a better way to handle this. It would be useful if you could return one of several numeric types from a procedure then you could:

Code: Select all

Procedure GetColumnValue(#Database,2)
    Select DatabaseColumnType(#Database, 2) 
   Case #PB_Database_Long  
       ProcedureReturn GetDatabaseLong(#Database,2)
   Case #PB_Database_Double
       ProcedureReturn GetDatabaseDouble(#Database,2)
   Case #PB_Database_Float
       ProcedureReturn GetDatabaseFloat(#Database,2)
   Case #PB_Database_Quad
       ProcedureReturn GetDatabaseQuad(#Database,2)
   EndSelect
So I am just looking for possible ideas for improving access to database columns.

Thanks,
Simon
Simon White
dCipher Computing
fabulouspaul
User
User
Posts: 34
Joined: Sun Nov 23, 2014 1:18 pm

Re: Returning Numeric Values from a Database Column

Post by fabulouspaul »

...you could use the SQL cast()-function in your queries to put a certain column into the data type your PB codes expects...
User avatar
mk-soft
Always Here
Always Here
Posts: 5335
Joined: Fri May 12, 2006 6:51 pm
Location: Germany

Re: Returning Numeric Values from a Database Column

Post by mk-soft »

Purebasic does not know dynamic variable types like Variant. Therefore I define a user data type together if needed.

Example

Code: Select all

;-TOP

; Comment: UserDataType Any

;--------------------------------------------------------------------

Structure udtAny
  Type.w
  res.w
  StructureUnion
    iVal.i
    lVal.l
    qVal.q
    fltVal.f
    dblVal.d
  EndStructureUnion
  sVal.s
EndStructure

Macro AllocateAny()
  AllocateStructure(udtAny)
EndMacro

Macro FreeAny(Value)
  FreeStructure(Value)
EndMacro

;--------------------------------------------------------------------

Macro IsInteger(Value)
  Bool(Value\Type = #PB_Integer)
EndMacro

Macro IsLong(Value)
  Bool(Value\Type = #PB_Long)
EndMacro

Macro IsQuad(Value)
  Bool(Value\Type = #PB_Quad)
EndMacro

Macro IsFloat(Value)
  Bool(Value\Type = #PB_Float)
EndMacro

Macro IsDouble(Value)
  Bool(Value\Type = #PB_Double)
EndMacro

Macro IsString(Value)
  Bool(Value\Type = #PB_String)
EndMacro

;--------------------------------------------------------------------

Macro SetAnyI(Value)
  Value\Type = #PB_Integer : Value\iVal
EndMacro

Macro SetAnyF(Value)
  Value\Type = #PB_Float : Value\fltVal
EndMacro

Macro SetAnyD(Value)
  Value\Type = #PB_Double : Value\dblVal
EndMacro

Macro SetAnyS(Value)
  Value\Type = #PB_String : Value\sVal
EndMacro

;--------------------------------------------------------------------

Procedure GetColumnValue(Database, Column)
  Protected *result.udtAny = AllocateStructure(udtAny)
  Select DatabaseColumnType(Database, Column) 
    Case #PB_Database_Long
      *result\Type = #PB_Long
      *result\lVal = GetDatabaseLong(Database, Column)
    Case #PB_Database_Double
      *result\Type = #PB_Double
      *result\dblVal = GetDatabaseDouble(Database, Column)
    Case #PB_Database_Float
      *result\Type = #PB_Float
      *result\fltVal = GetDatabaseFloat(Database, Column)
    Case #PB_Database_Quad
      *result\Type = #PB_Quad
      *result\qVal = GetDatabaseQuad(Database, Column)
    Case #PB_Database_String
      *result\Type = #PB_String
      *result\sVal = GetDatabaseString(Database, Column)
  EndSelect
  ProcedureReturn *result
EndProcedure

Define *var1.udtAny

*var1 = GetColumnValue(0,2)
If IsString(*var1)
  Debug *var1\sVal
EndIf

FreeStructure(*var1)
My Projects ThreadToGUI / OOP-BaseClass / EventDesigner V3
PB v3.30 / v5.75 - OS Mac Mini OSX 10.xx - VM Window Pro / Linux Ubuntu
Downloads on my Webspace / OneDrive
swhite
Enthusiast
Enthusiast
Posts: 726
Joined: Thu May 21, 2009 6:56 pm

Re: Returning Numeric Values from a Database Column

Post by swhite »

Hi Mk-Soft

I am doing something very similar to what you suggest. The issue is that I would like to get rid of the IsString() or IsDouble() etc. as this complicates the use of the columns. If I want to multiple two numeric values then I first have to determine the type of both columns before I can perform the operation which makes the code very messy.

Code: Select all

    If IsDouble(*Var1) And IsDouble(*Var2)
       result =  *Var1\dVal * *Var2\dVal
    ElseIf IsDouble(*Var1) And IsLong(*Var2)
       result =  *Var1\dVal * *Var2\lVal

    ......   
In my current case I am doing the following using a Maps and a structure to avoid the If construct above.

Code: Select all

     MyTable("column1")\ValueD * MyTable("column2")\ValueL
Simon White
dCipher Computing
User avatar
mk-soft
Always Here
Always Here
Posts: 5335
Joined: Fri May 12, 2006 6:51 pm
Location: Germany

Re: Returning Numeric Values from a Database Column

Post by mk-soft »

I'll write some new functions to convert the types. The next part is almost finished
My Projects ThreadToGUI / OOP-BaseClass / EventDesigner V3
PB v3.30 / v5.75 - OS Mac Mini OSX 10.xx - VM Window Pro / Linux Ubuntu
Downloads on my Webspace / OneDrive
User avatar
mk-soft
Always Here
Always Here
Posts: 5335
Joined: Fri May 12, 2006 6:51 pm
Location: Germany

Re: Returning Numeric Values from a Database Column

Post by mk-soft »

Update for dynamic vars

Update v1.03

Code: Select all

;-TOP

; Comment: UserDataType Any
; Author : mk-soft
; Version: v1.03
; Date   : 13.10.2018

; --------------------------------------------------------------------

EnableExplicit

; --------------------------------------------------------------------

;- Constant

#PB_PVData = 101

; --------------------------------------------------------------------
;- Structure

Structure udtAny
  Type.w
  res.w
  StructureUnion
    iVal.i
    lVal.l
    qVal.q
    fltVal.f
    dblVal.d
    *pvData.pdata
  EndStructureUnion
  sVal.s
EndStructure

;--------------------------------------------------------------------

;- Allocate

Macro AllocateAny()
  AllocateStructure(udtAny)
EndMacro

Macro FreeAny(Var)
  FreeStructure(Var)
EndMacro

;--------------------------------------------------------------------

;- Check Type

Macro IsInteger(Var)
  Bool(Var\Type = #PB_Integer)
EndMacro

Macro IsLong(Var)
  Bool(Var\Type = #PB_Long)
EndMacro

Macro IsQuad(Var)
  Bool(Var\Type = #PB_Quad)
EndMacro

Macro IsFloat(Var)
  Bool(Var\Type = #PB_Float)
EndMacro

Macro IsDouble(Var)
  Bool(Var\Type = #PB_Double)
EndMacro

Macro IsString(Var)
  Bool(Var\Type = #PB_String)
EndMacro

Macro IsData(Var)
  Bool(Var\Type = #PB_PVData)
EndMacro

;--------------------------------------------------------------------

;- Define value and type

Macro SetVarI(Var, Value = 0)
  Var\Type = #PB_Integer : Var\sVal = #Null$ : Var\iVal = Value
EndMacro

Macro SetVarL(Var, Value = 0)
  Var\Type = #PB_Long : Var\sVal = #Null$ : Var\lVal = Value
EndMacro

Macro SetVarQ(Var, Value = 0)
  Var\Type = #PB_Quad : Var\sVal = #Null$ : Var\qVal = Value
EndMacro

Macro SetVarF(Var, Value = 0.0)
  Var\Type = #PB_Float : Var\sVal = #Null$ : Var\fltVal = Value
EndMacro

Macro SetVarD(Var, Value = 0.0)
  Var\Type = #PB_Double : Var\sVal = #Null$ : Var\dblVal = Value
EndMacro

Macro SetVarS(Var, Value = #Null$)
  Var\Type = #PB_String : Var\sVal = Value
EndMacro

Macro SetVarP(Var, Value = 0)
  Var\Type = #PB_PVData : Var\sVal = #Null$ : Var\pvData = Value
EndMacro

;--------------------------------------------------------------------

;- Assign value and type

Macro _VarI(Var)
  Var\Type = #PB_Integer : Var\sVal = #Null$ : Var\iVal
EndMacro

Macro _VarL(Var)
  Var\Type = #PB_Long : Var\sVal = #Null$ : Var\lVal
EndMacro

Macro _VarQ(Var)
  Var\Type = #PB_Quad : Var\sVal = #Null$ : Var\qVal
EndMacro

Macro _VarF(Var)
  Var\Type = #PB_Float : Var\sVal = #Null$ : Var\fltVal
EndMacro

Macro _VarD(Var)
  Var\Type = #PB_Double : Var\sVal = #Null$ : Var\dblVal
EndMacro

Macro _VarS(Var)
  Var\Type = #PB_String : Var\sVal
EndMacro

Macro _VarP(Var)
  Var\Type = #PB_PVData : Var\sVal = #Null$ : Var\pvData
EndMacro

;--------------------------------------------------------------------

;- Change type of value. Can be inplace.

Procedure ChangeVarType(*Desc.udtAny, *Source.udtAny, Type)
  
  Select Type
    Case #PB_Integer
      Select *Source\Type
        Case #PB_String
          *Desc\iVal = Val(*Source\sVal)
        Case #PB_Integer
          *Desc\iVal = *Source\iVal
        Case #PB_Long
          *Desc\iVal = *Source\lVal
        Case #PB_Quad
          *Desc\iVal = *Source\qVal
        Case #PB_Float
          *Desc\iVal = *Source\fltVal
        Case #PB_Double
          *Desc\iVal = *Source\dblVal
        Default
          *desc\qVal = 0
      EndSelect
      *Desc\Type = #PB_Integer
      *Desc\sVal = #Null$
      
    Case #PB_Long
      Select *Source\Type
        Case #PB_String
          *Desc\lVal = Val(*Source\sVal)
        Case #PB_Integer
          *Desc\lVal = *Source\iVal
        Case #PB_Long
          *Desc\lVal = *Source\lVal
        Case #PB_Quad
          *Desc\lVal = *Source\qVal
        Case #PB_Float
          *Desc\lVal = *Source\fltVal
        Case #PB_Double
          *Desc\lVal = *Source\dblVal
        Default
          *desc\qVal = 0
      EndSelect
      *Desc\Type = #PB_Long
      *Desc\sVal = #Null$
      
    Case #PB_Quad
      Select *Source\Type
        Case #PB_String
          *Desc\qVal = Val(*Source\sVal)
        Case #PB_Integer
          *Desc\qVal = *Source\iVal
        Case #PB_Long
          *Desc\qVal = *Source\lVal
        Case #PB_Quad
          *Desc\qVal = *Source\qVal
        Case #PB_Float
          *Desc\qVal = *Source\fltVal
        Case #PB_Double
          *Desc\qVal = *Source\dblVal
        Default
          *desc\qVal = 0
      EndSelect
      *Desc\Type = #PB_Quad
      *Desc\sVal = #Null$
      
    Case #PB_Float
      Select *Source\Type
        Case #PB_String
          *Desc\fltVal = ValF(*Source\sVal)
        Case #PB_Integer
          *Desc\fltVal = *Source\iVal
        Case #PB_Long
          *Desc\fltVal = *Source\lVal
        Case #PB_Quad
          *Desc\fltVal = *Source\qVal
        Case #PB_Float
          *Desc\fltVal = *Source\fltVal
        Case #PB_Double
          *Desc\fltVal = *Source\dblVal
        Default
          *Desc\fltVal = 0.0
      EndSelect
      *Desc\Type = #PB_Float
      *Desc\sVal = #Null$
      
    Case #PB_Double
      Select *Source\Type
        Case #PB_String
          *Desc\dblVal = ValD(*Source\sVal)
        Case #PB_Integer
          *Desc\dblVal = *Source\iVal
        Case #PB_Long
          *Desc\dblVal = *Source\lVal
        Case #PB_Quad
          *Desc\dblVal = *Source\qVal
        Case #PB_Float
          *Desc\dblVal = *Source\fltVal
        Case #PB_Double
          *Desc\dblVal = *Source\dblVal
        Default
          *Desc\dblVal = 0.0
      EndSelect
      *Desc\Type = #PB_Double
      *Desc\sVal = #Null$
      
    Case #PB_String
      Select *Source\Type
        Case #PB_String
          *Desc\sVal = *Source\sVal
        Case #PB_Integer
          *Desc\sVal = Str(*Source\iVal)
        Case #PB_Long
          *Desc\sVal = Str(*Source\lVal)
        Case #PB_Quad
          *Desc\sVal = Str(*Source\qVal)
        Case #PB_Float
          *Desc\sVal = StrF(*Source\fltVal)
        Case #PB_Double
          *Desc\sVal = StrD(*Source\dblVal)
        Default
          *Desc\sVal = #Null$
      EndSelect
      *Desc\Type = #PB_String
      
  EndSelect
  
  ProcedureReturn 1
EndProcedure

;--------------------------------------------------------------------

;- Get value as type

Procedure.i GetVarI(*Var.udtAny)
  Protected desc.udtAny
  ChangeVarType(desc, *Var, #PB_Integer)
  ProcedureReturn desc\iVal
EndProcedure

;--------------------------------------------------------------------

Procedure.l GetVarL(*Var.udtAny)
  Protected desc.udtAny
  ChangeVarType(desc, *Var, #PB_Long)
  ProcedureReturn desc\lVal
EndProcedure

;--------------------------------------------------------------------

Procedure.q GetVarQ(*Var.udtAny)
  Protected desc.udtAny
  ChangeVarType(desc, *Var, #PB_Quad)
  ProcedureReturn desc\qVal
EndProcedure

;--------------------------------------------------------------------

Procedure.f GetVarF(*Var.udtAny)
  Protected desc.udtAny
  ChangeVarType(desc, *Var, #PB_Float)
  ProcedureReturn desc\fltVal
EndProcedure

;--------------------------------------------------------------------

Procedure.d GetVarD(*Var.udtAny)
  Protected desc.udtAny
  ChangeVarType(desc, *Var, #PB_Double)
  ProcedureReturn desc\dblVal
EndProcedure

;--------------------------------------------------------------------

Procedure.s GetVarS(*Var.udtAny)
  Protected desc.udtAny
  ChangeVarType(desc, *Var, #PB_String)
  ProcedureReturn desc\sVal
EndProcedure

;--------------------------------------------------------------------

;-Test

CompilerIf #PB_Compiler_IsMainFile
  
  
  Define.udtAny var1, var2, var3, result
  
  SetVarL(var1, 20)
  SetVarD(var2, 98.0)
  SetVarS(var3, "0.5")
  Define r1.d
  
  r1 = GetVarD(var1) * GetVarD(var2) * GetVarD(var3)
  Debug r1
  
  _VarD(result) = GetVarD(var1) * 2.0
  Debug result\dblVal
  
  ; Change variable type inplace
  ChangeVarType(result, result, #PB_String)
  Debug result\sVal
  
CompilerEndIf
[/size]
My Projects ThreadToGUI / OOP-BaseClass / EventDesigner V3
PB v3.30 / v5.75 - OS Mac Mini OSX 10.xx - VM Window Pro / Linux Ubuntu
Downloads on my Webspace / OneDrive
Post Reply