Perl DBI Module: SQL query where there is space in field name

[ambarish.mitra]

Hi all,

Using the DBI module, I have connected to a CSV file, and am trying to
execute SQL queries on the CSV file. I am stuck when there is a space
in the field name and I cannot proceed.

The CSV file col heading:
"Attribute","Display Name","Semantic Type","Display Type". (ie, space
in the heading)

I am trying to "prepare" only those lines for which 'Display Type' is
given.

The error line is given:

my $sth = $dbh->prepare("select * from report where [Display Type]
=MultiLineText");


SQL ERROR: Bad table or column name '[Display Type]' has chars not
alphanumeric or underscore!
SQL ERROR: Couldn't find predicate!


I have tried some googling and also tried multiple combination of
brackets/qoutes/escapes with a hope that one of them will work, but
without luck.

Question: What is the way to fire this command in PERL where the field
name (and value as well) can have spaces?

Regards,
Ambarish.

 

[Andrew DeFaria]

I have no idea. You might ask in a PERL group - this one talks about Perl.

What a non-helpful answer Sherm...

 

[Ben Morrow]

Quoth (e-mail address removed):

Hi all,

Using the DBI module, I have connected to a CSV file, and am trying to
execute SQL queries on the CSV file. I am stuck when there is a space
in the field name and I cannot proceed.

The CSV file col heading:
"Attribute","Display Name","Semantic Type","Display Type". (ie, space
in the heading)

I am trying to "prepare" only those lines for which 'Display Type' is
given.

The error line is given:

my $sth = $dbh->prepare("select * from report where [Display Type]
=MultiLineText");


SQL ERROR: Bad table or column name '[Display Type]' has chars not
alphanumeric or underscore!
SQL ERROR: Couldn't find predicate!

Assuming you are using DBD::CSV, the docs point you to SQL::Statement,
which says you should quote identifiers with double quotes. Quoting with
square brackets is (IIRC) an MS Access-specific syntax. So try

my $sth = $dbh->prepare(
'select * from report where "Display Type"=MultiLineText'
);

Ben

 

[RedGrittyBrick]

[Apparently using DBD::CSV]

my $sth = $dbh->prepare("select * from report where [Display Type]
=MultiLineText");
...
SQL ERROR: Bad table or column name '[Display Type]' has chars not
alphanumeric or underscore!
...
Question: What is the way to fire this command in PERL where the field
name (and value as well) can have spaces?

See col_names and skip_first_row in
http://search.cpan.org/~jzucker/DBD-CSV-0.22/lib/DBD/CSV.pm

I think you can use these to assign legal column names without requiring
changes to the CSV.

 

[Jens Thoms Toerring]

Using the DBI module, I have connected to a CSV file, and am trying to
execute SQL queries on the CSV file. I am stuck when there is a space
in the field name and I cannot proceed.

The CSV file col heading:
"Attribute","Display Name","Semantic Type","Display Type". (ie, space
in the heading)

I am trying to "prepare" only those lines for which 'Display Type' is
given.

The error line is given:

my $sth = $dbh->prepare("select * from report where [Display Type]
=MultiLineText");

SQL ERROR: Bad table or column name '[Display Type]' has chars not
alphanumeric or underscore!
SQL ERROR: Couldn't find predicate!

I have tried some googling and also tried multiple combination of
brackets/qoutes/escapes with a hope that one of them will work, but
without luck.

Question: What is the way to fire this command in PERL where the field
name (and value as well) can have spaces?

I guess you're out of luck. While the documentation of DBI::CVS only
explicitely says that table names can't contain spaces (due to SQL
not allowing any) if I remember correctly also spaces in column
names aren't allowed in SQL. Thus I wouldn't expect DBI to accept
them even with some kinds of quotation/escape characters (which
probably would have an extra meaning in an SQL statement).

Regards, Jens

 

[Jürgen Exner]

--------------070607060008040400030708
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


What a non-helpful answer Sherm...

And you honestly believe your 120+ lines long signature (quoted in full
below) is any more helpful?

jue

--
Andrew DeFaria <http://defaria.com>
Whatever happened to Preparations A through G?

--------------070607060008040400030708
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<style type="text/css">
body {
font: Helvetica, Arial, sans-serif;
}
p {
font: Helvetica, Arial, sans-serif;
}
.standout {
font-family: verdana,
arial,
sans-serif;
font-size: 12px;
color: #993333;
line-height: 13px;
font-weight: bold;
margin-bottom: 10px;
}
.code {
border-top: 1px solid #ddd;
border-left: 1px solid #ddd;
border-right: 2px solid #000;
border-bottom: 2px solid #000;
padding: 10px;
margin-top: 5px;
margin-left: 5%;
margin-right: 5%;
background: #ffffea;
color: black;
-moz-border-radius: 10px;
}
.terminal {
border-top: 10px solid #03f;
border-left: 1px solid #ddd;
border-right: 2px solid grey;
border-bottom: 2px solid grey;
padding: 10px;
margin-top: 5px;
margin-left: 5%;
margin-right: 5%;
background: black;
color: white;
-moz-border-radius: 10px;
}
#code {
color: black;
font-size: 14px;
font-family: courier;
padding-left: 5px;
}
#line-number {
color: #804000;
font-family: Arial;
font-size: 14px;
padding-right: 5px;
border-right: 1px dotted #804000;
}
blockquote[type=cite] {
padding: 0em .5em .5em .5em !important;
border-right: 2px solid blue !important;
border-left: 2px solid blue !important;
}
blockquote[type=cite]
blockquote[type=cite] {
border-right: 2px solid maroon !important;
border-left: 2px solid maroon !important;
}
blockquote[type=cite]
blockquote[type=cite]
blockquote[type=cite] {
border-right: 2px solid teal !important;
border-left: 2px solid teal !important;
}
blockquote[type=cite]
blockquote[type=cite]
blockquote[type=cite]
blockquote[type=cite] {
border-right: 2px solid purple !important;
border-left: 2px solid purple !important;
}
blockquote[type=cite]
blockquote[type=cite]
blockquote[type=cite]
blockquote[type=cite]
blockquote[type=cite] {
border-right: 2px solid green !important;
border-left: 2px solid green !important;
}
</style>
</head>
<body>
Sherman Pendley wrote:
<blockquote id="mid_m1fxssrd41_fsf_dot-app_org"
cite="mid:[email protected]" type="cite"><a class="moz-txt-link-abbreviated" href="mailto:[email protected]">[email protected]</a>
writes:<br>
<blockquote id="StationeryCiteGenerated_1" type="cite">Question: What
is the way to fire this command in PERL where the field name (and value
as well) can have spaces?<br>
</blockquote>
<!---->I have no idea. You might ask in a PERL group - this one talks
about Perl.<br>
</blockquote>
What a non-helpful answer Sherm...<br>
<div class="moz-signature">-- <br>
<a href="http://defaria.com">Andrew DeFaria</a><br>
<small><font color="#999999">Whatever happened to Preparations A
through G?</font></small>
</div>
</body>
</html>

--------------070607060008040400030708--

 

[Andrew DeFaria]

And you honestly believe your 120+ lines long signature (quoted in
full below)

Looks like somebody needs to learn what a signature is...

is any more helpful?

There's a difference between being non helpful and being a pinhead who
insults somebody who's merely asking a question.

 

[ambarish.mitra]

[Apparently using DBD::CSV]

my $sth = $dbh->prepare("select * from report where [Display Type]
=MultiLineText");
...
SQL ERROR: Bad table or column name '[Display Type]' has chars not
alphanumeric or underscore!
...
Question: What is the way to fire this command in PERL where the field
name (and value as well) can have spaces?

See col_names and skip_first_row inhttp://search.cpan.org/~jzucker/DBD-CSV-0.22/lib/DBD/CSV.pm

I think you can use these to assign legal column names without requiring
changes to the CSV.

Absolutely. Bang on target. This works. We have to set up our col
names without spaces using col_names like:
$dbh->{'csv_tables'}->{'report'} = {'file' => 'report.csv',
'col_names' =>
["Attr","Name","S","Type"]
};

After this, trying to do "prepare" (with where Type=) works perfectly.

Thanks to Ben and others who spent some time on this question.

There are some people quarreling over non-issues in this thread.
Requesting them to take that fight offline so that the thread contents
remain focussed and helpful for generations to come.

 

[Achim Peters]

Looks like somebody needs to learn what a signature is...

--------------030103090100010400060503
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>

Please stop posting HTML and/or MIME. TIA!

Bye
Achim

 

[Ted Zlatanov]

AD> Looks like somebody needs to learn what a signature is...

Yes, please do.

Ted

 

[szr]

Please stop posting HTML and/or MIME. TIA!

It's a multiple format posting; one section for html and one for plain
text. If I set my reader to use plain-text only, I don't see html, just
the plain text version. Can your reader not be configured similarly? If
you only wish to see plain text then set your reader to display only
plain text and then you wont have a problem.

 

[Jürgen Exner]

Looks like somebody needs to learn what a signature is...

Isn't it the part of a posting that is following the dash-dash-blank
line? And that is supposed to be 4 lines max?

Well, your's over 120+ lines and contained pretty much no information at
all.

jue

 

[Jürgen Exner]

Sounds like somebody is still ignorant

Yep, looks very much like it [Fullquote intentional]:

--
Andrew DeFaria <http://defaria.com>
Don't be accommodating, be honest. I honestly don't have much more time
for anything else.

--------------090207050102050807010007
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<style type="text/css">
body {
font: Helvetica, Arial, sans-serif;
}
p {
font: Helvetica, Arial, sans-serif;
}
.standout {
font-family: verdana,
arial,
sans-serif;
font-size: 12px;
color: #993333;
line-height: 13px;
font-weight: bold;
margin-bottom: 10px;
}
.code {
border-top: 1px solid #ddd;
border-left: 1px solid #ddd;
border-right: 2px solid #000;
border-bottom: 2px solid #000;
padding: 10px;
margin-top: 5px;
margin-left: 5%;
margin-right: 5%;
background: #ffffea;
color: black;
-moz-border-radius: 10px;
}
.terminal {
border-top: 10px solid #03f;
border-left: 1px solid #ddd;
border-right: 2px solid grey;
border-bottom: 2px solid grey;
padding: 10px;
margin-top: 5px;
margin-left: 5%;
margin-right: 5%;
background: black;
color: white;
-moz-border-radius: 10px;
}
#code {
color: black;
font-size: 14px;
font-family: courier;
padding-left: 5px;
}
#line-number {
color: #804000;
font-family: Arial;
font-size: 14px;
padding-right: 5px;
border-right: 1px dotted #804000;
}
blockquote[type=cite] {
padding: 0em .5em .5em .5em !important;
border-right: 2px solid blue !important;
border-left: 2px solid blue !important;
}
blockquote[type=cite]
blockquote[type=cite] {
border-right: 2px solid maroon !important;
border-left: 2px solid maroon !important;
}
blockquote[type=cite]
blockquote[type=cite]
blockquote[type=cite] {
border-right: 2px solid teal !important;
border-left: 2px solid teal !important;
}
blockquote[type=cite]
blockquote[type=cite]
blockquote[type=cite]
blockquote[type=cite] {
border-right: 2px solid purple !important;
border-left: 2px solid purple !important;
}
blockquote[type=cite]
blockquote[type=cite]
blockquote[type=cite]
blockquote[type=cite]
blockquote[type=cite] {
border-right: 2px solid green !important;
border-left: 2px solid green !important;
}
</style>
</head>
<body>
J&uuml;rgen Exner wrote:
<blockquote id="mid_3c0924d24c6597j2qgn7l88mqnhiohu5v6_4ax_com"
cite="mid:[email protected]" type="cite">Andrew
<blockquote id="StationeryCiteGenerated_1" type="cite">J&uuml;rgen Exner
wrote:<br>
<blockquote id="StationeryCiteGenerated_2" type="cite">And you
honestly believe your 120+ lines long signature (quoted in full below)<br>
</blockquote>
Looks like somebody needs to learn what a signature is...<br>
</blockquote>
<!---->Isn't it the part of a posting that is following the
dash-dash-blank line? And that is supposed to be 4 lines max?<br>
<br>
Well, your's over 120+ lines and contained pretty much no information
at all.<br>
</blockquote>
Sounds like somebody is still ignorant... I hope you eventually learn...<br>
<div class="moz-signature">-- <br>
<a href="http://defaria.com">Andrew DeFaria</a><br>
<small><font color="#999999">Don't be accommodating, be honest. I
honestly don't have much more time for anything else.</font></small>
</div>
</body>
</html>

