Posted by Darko on 11/08/07 14:45

On Nov 8, 2:28 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> Steve wrote:
> > "The Natural Philosopher" <a...@b.c> wrote in message
> >news:1194484441.81272.6@demeter.uk.clara.net...
> >> Darko wrote:
> >>> On Nov 7, 10:37 pm, "Steve" <no....@example.com> wrote:
> >>>> "Darko" <darko.maksimo...@gmail.com> wrote in message
>
> >>>>news:1194463439.305946.20240@z9g2000hsf.googlegroups.com...
>
> >>>>> On Nov 7, 6:19 pm, "Steve" <no....@example.com> wrote:
> >>>>>> well !!! lo-and-behold!!! when you get your error message back THIS
> >>>>>> time,
> >>>>>> you actually get a line number OTHER THAN 1 !!! now THAT would be
> >>>>>> helpful!
> >>>>>> imagine too, that you echo this out to the browser, copy it, and paste
> >>>>>> it
> >>>>>> directly into your mysql query browser...then execute it. even before
> >>>>>> then,
> >>>>>> you might have discovered (since you can now READ IT) that there is
> >>>>>> something wrong in the data you're inserting.
> >>>>> Having yelled that out, haven't you ever noticed that mysql (and so do
> >>>>> other
> >>>>> sql servers) specify precisely where the problem is - this time it
> >>>>> said:
> >>>>>> near 'from, size, format, cat, host ...
> >>>> you obviously haven't written very long or complex queries. 'near' and
> >>>> ON
> >>>> LINE x are *worlds* apart, now aren't they.
>
> >>>>> ... so it was quite clear that it had had problem with "from".
> >>>> apparently not quite as clear to the op. :)
>
> >>>>> Considering php and
> >>>>> queries code readability you are, of course, right, since a programmer
> >>>>> will much more
> >>>>> easily read the code formatted in the way you have, but considering
> >>>>> error information,
> >>>> you should ammend that...'considering the error information [IN THIS
> >>>> CASE]'.
>
> >>>> either way, it should be formatted as a rule...unless you're saying you
> >>>> can
> >>>> predict your errors, in which case you wouldn't make mistakes anyway.
>
> >>>>> sql servers are pretty precise about where the problem occurred, code
> >>>>> being indented
> >>>>> or not.
> >>>> really? which ones? what is 'pretty' precise?
>
> >>>> the indenting is multipurpose. it is my experience that the top 4 sql
> >>>> servers (ms sql, oracle, mysql, teradata) are generally *obtuse* in
> >>>> their
> >>>> error messages...but they all give line numbers!
>
> >>>>>> don't let me throw you on that one...bad data is NOT the problem here.
> >>>>>> there
> >>>>>> are things called RESERVED WORDS. one of those would be the word
> >>>>>> 'FROM'...as
> >>>>>> in "select * FROM". if you had correctly formatted your sql statement,
> >>>>>> the
> >>>>>> line number in error would have been line 6...a much better clue.
> >>>>> As for rude yelling about making mistakes with reserved words, that is
> >>>>> something that happens
> >>>>> to many people, even experienced, from time to time, so no need to get
> >>>>> upset about it.
> >>>> rude? lol.
>
> >>>> you even infer rudeness about the mistake itself. no, i capitalized FROM
> >>>> so
> >>>> that it stood out. if that hurt your ears, then you won't hear me
> >>>> laughing
> >>>> right now. my intention throughout the thread here has been to make a
> >>>> point
> >>>> about formatting. did you not notice that even though i told him what
> >>>> the
> >>>> problem was, i did not tell him how to fix it? hmmmm...must not have
> >>>> been
> >>>> the goal of my post. seems you've missed that point.
>
> >>>>> I once
> >>>>> named two variables in C like "od" and "do", and couldn't find out
> >>>>> what was wrong with it until
> >>>>> I realised it was the "do" keyword.
> >>>> christ almighty! i suppose you proliferate the use of variables like
> >>>> $tmp
> >>>> too. what a goof! 'do'? for the love of god, almost *every* language has
> >>>> a
> >>>> *do* loop construct. so, when you said, 'even experienced' above, you
> >>>> were
> >>>> not associating yourself among those. :)
>
> >>>>> Finally, it is not "reserved" word in any sql, as you can indeed name
> >>>>> any field "from", as long
> >>>>> as you make the parser know it. For an example, this is totally legal:
> >>>>> select name, img, descr, "from", size, format from table;
> >>>> why yes. now why would i NOT explain that to the op? must not have been
> >>>> the
> >>>> purpose of my post. what's more, i'd be encouraging BAD behavior. if you
> >>>> think that's just my ho, why don't you prepose that question in a db
> >>>> forum...bring your asbestos umbrella, cuz it'll rain fire from the first
> >>>> response to the last. dba's are kinda picky that way.
>
> >>>>> just as long as you keep the double quotes around key words.
> >>>> ahhhh...you assume too much. oracle will fart on your double quotes. it
> >>>> likes either single tics or single back tics (`). again, you just killed
> >>>> a
> >>>> great chance for scalability. you should be able to take your code base
> >>>> and
> >>>> plop it down in front of any db and nothing breaks. you've forced
> >>>> yourself
> >>>> to reprogram when switching from one db to another...which is the shits
> >>>> when
> >>>> you're prototyping on your local pc using mysql and pushing code to
> >>>> production where teradata is the db being used.
>
> >>>> wanna keep going, darko?
> >>> Yes, please.
>
> >>> It wasn't my intention to encourage Einstein30000 to use such field
> >>> names as "from" or "select",
> >>> the idea was only that such errors happen even to experienced
> >>> programmers, not indicating whether
> >>> I consider myself one or not - it's pretty relative thing, as you
> >>> know.
>
> >>> As for "od" and "do", you should first know that I am a Serb, and that
> >>> in Serbian language "od" means "from",
> >>> and "do" means "to", so "od 1 do 10" means "from 1 to 10". Thus, once
> >>> in a simple C program I needed such "from" and
> >>> "to" helper variables, and I named them "od" and "do". It would have
> >>> been much easier to avoid if I was writing in
> >>> English, which I usually do when making non-test programs, since then
> >>> it would be easier to "hear" it as the English
> >>> do. But, being switched to Serbian in my mind, I didn't see any danger
> >>> coming of it, and the
> >>> compiler was pretty vague about the error, as you know it can be, and
> >>> I hardly recognized it. This is,
> >>> if you'd really like to know.
>
> >>> As for yelling, your uppercasing "FROM" explanation doesn't mention
> >>> the "your sql statement is F.U.C.K.E.D", "well !!! lo-and-behold!!!",
> >>> "a line number OTHER THAN 1 !!! now THAT would be helpful! ", "since
> >>> you can now READ IT", "bad data is NOT the problem here. there are
> >>> things called RESERVED WORDS. " statements, which I normally
> >>> considered yelling. It's just not polite to address people like that,
> >>> especially ones that came for advice and help.
>
> >>> Regards,
> >>> Darko
>
> >> I'm with darko here. It took me about 5 seconds flat to realise what was
> >> wrong.
>
> >> No need to blow it across 15 lines.
>
> >> Unless you are the sort person who can't count beyond ten without taking
> >> their socks off.
>
> >> Mysql barfs where its parser gets confused..that was at the word 'from'
> >> Simple.
>
> > right. and no one is arguing the simple nature of identifying the problem
> > here. however, it becomes very difficult, not only to read, but to maintain
> > and debug sql statements that are not well formatted...which helps more
> > quickly identify the root cause when it is less than obvious.
>
> > i'm not for or against anyone. i'm for a systemic approach that covers all
> > the bases and is a best practice. that's all. it has little to do with the
> > actual problem faced here with reserved words.
>
> Sorry, Steve - I don't agree with your method of "properly formatting"
> the SQL. It takes way too much space on the page. It is not "correct"
> by virtually any programmer I know.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================

I must agree with you on this one. I do format my queries in code, but
it looks something like this:

$query = sprintf(
"INSERT INTO " .
"main " .
"(name, img, descr, from, size, format, cat, host, link, date)
" .
"VALUES " .
"('%s', '%s', '%s', '%s', %f, '%s', '%s', '%s', '%s', '%s')",
mysql_real_escape_string( $name ),
...
);

$queryResult = @mysql_query( $query );
if ( $queryResult === FALSE ) {
throw new Exception( "..." );
....

....with exception, of course, that I would try to avoid the "from"
sort of variable
names. This is pretty shorter, and still good enough to understand (at
least for me)

Darko

[Back to original message]