Hi,

This is a very weird behaviour I've found of either SQLite, or the Unit. I'm not sure... I have been discussing this same problem on the SQLite mailing list but without success there, so perhaps someone here might know...

I have attached a test database, which contains 1 database entry, null strings are inserted using NULL... Check the database schema yourself.

The problem therein lies in the fact that test3.pas returns "" for the query: SELECT channelNotice FROM channels WHERE channel LIKE '#ProLogiTech'; It should return a null string as it does in test2.pas and in the sqlite shell program.

$ ./test2
Creating class
Fields Names -------------------
0 -> channel
1 -> owner
2 -> regTime
3 -> state
4 -> URL
5 -> email
6 -> channelNotice
7 -> joins
8 -> peak
9 -> games
10 -> modeLocked
11 -> mailLevel
12 -> restrict
13 -> entries
14 -> quota
15 -> channelIdle
16 -> noIdle
17 -> lastSuspension
18 -> hideAccess
19 -> keepTopic
20 -> onChannel
21 -> opRestrict
22 -> funMsg
23 -> mustId
24 -> tellAdd
25 -> tellDel
26 -> tellSet
27 -> msgNotice
28 -> topic
29 -> topicSetter
30 -> lastTopic
31 -> autoCmd
32 -> modes
Fields -------------------
0 -> #ProLogiTech prologic 1057934559 REGISTERED 1 1 0 0 1 50 0 OFF NO YES YES NO NO NO NO NO NO NO

$ ./test3
Creating class
Fields Names -------------------
0 -> channelNotice
Fields -------------------
0 -> ""

This should be: 0 ->

Any ideas anyone ?
The attached sqlite.pas and sqlitedb.pas were put together by Michael, myself and Eric Jourde (possibly ported from tsqlite I hear)

cheers
James

The problem is that an implicity conversion from Nil to empty string is done. You cannot solve this as long as stringlists are used to contain the data. (ok, you could set 'Null' as the string, but that is sloppy)

You'd need to have (as a minimum) an array of TField records:
  TField = Record
    IsNull : Boolean;
    Value : String;
  end;

But this will require major changes, so I suggest trying to find or implement a TDataset descendent for SQLIte, this will solve most of your .

"is done." in what ? The SQLite library or TStringList ?

TDataset descendent ? Couldn't I write a descendent myself ? (I've kinda of done something similar in creating a TDatabase descendant..., but it doesn't solve my problem)

cheers
James

sqlite.

Implementing a TDataset descendent is not easy, it'll take you at least a week (conservative estimate).

Michael.

But you said "You cannot solve this as long as stringlists are used to contain the data." This made me think it was a behaviour of TStringList ... If it truely is a behaviour of SQLite, I'll get them to change it.

Why is this a hard thing to do ? Give me some ideas here, you might be conservative, but I don't muck around :)
I'll attach my dbclass.pas a tiny desendant I wrote to simplify using queries (it's centered around making queries simpler)...

cheers
James

sure what I'm looking for here... An initial search on google provides some delphi units that are probably not what I'm looking for.

cheers
James

The problem is that there is no way to distinguish between a 'null' and an empty string once it is converted to a string. The information is simply lost.

Because TDataset is not an easy subject. You need to implement a lot of calls in a correct and predefined way.

Michael.

Ok thanks. I had searched for "TDataset descendant sqlite pascal" one page came up from Torry's pages.

*hits Mozilla, it crashes too much :P*

cheers
James

On the contrary, on Torry's pages there are some units that do exactly what I described.

Michael.

Just reconfirming with you and solidifying my knowledge. The reason it returns "" is because: when working with native pascal types, assigning null to a string results in an empty string "" right ?

cheers
James

James,

Pascal is a strongly typed language and there is no Pascal string precisely equivalent to a NULL. Someone (either the SQLite authors or the one who wrote the Pascal wrapper) decided to automatically translate NULL into empty (zero-length) strings. And it's impossible now to distinguish actual null values from fields containing actual zero-length strings.

You could solve this in several ways:

1) re-write all the code to return and store a more complex record (as suggested in a previous post) that include a string and a boolean, this record completely and precisely captures the data SQL is returning;

2) re-write the code that does the automatic translation to insert the string 'NULL' (in which case, you will never be able to distinguish NULL's from actual fields containing the value 'NULL'--but maybe this is no problem);

3) you could simply avoid ever having empty strings in your database (in which case, empty strings returned are always NULL's)

4) Live with not being able to distinguish, in many instances, an empty string is practically the same as a NULL (in other instances, however, it indicates that some records in a full join failed to find a match)

HTH,

-Alan

__________________________________
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com

I think I'm slowly understanding this bit now. I don't claim to be an SQL expert. But SQL (sqlite anyway) is capable of storing any data types, strings, integers, boolean etc, including NULL values. I hope I'm correct here...

I don't believe the authors of the sqlite unit do translate NULL into empty (zero-length) strings. I did check the source, check yourself if I'm wrong.

Why could I not simply check for '""' in my sql return functions and simply return '' instead (a pascal empty string) ? Would this be terribly wrong ? Or am I still going to have to follow Michael's suggestion in finding a TDataset desendant ? (I don't understand what a TDataset desendant really is and why I need to use one but anyway...)

There should be no empty string in my database anyway. If a field is empty it's value is NULL.

cheers
James

They do. But implicitly, by converting a pchar to a string. A nil will be converted to an empty string.

Michael.