--------------090207050102050807010007--

Bye bye, won't read you again

jue

 

[Andrew DeFaria]

Sounds like somebody is still ignorant

Yep, looks very much like it [Fullquote intentional]:

Of course it wasn't a very full quote now was it? Because you're were
missing the portion above the signature delimiter. Now had you looked at
that portion and read, and thought (or read another post here which
talked about this) you might have recognized that I post in what is
called multipart, MIME Multipart (<- look it up ya moron). So the
ignorance is still in your camp...

Bye bye, won't read you again

Is that a promise? Oh goodie!

For the record, I've often found people who claim to have killfiled but
who really don't or who "peak" in one way or another.

 

[Waylen Gumbal]

Isn't it the part of a posting that is following the dash-dash-blank
line? And that is supposed to be 4 lines max?

Well, your's over 120+ lines and contained pretty much no information
at all.

What are you talking about? His sig is only 2 lines long. Looks like
your reader is not setup correctly. If you don't want the text/html
portion then tell your reader you only want the text/plain only when it
encounters a multipart message.

 

[Martijn Lievaart]

It's a multiple format posting; one section for html and one for plain
text. If I set my reader to use plain-text only, I don't see html, just
the plain text version. Can your reader not be configured similarly? If
you only wish to see plain text then set your reader to display only
plain text and then you wont have a problem.

