It is currently Sat Dec 15, 2018 7:47 am

All times are UTC + 1 hour




Post new topic Reply to topic  [ 6 posts ] 
Author Message
 Post subject: Returning Numeric Values from a Database Column
PostPosted: Thu Oct 11, 2018 4:14 pm 
Offline
Enthusiast
Enthusiast

Joined: Thu May 21, 2009 6:56 pm
Posts: 494
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:
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


Top
 Profile  
Reply with quote  
 Post subject: Re: Returning Numeric Values from a Database Column
PostPosted: Fri Oct 12, 2018 11:41 am 
Offline
User
User

Joined: Sun Nov 23, 2014 1:18 pm
Posts: 28
...you could use the SQL cast()-function in your queries to put a certain column into the data type your PB codes expects...


Top
 Profile  
Reply with quote  
 Post subject: Re: Returning Numeric Values from a Database Column
PostPosted: Fri Oct 12, 2018 7:15 pm 
Offline
Addict
Addict
User avatar

Joined: Fri May 12, 2006 6:51 pm
Posts: 1476
Location: Germany
Purebasic does not know dynamic variable types like Variant. Therefore I define a user data type together if needed.

Example
Code:
;-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 OOP-BaseClass / OOP-BaseClassDispatch / Event-Designer /
PB v3.30 / v5.60 - OS Mac Mini OSX 10.xx - VM Window Pro / Linux Ubuntu
Downloads on my Webspace


Top
 Profile  
Reply with quote  
 Post subject: Re: Returning Numeric Values from a Database Column
PostPosted: Sat Oct 13, 2018 2:53 pm 
Offline
Enthusiast
Enthusiast

Joined: Thu May 21, 2009 6:56 pm
Posts: 494
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:
    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:
     MyTable("column1")\ValueD * MyTable("column2")\ValueL


Top
 Profile  
Reply with quote  
 Post subject: Re: Returning Numeric Values from a Database Column
PostPosted: Sat Oct 13, 2018 4:30 pm 
Offline
Addict
Addict
User avatar

Joined: Fri May 12, 2006 6:51 pm
Posts: 1476
Location: Germany
I'll write some new functions to convert the types. The next part is almost finished

_________________
My Projects OOP-BaseClass / OOP-BaseClassDispatch / Event-Designer /
PB v3.30 / v5.60 - OS Mac Mini OSX 10.xx - VM Window Pro / Linux Ubuntu
Downloads on my Webspace


Top
 Profile  
Reply with quote  
 Post subject: Re: Returning Numeric Values from a Database Column
PostPosted: Sat Oct 13, 2018 5:03 pm 
Offline
Addict
Addict
User avatar

Joined: Fri May 12, 2006 6:51 pm
Posts: 1476
Location: Germany
Update for dynamic vars

Update v1.03
Code:
;-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

_________________
My Projects OOP-BaseClass / OOP-BaseClassDispatch / Event-Designer /
PB v3.30 / v5.60 - OS Mac Mini OSX 10.xx - VM Window Pro / Linux Ubuntu
Downloads on my Webspace


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 6 posts ] 

All times are UTC + 1 hour


Who is online

Users browsing this forum: No registered users and 4 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  

 


Powered by phpBB © 2008 phpBB Group
subSilver+ theme by Canver Software, sponsor Sanal Modifiye