DatabaseColumnType in Mysql ( MariaDb)
Posted: Fri Jan 30, 2026 11:58 am
I'm using an auto-converter to JSON, with DatabaseColumnType(), but there are some columns from my client that don't match the type:
code pb:
Code: Select all
CREATE TABLE `mytipus` (
`tip_bigint` BIGINT NULL DEFAULT NULL,
`tip_tinyiny` TINYINT NULL DEFAULT NULL,
`tip_smallint` SMALLINT NULL DEFAULT NULL,
`tip_mediumint` MEDIUMINT NULL DEFAULT NULL,
`tip_int` INT NULL DEFAULT NULL,
`tip_decimal` DECIMAL(10,2) NULL DEFAULT NULL,
`tip_float` FLOAT NULL DEFAULT NULL,
`tip_double` DOUBLE NULL DEFAULT NULL,
`tip_unsignedtinyiny` TINYINT UNSIGNED NULL DEFAULT NULL,
`tip_unsignedsmallint` SMALLINT UNSIGNED NULL DEFAULT NULL,
`tip_unsignedmediumint` MEDIUMINT UNSIGNED NULL DEFAULT NULL,
`tip_unsignedint` INT UNSIGNED NULL DEFAULT NULL,
`tip_unsignedbigint` BIGINT UNSIGNED NULL DEFAULT NULL,
`tip_varchar` VARCHAR(32) NOT NULL,
`tip_text` TEXT NOT NULL,
`tip_char` CHAR(32) NOT NULL,
`tip_date_time` DATETIME NOT NULL,
`tip_date` DATE NOT NULL,
`tip_time` TIME NOT NULL
)
COLLATE='utf8mb4_0900_ai_ci'
ENGINE=InnoDB
;
INSERT INTO `testes`.`mytipus` (`tip_bigint`, `tip_tinyiny`, `tip_smallint`, `tip_mediumint`, `tip_int`, `tip_decimal`, `tip_float`, `tip_double`, `tip_unsignedtinyiny`, `tip_unsignedsmallint`, `tip_unsignedmediumint`, `tip_unsignedint`, `tip_unsignedbigint`, `tip_varchar`, `tip_text`, `tip_char`, `tip_date_time`, `tip_date`, `tip_time`) VALUES ('123456789', '122', '1234', '12345', '123456', '1.23', '12.123', '1.1234', '2', '22', '333', '346767', '987654321', 'AbcDe', 'xxxxxxxxxxxxxx', 'aBc', '2026-01-27 07:10:00', '2026-01-27', '07:10:16');
Code: Select all
;----------------------------------------------------------------------
; MySQL enum_field_types -> PureBasic Enumeration
;----------------------------------------------------------------------
Enumeration 0
#MYSQL_TYPE_DECIMAL
#MYSQL_TYPE_TINY
#MYSQL_TYPE_SHORT
#MYSQL_TYPE_LONG
#MYSQL_TYPE_FLOAT
#MYSQL_TYPE_DOUBLE
#MYSQL_TYPE_NULL
#MYSQL_TYPE_TIMESTAMP
#MYSQL_TYPE_LONGLONG
#MYSQL_TYPE_INT24
#MYSQL_TYPE_DATE
#MYSQL_TYPE_TIME
#MYSQL_TYPE_DATETIME
#MYSQL_TYPE_YEAR
#MYSQL_TYPE_NEWDATE
#MYSQL_TYPE_VARCHAR
#MYSQL_TYPE_BIT
#MYSQL_TYPE_TIMESTAMP2
#MYSQL_TYPE_DATETIME2
#MYSQL_TYPE_TIME2
#MYSQL_TYPE_TYPED_ARRAY
EndEnumeration
#MYSQL_TYPE_INVALID = 243
#MYSQL_TYPE_BOOL = 244
#MYSQL_TYPE_JSON = 245
#MYSQL_TYPE_NEWDECIMAL = 246
#MYSQL_TYPE_ENUM = 247
#MYSQL_TYPE_SET = 248
#MYSQL_TYPE_TINY_BLOB = 249
#MYSQL_TYPE_MEDIUM_BLOB= 250
#MYSQL_TYPE_LONG_BLOB = 251
#MYSQL_TYPE_BLOB = 252
#MYSQL_TYPE_VAR_STRING = 253
#MYSQL_TYPE_STRING = 254
#MYSQL_TYPE_GEOMETRY = 255
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
Enumeration
#tip_bigint
#tip_tinyiny
#tip_smallint
#tip_mediumint
#tip_int
#tip_decimal
#tip_float
#tip_double
#tip_unsignedtinyint
#tip_unsignedsmallint
#tip_unsignedmediumint
#tip_unsignedint
#tip_unsignedbigint
#tip_varchar
#tip_text
#tip_char
#tip_date_time
#tip_date
#tip_time
EndEnumeration
; in linux ubuntu
UseMySQLDatabase("/usr/lib/x86_64-linux-gnu/libmysqlclient.so")
Define MyDb= OpenDatabase(#PB_Any, "host=127.0.0.1 port=3306 dbname='testes'", "user", "pwd",#PB_Database_MySQL)
If MyDb
Debug "*** Connected to MySQL ***"
Define sql$="SELECT "
sql$+"tip_bigint"
sql$+",tip_tinyiny"
sql$+",tip_smallint"
sql$+",tip_mediumint"
sql$+",tip_int"
sql$+",tip_decimal"
sql$+",tip_float"
sql$+",tip_double"
sql$+",tip_unsignedtinyiny"
sql$+",tip_unsignedsmallint"
sql$+",tip_unsignedmediumint"
sql$+",tip_unsignedint"
sql$+",tip_unsignedbigint"
sql$+",tip_varchar"
sql$+",tip_text "
sql$+",tip_char"
sql$+",tip_date_time"
sql$+",tip_date"
sql$+",tip_time FROM mytipus"
If DatabaseQuery(MyDb,sql$) ; Get all the records in the 'mytipus' table
While NextDatabaseRow(MyDb) ; Loop for each records
Debug " > #tip_bigint"
If Bool(DatabaseColumnType(MyDb,#tip_bigint)=#PB_Database_Quad)
Debug "OK #PB_Database_Quad"
Else
Debug "Not #PB_Database_Quad"
EndIf
Debug " > #tip_tinyiny"
If Bool(DatabaseColumnType(MyDb,#tip_tinyiny)=#PB_Database_Long)
Debug "OK #PB_Database_Long"
Else
Debug "Not #PB_Database_Long"
EndIf
Debug " > #tip_smallint"
If Bool(DatabaseColumnType(MyDb,#tip_smallint)=#PB_Database_Long)
Debug "OK #PB_Database_Long"
Else
Debug "Not #PB_Database_Long"
EndIf
Debug " > #tip_mediumint"
If Bool(DatabaseColumnType(MyDb,#tip_mediumint)=#PB_Database_Long)
Debug "OK #PB_Database_Long"
Else
Debug "Not #PB_Database_Long"
EndIf
Debug " > #tip_int"
If Bool(DatabaseColumnType(MyDb,#tip_int)=#PB_Database_Long)
Debug "OK #PB_Database_Long"
Else
Debug "Not #PB_Database_Long"
EndIf
Debug " > #tip_unsignedtinyint"
If Bool(DatabaseColumnType(MyDb,#tip_unsignedtinyint)=#PB_Database_Long)
Debug "OK #PB_Database_Long"
Else
Debug "Not #PB_Database_Long"
EndIf
Debug " > #tip_unsignedsmallint"
If Bool(DatabaseColumnType(MyDb,#tip_unsignedsmallint)=#PB_Database_Long)
Debug "OK #PB_Database_Long"
Else
Debug "Not #PB_Database_Long"
EndIf
Debug " > #tip_unsignedmediumint"
If Bool(DatabaseColumnType(MyDb,#tip_unsignedmediumint)=#PB_Database_Long)
Debug "OK #PB_Database_Long"
Else
Debug "Not #PB_Database_Long"
EndIf
Debug " > #tip_unsignedint"
If Bool(DatabaseColumnType(MyDb,#tip_unsignedint)=#PB_Database_Long)
Debug "OK #PB_Database_Long"
Else
Debug "Not #PB_Database_Long"
EndIf
Debug " > #tip_unsignedbigint"
If Bool(DatabaseColumnType(MyDb,#tip_unsignedbigint)=#PB_Database_Quad)
Debug "OK #PB_Database_Quad"
Else
Debug "Not #PB_Database_Quad"
EndIf
Debug " > #tip_float"
If Bool(DatabaseColumnType(MyDb,#tip_float)=#PB_Database_Float)
Debug "OK #PB_Database_Float"
Else
Debug "Not #PB_Database_Float"
EndIf
Debug " > #tip_double"
If Bool(DatabaseColumnType(MyDb,#tip_double)=#PB_Database_Double)
Debug "OK #PB_Database_Double"
Else
Debug "Not #PB_Database_Double"
EndIf
;
Debug "#tip_varchar"
If Bool(DatabaseColumnType(MyDb,#tip_varchar)=#PB_Database_String)
Debug "OK #PB_Database_String"
Else
Debug "Not #PB_Database_String"
EndIf
Debug "#tip_date_time"
If Bool(DatabaseColumnType(MyDb,#tip_date_time)=#PB_Database_String)
Debug "OK #PB_Database_String"
Else
Debug "Not #PB_Database_String"
EndIf
Debug "#tip_date"
If Bool(DatabaseColumnType(MyDb,#tip_date)=#PB_Database_String)
Debug "OK #PB_Database_String"
Else
Debug "Not #PB_Database_String"
EndIf
Debug "#tip_time"
If Bool(DatabaseColumnType(MyDb,#tip_time)=#PB_Database_String)
Debug "OK #PB_Database_String"
Else
Debug "Not #PB_Database_String"
EndIf
Debug " "
Debug "#########################################################"
Debug "Error: It's coming from a different type, such as #PB_Database_Long"
Debug " > #tip_decimal"
If Bool(DatabaseColumnType(MyDb,#tip_decimal)=#PB_Database_Float)
Debug "OK #PB_Database_Float"
Else
Debug "Not #PB_Database_Float"
EndIf
Debug " > #tip_text"
If Bool(DatabaseColumnType(MyDb,#tip_text)=#PB_Database_String)
Debug "OK #PB_Database_String"
Else
Debug "Not #PB_Database_String"
EndIf
Debug " > #tip_char"
If Bool(DatabaseColumnType(MyDb,#tip_char)=#PB_Database_String)
Debug "Ok #PB_Database_String"
Else
Debug "Not #PB_Database_String"
EndIf
Wend
FinishDatabaseQuery(MyDb)
EndIf
CloseDatabase(MyDb)
Else
Debug "Connection failed: "+DatabaseError()
EndIf