How to determine a null value?

endo · Post by **endo** » Thu Sep 11, 2008 1:52 pm

How do I determine a database field value is NULL or not?

I use the following code to get column type, but it gives the "column type" not the "value type"? So it gives 0 (zero) for a NULL value but it is not the same thing. (zero has different meaning in my database)

a function like DatabaseFieldType() would be nice,
Any idea?

Code: Select all

While NextDatabaseRow(0)
	For i=0 To DatabaseColumns(0)-1
		t = ""
		Select DatabaseColumnType(0,i)
			Case #PB_Database_Long
				t = Str(GetDatabaseLong(0,i))
			Case #PB_Database_Quad
				t = StrQ(GetDatabaseQuad(0,i))
			Case #PB_Database_Float
				t =  StrF(GetDatabaseFloat(0,i),5)
			Case #PB_Database_Double
				t =  StrD(GetDatabaseFloat(0,i),5)
			Case #PB_Database_String
				t = GetDatabaseString(0,i)
		EndSelect
		WriteString(f, t , #PB_Ascii)
	Next
Wend

endo · Post by **endo** » Thu Sep 11, 2008 2:21 pm

Another problem is that DatabaseColumnType() function gives "long" value for SQL Server's Unique Identifier values. It should give "string" or null result so it can be readable.

rsts · Post by **rsts** » Thu Sep 11, 2008 4:54 pm

Don't know if all databases are the same in their interpretation of null.

For sql - try http://en.wikipedia.org/wiki/Null_(SQL)

cheers

endo · Post by **endo** » Fri Sep 12, 2008 8:37 am

rsts wrote:Don't know if all databases are the same in their interpretation of null.

For sql - try http://en.wikipedia.org/wiki/Null_(SQL)

cheers

I guess this should be handled by PB using ODBC API (or other DB APIs) because handling NULL values is really important and there is no way to do that in PB.
By the way, it gives better result if I get all the fields using GetDatabaseString() instead of cheking the column type and using the related GetDatabase...() function.
Because,
GetColumnType() gives Numeric for GUID columns, but GetDatabaseString() gives empty string so we can say its value is NULL.

But problem still exists if the column type is String and the value is NULL. There is no way to determine the value is empty-string ("") or NULL, because GetDatabaseString() returns empty-string in both cases.

Marlin · Post by **Marlin** » Mon Sep 15, 2008 9:13 pm

Some databases like Oracle don't differentiate between empty string and null anyways.

If you need to differentiate, you could also let the database tell you:

Code: Select all

select
  col1,
  case when col1 is null then 1 else 0 end as col1_is_null,
  ...

However needing to handle null differently than empty string
also might make the gui part of an application more complex...

F.e. if you want to let the user set a string value that could be empty or null("<>" empty string).

In many cases I would not see an advantage of that approach.

As you would need to map empty strings for numeric fields to null,
why not do the same for string values (simply save empty strings as null)
and treat it the same?

SQL concatenation with null values of course can have it's problems:

Code: Select all

select null || 'somestring' ...   will select
null   in SQLite3 and
'somestring'   in Oracle.

Rook Zimbabwe · Post by **Rook Zimbabwe** » Mon Sep 15, 2008 10:56 pm

So you are looking for a way to determine the tables names and column names in a ODBC or SQL DB?

ABBKlaus gave me this section...

Code: Select all

;-
#ODBC_ADD_DSN    =   1 ; Add Data source
#ODBC_ADD_SYS_DSN   =   4 ; Add SYSTEM Data source
#ODBC_CONFIG_DSN    =   2 ; Configure (edit) Data source
#ODBC_REMOVE_DSN   =   3 ; Remove Data source
#ODBC_REMOVE_SYS_DSN   =   6 ; Remove SYSTEM Data source
#SQL_SUCCESS    =   0
#SQL_SUCCESS_WITH_INFO   =   1
#SQL_ERROR    =  -1
#SQL_INVALID_HANDLE   =  -2
#SQL_NO_DATA   = 100
#SQL_MAX_MESSAGE_LENGTH   = 512
#SQL_NTS   =  -3
#SQL_HANDLE_ENV   =   1
#SQL_HANDLE_DBC   =   2
#SQL_HANDLE_STMT    =   3
#SQL_HANDLE_DESC     =   4
#SQL_C_CHAR    =   1
#ODBC_ERROR_GENERAL_ERR    =   1
#ODBC_ERROR_INVALID_BUFF_LEN    =   2
#ODBC_ERROR_INVALID_HWND    =   3
#ODBC_ERROR_INVALID_STR   =   4
#ODBC_ERROR_INVALID_REQUEST_TYPE    =   5
#ODBC_ERROR_COMPONENT_NOT_FOUND     =   6
#ODBC_ERROR_INVALID_NAME    =   7
#ODBC_ERROR_INVALID_KEYWORD_VALUE   =   8
#ODBC_ERROR_INVALID_DSN   =   9
#ODBC_ERROR_INVALID_INF   =  10
#ODBC_ERROR_REQUEST_FAILED   =  11
#ODBC_ERROR_INVALID_PATH   =  12
#ODBC_ERROR_LOAD_LIB_FAILED =  13
#ODBC_ERROR_INVALID_PARAM_SEQUENCE  =  14
#ODBC_ERROR_INVALID_LOG_FILE   =  15
#ODBC_ERROR_USER_CANCELED   =  16
#ODBC_ERROR_USAGE_UPDATE_FAILED = 17
#ODBC_ERROR_CREATE_DSN_FAILED = 18
#ODBC_ERROR_WRITING_SYSINFO_FAILED =19
#ODBC_ERROR_REMOVE_DSN_FAILED = 20
#ODBC_ERROR_OUT_OF_MEM = 21
#ODBC_ERROR_OUTPUT_STRING_TRUNCATED = 22

Define Result

Dim DatabaseType.s(4)
DatabaseType(0) = "Unknown"
DatabaseType(1) = "Numeric"
DatabaseType(2) = "String"
DatabaseType(3) = "Float"


Macro SQL_HANDLE_ENV(Database)
PeekL(PeekL(IsDatabase(Database))-4)
EndMacro

Macro SQL_HANDLE_STMT(Database)
PeekL(PeekL(IsDatabase(Database)+4)+4)
EndMacro

Macro SQL_HANDLE_DBC(Database)
PeekL(PeekL(IsDatabase(Database)+4))
EndMacro

Macro cut(in,by,lf)
lf = Left(in,FindString(in,by,0)-1)
in = ReplaceString(in,lf+by,"")
EndMacro

Procedure.l GetDatabaseTables(Database)
    SQLCancel_(SQL_HANDLE_STMT(Database))
    res.w=SQLTables_(SQL_HANDLE_STMT(Database),0,0,0,0,0,0,0,0)
    If res = 0 Or res = 1 ; #SQL_SUCCESS / #SQL_SUCCESS_WITH_INFO
        ProcedureReturn 1
    EndIf
EndProcedure

Procedure.l GetDatabaseDBNames(Database)
    SQLCancel_(SQL_HANDLE_STMT(Database))
    res.w=SQLTables_(SQL_HANDLE_STMT(Database),"%",-3,"",-3,"",-3,"",-3)
    If res = 0 Or res = 1 ; #SQL_SUCCESS / #SQL_SUCCESS_WITH_INFO
        ProcedureReturn 1
    EndIf
EndProcedure

Procedure.l GetDatabaseTables2(Database,Catalogname.s="%",SchemaName.s="",TableName.s="",TableType.s="")
    SQLCancel_(SQL_HANDLE_STMT(Database))
    If Catalogname="%" And SchemaName="" And TableName=""
        Debug "DBNames"
        res.w=SQLTables_(SQL_HANDLE_STMT(Database),Catalogname,-3,"",-3,"",-3,"",-3)
    ElseIf SchemaName="%" And Catalogname="" And TableName=""
        Debug "Schemaname"
        res.w=SQLTables_(SQL_HANDLE_STMT(Database),0,0,0,0,0,0,0,0)
    Else
        Debug "Rest"
        res.w=SQLTables_(SQL_HANDLE_STMT(Database),Catalogname,-3,SchemaName,-3,TableName,-3,TableType,-3)
    EndIf
    If res = 0 Or res = 1 ; #SQL_SUCCESS / #SQL_SUCCESS_WITH_INFO
        ProcedureReturn 1
    EndIf
EndProcedure

endo · Post by **endo** » Tue Sep 16, 2008 7:43 am

Thanks for the replies. Ofcourse it may possible to use some another value for null values. 0 for numeric and "" for string type of columns. But we can't do that always, if 0 has a different meaning ("" as well), yes, then choose another one? Well, what if db is not designed by me? I can not say "please use 0 instead of null".

Another problem is using a relation between two tables by foreign-key (unique identifier field type, GUID)? you can not insert a empty-string in that field. You have to put one of valid GUID value or NULL value. I can't change the field type if DB is not designed by me as well.

Marlin · Post by **Marlin** » Wed Sep 17, 2008 12:46 am

As far as I know, there is no nullable datatype in PureBasic
and there was no nullable datatype in VB6 and
probably quite some other non database languages.

Datatypes are usually nullable in PL/SQL (Oracle),
and some special nullable datatypes were introduced
in the Microsoft DotNet Framework.

As there is no Null value for the ordinary datatypes,
you would need to find a way to store the "isnull" property
for the variables you need that property for.
You could f.e. do that with a stucture with a "isnull" structure field.

Code: Select all

Structure NullableLong
  l.l
  isnull.l
EndStructure


Procedure DebugNullableVal(*pValue.NullableLong)
  If *pValue\isnull
    Debug  "Null"
  Else
    Debug *pValue\l
  EndIf
EndProcedure


myval.NullableLong

; Assign value:
myval\l = 5
myval\isnull = #False

DebugNullableVal(myval)

; Assign Null:
myval\isnull = #True
; and possibly:
myval\l = 0

DebugNullableVal(myval)

; Assign other value:
myval\l = 99
myval\isnull = #False

DebugNullableVal(myval)

As I have shown in my other post above, you can let the db tell you if a field value is Null or not.

There is no editable recordset in PB, so you write your values to the database by DatabaseUpdate()'s only.

Storing Nulls is not problem that way:

Code: Select all

If Not DatabaseUpdate(#myConnection, "update mytable set mycol = null where mykey = 12897")
  Debug DatabaseError()
EndIf

endo · Post by **endo** » Wed Sep 17, 2008 7:56 am

Thank you for the reply.
Actually VB6 variables can have NULL value. Ofcourse you should set your variable type to VARIANT instead of Long or String. Then you can set it to NULL or read from a db with NULL value, then check by IsNull().

Your structure advice is good, but ofcourse it may work if YOU designed the DB. And ODBC has also a null type so I guess Purebasic can have a NULL sturucture (and it should check if the value read from db is null then DatabaseVALUEType should return #PB_Database_Null)

The main problem is reading a field from a DB, not just using NULL values in an application.

Simply, How do I get the result of this SQL: SELECT NULL;

It is impossible to get the correct result in PB (GetDatabaseString returns empty-string, GetDatabaseLong returns 0, but you can't be sure it is real value is 0 or empty-string or NULL), but it is possible in other languages like VB6, PHP, C, Perl (they usually have IsNull function)

Or we should use ODBC APIs directly to handle the correct value type and the value itself.

Post by **Fred** » Wed Sep 17, 2008 1:06 pm

I will see what can be done.

endo · Post by **endo** » Wed Sep 17, 2008 1:13 pm

Fred wrote:I will see what can be done.

that's great! Thanks a lot.

Marlin · Post by **Marlin** » Wed Sep 17, 2008 7:51 pm

@endo:

You're right VB6 variants could take Null values ...
but not ordinary types.

As far as I know there is no builtin variant type in PB (yet).

However there is still at least one way to "read" NULL values from any db.

No need for db design changes!

Code: Select all

Structure NullableString
  s.s
  isnull.l
EndStructure

Procedure DebugNullableString(*pValue.NullableString)
  If *pValue\isnull
    Debug  "Null"
  Else
    Debug *pValue\s
  EndIf
EndProcedure


Define ColVal_01.NullableString
Define myConnection.l
Define strSql.s


; get your connection etc.

; ...


strSql = "select col_1, case when col_1 is null then 1 else 0 end as col_1_is_null from mytable where mykey = 34276"
; any database should have some way to "tell" if a column value is null or not,
; many should get along with the above statement.

If Not DatabaseQuery(myConnection, strSql)
  Debug DatabaseError()
  End
EndIf

If Not NextDatabaseRow(myConnection)
  Debug "No record found!"
  End
EndIf

ColVal_01\s = GetDatabaseString(myConnection, 0)
ColVal_01\isnull = GetDatabaseLong(myConnection, 1)

; show value or possibly Null:
DebugNullableString(ColVal_01)

; ...

What I want to stress is, that it is possible to get NULL values identified and handled with the current possibilities of PureBasic.

Maybe Fred could built some function like DatabaseValueIsNull(#Database, Column) to enable the identification of NULL values without the need to adapt the SQL statement.
Further approaches seem to bring the need for more basic design changes and maybe would look more complicated.

I do like the nice and simple ways of handling things with PureBasic
and also it's speed.

I didn't miss NULL values in PB all that much.

endo · Post by **endo** » Thu Sep 18, 2008 10:51 am

Your advice is pretty good! Thank you very much.
The only problem is that we can not use SELECT * FROM type of queries, and also if there are lots of nullable fields in table this method needs a little more effort. But any way it is good to find a workaround.
Thanks again.

endo · Post by **endo** » Thu Sep 18, 2008 11:01 am

I didn't miss NULL values in PB all that much.

I use PB from 98 (I'm the one of the first registered users, at that time there was no Windows & Linux version, only Amiga..) and never needed to use a null value (kind of db-style null value)
Because, actually zero or empty-string means Null in all old (or low level) programming languages. There is no real "Null" anyway.
But now I should write a DB application for my company and it's difficult to do it without Null.. :T
Thanks.

Marlin · Post by **Marlin** » Thu Sep 18, 2008 8:50 pm

@endo:

I am quite impressed.
I only discovered PureBasic in the second half of 2006.

Yes, my suggestion is a kind of workaround.
The more nullable fields of interest you have, the less nice it looks...

But I would like to point out, that you can use "*" in you sql field list and also have additional other items.

like:

Code: Select all

select <list of columns for Null information>, * from mytable ...