Everything else that doesn't fall into one of the other PB categories.
swhite
Enthusiast
Posts: 727 Joined: Thu May 21, 2009 6:56 pm
Post
by swhite » Thu Oct 11, 2018 4:14 pm
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
Posts: 34 Joined: Sun Nov 23, 2014 1:18 pm
Post
by fabulouspaul » Fri Oct 12, 2018 11:41 am
...you could use the SQL cast()-function in your queries to put a certain column into the data type your PB codes expects...
mk-soft
Always Here
Posts: 5398 Joined: Fri May 12, 2006 6:51 pm
Location: Germany
Post
by mk-soft » Fri Oct 12, 2018 7:15 pm
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)
swhite
Enthusiast
Posts: 727 Joined: Thu May 21, 2009 6:56 pm
Post
by swhite » Sat Oct 13, 2018 2:53 pm
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
mk-soft
Always Here
Posts: 5398 Joined: Fri May 12, 2006 6:51 pm
Location: Germany
Post
by mk-soft » Sat Oct 13, 2018 4:30 pm
I'll write some new functions to convert the types. The next part is almost finished
mk-soft
Always Here
Posts: 5398 Joined: Fri May 12, 2006 6:51 pm
Location: Germany
Post
by mk-soft » Sat Oct 13, 2018 5:03 pm
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]