Well, there is this slight problem of standards, encoded into RFCs. You
don't /have/ to follow them, but it's in general a good idea. HTML in
usenet postings is definitely not standard and in fact, any serious
newsreader (on any platform, even on Windows) does not render it. Even
worse, mime-multipart, although a standard for mail, is not a standard
for usenet.

Andrew DeFaria chooses not to conform to the defacto standards on usenet.
That means almost everyone has killfiled him. Also, he has controversial
opinions (that's ok) and is sticking to them (that's not OK), is
generally trolling (that's also not OK), insulting regulars (that's a
definite nono) and is generally more wrong than right (I do that to, but
I admit my mistakes), posting in HTML is just one of the minor
annoyances. Couple his posting in HTML to the fact that no one else does
in technical newsgroups and my opninions stated above, you'll see why
generally people hate HTML on usenet.

If you post in HTML, there is a very high probability you are either a
troll, or a newbie. Newbies learn, Andrew doesn't. That alone places him
squarely in the trolling section. But please use Google to check his
posting history. It's fun.

Usenet does not do mime-multipart by definition. Any serious newsreader
does not do mime-multipart. Anyone who does use mime-multipart, after
being made aware that this is not standard and inconveniences anyone who
uses a real newsreader, is definitely anti social.

Oh and before you ask. What's a real newsreader? It varies greatly, but
generally has 1) very good filtering capabilities, 2) makes it easy to
follow threads, 3) has good scoring capabilities and 4) follows GNKSA
(http://www.gnksa.org/).

Be free to write a new RFC for Usenet. The old RFCs need an overhaul
anyway. But don't expect HTML to be adopted for Usenet, there is very
strong opposition to that.

HTH,
M4

 

[Jürgen Exner]

What are you talking about? His sig is only 2 lines long. Looks like
your reader is not setup correctly. If you don't want the text/html
portion then tell your reader you only want the text/plain only when it
encounters a multipart message.

Multipart is a feature of EMail. This here is Usenet. There is no such
thing as multipart in Usenet. Therefore anything following his signature
delimiter line must be his signature.

jue

 

[A. Sinan Unur]

What are you talking about? His sig is only 2 lines long.

Here, what counts is not what you see but what gets transmitted.

Looks like your reader is not setup correctly. If you don't want the
text/html portion then tell your reader you only want the text/plain
only when it encounters a multipart message.

Nope. Post plain text in text only groups.

Sinan

--
A. Sinan Unur <[email protected]>
(remove .invalid and reverse each component for email address)

comp.lang.perl.misc guidelines on the WWW:
http://www.rehabitation.com/clpmisc/

 

[szr]

Achim Peters wrote: [...]

Please stop posting HTML and/or MIME. TIA!

It's a multiple format posting; one section for html and one for
plain text. If I set my reader to use plain-text only, I don't see
html, just the plain text version. Can your reader not be configured
similarly? If you only wish to see plain text then set your reader
to display only plain text and then you wont have a problem.

Well, there is this slight problem of standards, encoded into RFCs.
You don't /have/ to follow them, but it's in general a good idea.
HTML in usenet postings is definitely not standard and in fact, any
serious newsreader (on any platform, even on Windows) does not render
it. Even worse, mime-multipart, although a standard for mail, is not
a standard for usenet.

I fully agree with you, so don't get me wrong here

I also think one has to take into account the time when those RFCs came
to be. In this day and age, is it really unreasonable for any modern
news reader to properly handle multipart postings (according to
configured preference) ? One might think that plain-text-only readers
(such as command-line-based clients like slrn) should be able to parse
the plain-text only, though I have never tested it with a multipart
message.

That said, UseNet is still thought of as a plain-text medium and
probably will be for a long time. But I think readers should be able to
properly handle multipart posts as it is virtually impossible to prevent
such postings from being made.

Any serious newsreader does not do mime-multipart.

This would seem to be the opposite from what I've seen. All the major
graphical ones (at least for Windows) to handle it, and they all allow
you to specify which format should take precedence. I have that set to
"plain", so I don't actually see the HTML part of a multipart posting

 

[Waylen Gumbal]

Multipart is a feature of EMail. This here is Usenet. There is no such
thing as multipart in Usenet.

I thought it's UseNet ? I've seen many people make this point before
around here so I'm surprised a regular would make such a slip. Any
newbie would be tarred and feathered to high hell by now for doing that.

Therefore anything following his signature delimiter line must be
his signature.

Only if you look at the raw source of the message, but looking at raw
source is not what you would do, right? No, you would look at the
plain-text part of the message, which has a sig 2 lines long.