Re: Inserting Empty String stores Quotes in Postgres Database

[Mark Rendle]

Is it actually inserting double quotes, or is that just how empty strings are represented in that tool?

Mark

Sent from my iPad

On 29 Sep 2012, at 20:56, Nate <nas...@gmail.com> wrote:

Hello mailing list -

I'm trying to use Simple.Data with Simple.Data.Postgres.

Using Simple.Data version 0.17.1.1 and Simple.Data.Postgres version 0.16.2.2 (because Simple.Data.Postgres doesn't work with 0.18.0 yet).

I have a basic model object with basic properties that I'm trying to insert into a table.
A few of my object's values are empty strings.

However, when I look in the Postgres database, the empty string columns are being filled with two single quotes, e.g. ''  (note there's a space between them).

Here's the SQL output:

Simple.Data.Ado:
Text
INSERT INTO "db"."chat_message" ("sender","destination","message","source","topic","server","event","session","location","sourcetime","collecttime") VALUES(:p0,:p1,:p2,:p3,:p4,:p5,:p6,:p7,:p8,:p9,:p10) RETURNING *;
p0 (String) = test
p1 (String) = Room2
p2 (String) = test msg
p3 (String) = Unknown
p4 (String) =
p5 (String) = rox-78944C3C
p6 (String) = MyEvent
p7 (String) = 1
p8 (String) =
p9 (DateTime) = 9/29/2012 7:44:00 PM
p10 (DateTime) = 9/29/2012 7:44:00 PM

Based on the printout above, both topic and location should be inserted as nulls or empty.  However, instead double single quotes appear in the database.

If I actively set the empty string values before the insert to null then my record is inserted correctly.

I've attached a picture of my database table.  You can see after record 10 how I switched my object's location value from empty string to null.

Has anyone else encountered this issue? Does anyone know how I can fix it?

I'd rather not have to figure out during run time and for every insert if any of my model's properties are empty strings and switch them to nulls.

Thanks much,
Nate

<DatabaseTable.png>

[Nate]

Mark (and mailing list) -

As soon as I submitted my post, I realized I had failed that timeless troubleshooting/bug fixing step of googling your broader issue.  Sure enough, I found a blog post saying that Postgres' Pg Admin III (which is what i use to sanity check my Postgres database table content) shows empty text fields as ''.

The rationale, supposedly, is that it indicates an empty field. 

So this is not even a 'bug' just an oddity with Pg Admin III and hopefully this info may help out others.

As always Mark, thanks for the great support with Simple.Data!

[Mark Rendle]

No problem!

BTW, are you the same Nate who was working on an MS Access provider? I had someone asking about it the other day...

Cheers,

Mark

[Nate]

Mark -

Sad to say, I am the same Nate =/

I did, in fact, write a Simple.Data.Access provider and got to the point
where I waited for you to implement the changes to allow operator
overloading since Access uses things like <> to mean !=.  You responded
quite quickly with updates and then it was on me to do the rest.

I just had to override a few operators, add in a few more unit tests, build
a Nuget package, upload it onto my Github site, setup a readme page and it
would be done and available for all.

Unfortunately, I got dragged into my projects and have been working hard to
meet all sorts of deadlines and haven't had the time to put those last few
touches on it.

I'll really really try to get something up in the next two weeks.  How you
manage to stay so involved and rapidly responsive I find impressive.
Really makes it enjoyable using the Simple.Data framework.  So thanks!

[Mark Rendle]

Nate,

If you put it up on Github in its current state, you might find people will help out. I'll link the person who was looking for the provider to this thread.

Cheers,

Mark