Oh :)

It's funny how I haven't been able to actually reproduce this in some test programs on Linux using FPC.

cheers
James

Not having much luck with Torry's pages I'm afraid. However I've looked at the FCL source, there is a mysqldb.pp in the db directory... Couldn't that be used for sqlite (obviously with changes) ?

cheers
James

Yes.

Michael.

I know nothing specifically about SQLite but SQL has many data types, similar to Pascal. However, SQL data fields can also have the value NULL which has no analog with Pascal data. Pascal data types cannot simultaneously hold data and this non-data missing value code (unless you define some convention within your code... like you could arrange for NULL results to be returned as the string value 'NULL' but then you would be unable to distinguish between actual strings with the value 'NULL' and the NULL result... which may be no big deal).

According to Michael, someone arranged for NULL values to be translated to empty strings, perhaps without even meaning to, because of how ansistrings act.. they are internally pointers and an empty string is represented as a nil pointer.

I have no idea why this behavior would be different between Windows (?) and Linux. (in fact, are you sure it is?)

Why could I not simply check for '""' in my sql return functions and simply return '' instead (a pascal empty string) ? Would this be terribly wrong ?

See below.

I think the idea with the TDataset was to create a record in Pascal that could hold all the SQL data. Since a string alone cannot hold it, you need a record with a string and a boolean. The boolean holds NULL or not-NULL. When the boolean holds not-NULL, the string holds the data (the string should never hold data if the boolean hold NULL). By "boolean holds NULL", of course, I mean that you define the boolean in some way so that it indicates the NULL-ness. For example, define it as 'IsNull:boolean;' and then TRUE will indicate that the result is NULL.

Well then why are you worried about distinguishing between NULL's and empty strings? All empty strings are NULL's. (I know originally, you were compiling test programs... is this really an issue?)

-Alan

__________________________________
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com

Why did Michael say that creating a TDataset was so hard ? If in it's simplest form it's just a record to hold the data with a boolean indicating it's null-ness, that doesn't seem so difficult to implement to me.

I might play around with the mysqldb.pp dataset class in the fcl source though...

My program relies on the fact that some fields (which are string types) are null. I'm not sure what to say next so I'll leave it at that :P

cheers
James

It is not that, it's much more than that. TDataset manages such 'records' and much more.

You can write whole books about the TDataset class and it's descendents. I wouldn't advise someone with no experience with TDatasets to start writing a descendent.

Michael.

You obviously know a lot about this topic as I've seen in the mysql dataset you created and the generic dataset. I've looked at it and shaken my head! (I'll admit I don't know a lot about databases, but am learning, SQL is my only knowledge thus far).

Having said that though, I'm going to try and modify Eric Jourde's sqlite unit to accomadate for NULLs (try being the operative word :P)

cheers
James

--- James Mills wrote

My program relies on the fact that some fields (which are string types) are null. I'm not sure what to say next so I'll leave it at that :P

If your database never contains an empty string, then your program can rely on the empty strings you retrieve being NULL values in the database.

But if an empty string ever crept into a database field, then your program would think the field is null when it's not. If this would cause a grevious error, then you should find a better way.

-Alan

__________________________________
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com

This case is true and the database will never contain empty strings only NULL values. The program has strict ways of doing this, unless of course you modify the databases externally, then bam (you're right as per below)...

cheers
James

So if you do nothing, your program will work unless someone accesses the database externally (or there is a bug in whatever code ensures against inserting empty strings). You need to decide how likely and bad that is.

But maybe there is a simple work-around. Surely you can execute SQL select statements using SQLite, right? If so, then you can exploit the fact that SQL itself is well aware of the distinction between empty strings and NULL's to check for this error condition.

If you're checking one table called MYTABLE with a key called IDX and a couple fields to check are ADDRESS1 and ADDRESS2 then you would execute this select:

select IDX from MYTABLE where ADDRESS1='' or ADDRESS2=''

If you get no results, then there is no error condition (you can safely assume empty strings are NULL's). If you get any results, then you know which records need to be fixed.

You could execute this query at the start of your application's run. Or you could run it periodically (e.g., nightly) and email yourself the results.

-Alan

__________________________________
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com

Well the only bad thing is that it returns data where there should be no data. ie: '""' instead of the expected '', causing the program to treat '""' as if it were a real string.

James

like you could arrange for NULL results to be returned as the string value 'NULL' but then you would be unable to distinguish between actual strings with the value 'NULL' and the NULL result...

Just a thought, but maybe you could return
the single-character string: #0

__________________________________
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com

Thanks mate :) I'm so lost anyway, all these people with much deeper knowledge of databases...

cheers
James

All the better. The string '""' is unlikely to exists you your database naturally, right? So when you see it, you have a NULL.

Or you could, as you suggested earlier, just convert it to an empty string.

-Alan

__________________________________
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com

That's correct. What I've learnt over the past 2 days though is that I'll be leave blinded by all this unless I go pickup a book on database and read it! :)

Or you could, as you suggested earlier, just convert it to an empty string.

I might do that if I have no luck with a TDataset desendant...

Thanks Alan,

James

Hello,
From SQLite docs:
"Note that the callback function reports a NULL value in the database as a NULL pointer, which is very different from an empty string. If the i-th parameter is an empty string, we will get:

argv[i][0] == 0
But if the i-th parameter is NULL we will get:

argv[i] == 0"So maybe there are different pointers here which You should test ?Best RegardsBoguslaw