Extremely simple SQLite query doesn't return expected

Question

Below I provide my database schema, the database data currently inserted (which is only one record), and the code I am running. It is a very simple setup, but instead of returning the one record in the database, it returns nothing. Anyone have any idea why? I am on my wit's end here...

Table: Subcontractor

Columns: listed below in (name type) format.

ID guid, 
BusinessName varchar(50), 
Address varchar(200), 
City varchar(50), 
State varchar(50), 
ZipCode varchar(50), 
Contact varchar(50), 
Phone varchar(50), 
Fax varchar(50), 
Email varchar(200), 
GLOPolicy bit, 
GLOLimit bigint, 
GLOExpiration datetime, 
ALPolicy bit, 
ALLimit bigint, 
ALExpiration datetime, 
WCPolicy bit, 
WCLimit bigint, 
WCExpiration datetime, 
ULPolicy bit, 
ULLimit bigint, 
ULExpiration datetime, 
Notes varchar(15000)

=====

I have one record in my database, as follows.

ID "7b143c19-ad66-46ad-b587-db0bee98cf1e"
BusinessName "1"
Address "1"
City "1"
State "1"
ZipCode "1"
Contact NULL
Phone NULL
Fax NULL 
Email NULL
GLOPolicy False (0) 
GLOLimit NULL 
GLOExpiration NULL
ALPolicy False (0)
ALLimit NULL
ALExpiration NULL
WCPolicy False (0)
WCLimit NULL
WCExpiration NULL
ULPolicy False (0) 
ULLimit NULL
ULExpiration NULL
Notes NULL

===== *I am attempting the following query, and it returns nothing, when it should obviously return the only record, shown above.*

String ID = "7b143c19-ad66-46ad-b587-db0bee98cf1e";
DataTable dt = sqliteQuery.selectFromDatabase("*", "WHERE ID = '" + ID + "'");

And the code for the above method is...

    public DataTable selectFromDatabase(String column, String filter)
    {
        string SQL = "SELECT " + column + " FROM SUBCONTRACTOR " + filter;
        SQLiteCommand cmd = new SQLiteCommand(SQL);
        cmd.Connection = connection;
        SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
        DataSet ds = new DataSet();
        try
        {
            da.Fill(ds);
            DataTable dt = ds.Tables[0];
            return dt;
        }
        catch (Exception e)
        {
            MessageBox.Show(e.ToString());
            return null;
        }
        finally
        {
            cmd.Dispose();
            connection.Close();
        }
    }

First try to select without filtering. Set your predicate to be WHERE 1 = 1 (always true) instead of the filter to make sure you get all the rows. If you don't then it's not the query at fault. Also, your column type is A GUID but your query uses a string value. You might need a conversion. — Paul Sasik
– Paul Sasik, Commented Dec 19, 2010 at 6:33
When setting the filter to 1=1, it returns the single record in the database, as it should. What type of conversion is there suppose to be? I was under the assumption everything was passed as strings when doing queries... is that not the case? If it isn't, do you know of such a conversion? — CODe
– CODe, Commented Dec 19, 2010 at 6:35

Paul Sasik · Accepted Answer · 2010-12-19 06:53:04Z

4

It looks like SqLite does not support a GUID type natively. It's supported by wrappers and plugins but I think that you're experiencing some impedance between the way the type is used. You are probably better off changing it to a TEXT type. Your predicate will then work.

EDIT

To create a new GUID value to insert into the row as a pkey just do:

Guid.NewGuid().ToString();

In addition I found the following info:

GUID is not(!) a native SQLite-datatype, but an addon provided by Robert's wrapper. In the connection string you can specify "BinaryGUID=Yes|No", with Yes as default. When "BinaryGUID=Yes" the GUID is stored taking 16 bytes of storage. Now it depends whether "SQLite Expert" recognises the datatype GUID. If so, you should look at its documentation how it is handled. If not, it is probably treated as text (likely with invalid character data). You will have this trouble again when you change your SQLite editor or wrapper.

When "BinaryGUID=No" the GUID is stored as text taking 32-38 bytes (I don't know whether the minus-signs and braces {} are stored, you have to test). When storage is not a large problem, I would recommend to use this form. You then should have no problems with whatsover wrapper or DB editor you use and save a lot of time and trouble.

There seems to be an issue with the type itself.

edited Dec 19, 2010 at 6:53

answered Dec 19, 2010 at 6:40

Paul Sasik

81.9k23 gold badges152 silver badges194 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

CODe Over a year ago

So, the wrapper converts a hexadecimal? Blah, is there a way to easily do what I'm doing above in a way that will actually work? -_-

Paul Sasik Over a year ago

Yeah. Change your ID column type to text. (I made that the main point of my answer.)

CODe Over a year ago

I'm unfamiliar with the text datatype, does it give a unique value for every record like guid does? Or would I have to do that manually somehow?

Paul Sasik Over a year ago

There are only a few data types and varchar(50) should do fine, meaning that it will work like Sqlite's TEXT type. (The wrappers really confuse things... Makes it look like Sql Server.) And yes, you will have to insert a GUID yourself but that is VERY EASY to do. It's actually a member of the GUID class.

Paul Sasik Over a year ago

See the EDIT for the GUID generating code. Very simple, static method call.

|

Collectives™ on Stack Overflow

Extremely simple SQLite query doesn't return expected

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related