Any SQL experts here?

Jerry Stuckle <jstucklex.DeleteThis@attglobal.net> wrote:

> John Bokma wrote:
>> Jerry Stuckle <jstucklex.DeleteThis@attglobal.net> wrote:

[..]

>> I know (mysqli). Only how many applications *do* use those?
>>
>
> It depends on the programmer.

With PHP that is, sadly, often bad news.

>>> When did you update your PHP?
>>
>> Doesn't matter, no? I wrote that via emulation one could use prepared
>> statements long before MySQL 4.1 in Java, Perl, and several other
>> languages. PHP was the big exception IIRC.
>
> Of course it does. You can't use features which don't exist.

I was using prepared statements with MySQL before 4.1. (hint: emulation
(in the driver)). The developer(s) who made the original MySQL library
for PHP could have used some insight (so could have many other
developers working on PHP)

[ PHP ]

> So? That's WordPress's problem, not PHP's. They should get better
> programmers.

PHP's problem has always been that it's utterly confusing. Moreover, PHP
could push developers to stay away from the old mysql and move over to
proper abstraction (afaik there is already something wonderful like
that, something that has been available for many other languages for
like 10 years or so).

>> Nah, PHP has been badly designed from the start. Only recent versions
>> get close to a decent programming language, IMO of course.
>
> Bullshit.

OK, I have to go with your very strong argument. Anyway, anybody who has
been exposed to some programming languages will agree with me that PHP
is a mess.

>> Pfft, to me they are a tool. In PHP you have to use mysqli to get the
>> tool, which somewhat proves my point
>
> Fine. But not to the rest of the world.

I have no idea, nor do I care much.

>> Then explain the difference.
>
> I did.

Then I've missed it. Maybe you can quote it for me?

>> Hence: restricted gotos.
>
> Nope.

Another fine argument. Anyway, time for the personal attack I guess:

> John, your strong anti-PHP bias is well known,

I can at least built a case for it. Probably has something to do with my
experience with quite some programming languages.

> as are many of your other weird ideas.

Like?

> You jumped into the middle of a thread and keep changing
> the subject from the original.

About half of it is still on SQL. I didn't bring on the goto's.

> You even were all over SpaceGirl for trying to learn something new.

Maybe it's because I bump too often in code written by people who "learn
something new".

> IOW, you're just trolling.

Because I disagree with you?

> Get a life.

I have already one that is quite satisfying. I don't have time for
another one.

> I'm not going to respond to any
> more of your asinine comments.

IMO, so far you have failed to show me I am stupid or even wrong. You
also have failed to convince me that you're stupid, but you're getting
quite close.

--
John Bokma http://johnbokma.com/
 >> Stay informed about: Any SQL experts here? 

Jerry Stuckle <jstucklex.TakeThisOut@attglobal.net> wrote:

[ programs that work off the local clock instead of using GMT internally ]
> And how many applications actually do that?

I have no idea. You seem to have experience with "many commercial programs
out there" that don't, so maybe you can publish a list?

I've mostly experience with OSS applications on the server, and I can't
think of an example that didn't use GMT internally, and also offered the
user a way to specify a different timezone then the local one of the
server.

For each application I've written for (commercially), that did something
with time, I've used GMT internally.

--
John Bokma http://johnbokma.com/
 >> Stay informed about: Any SQL experts here? 

In <713e4877-f5ec-4bb1-95e3-b86bcd6329dd.DeleteThis@q21g2000hsa.googlegroups.com>,
SpaceGirl <nothespacegirlspam.DeleteThis@subhuman.net> mentions:
>I had an interesting chat with a web team person at the BBC last year.
>Their stats and fun end databases are MySQL5. If the BBC web site,
>which is one of the busiest in the world, can manage with MySQL, I'm
>sure it'll be more than fine for most people

Oh, yea for quantity and speed, I'd say mysql even though I think postgresql
is "better" in terms of referential integrity.

Where postgress really shines is setting up the initial tables and creating
rules for the data to be inserted. You can easily do stuff like set up restrictions
such as "This column must match this regex"

Mysql has constraints now, but, they depend on which storage engine is in
use. Same with transactions.

But, if I wanted sheer quantity (especially read-mostly data) and I didn't much
care about integrity on insert, mysql would probably be the way to go.

That being said.. I wonder if sqllite will encroach mysqls' turf. (I recently
did a demo for someone using sqllite and was amazed by what a tiny little
package could do)


Jamie
--
http://www.geniegate.com Custom web programming
Perl * Java * UNIX User Management Solutions
 >> Stay informed about: Any SQL experts here? 

nospam.DeleteThis@geniegate.com (Jamie) wrote:

>I recently did a demo for someone and was amazed by what a tiny little
>package could do

I know the feeling. <bg>

Matt


--
The Probert Encyclopaedia
http://www.probertencyclopaedia.com
 >> Stay informed about: Any SQL experts here? 

On Sun, 10 Feb 2008 16:36:55 +0000, Jamie writ:

> amazed by what a tiny little package could do

You sound like my wife. 0-o

--
"Because all you of Earth are idiots!"
¯`·.¸¸.·´¯`·-> freemont© <-·´¯`·.¸¸.·´¯
 >> Stay informed about: Any SQL experts here? 

On Fri, 8 Feb 2008 05:47:42 -0800 (PST), SpaceGirl put finger to
keyboard and typed:

>I had an interesting chat with a web team person at the BBC last year.
>Their stats and fun end databases are MySQL5. If the BBC web site,
>which is one of the busiest in the world, can manage with MySQL, I'm
>sure it'll be more than fine for most people
>
>I doubt they use MySQL behind the scenes though for the bulk of the
>content.

The following is correct to the best of my knowledge...

Despite the website appearing to be a monolithic entity, each
department of the BBC generates its own content using its own CMS and
the resulting pages are copied to the public server as static content.
With the exception of the discussion forums and online games, very
little of the content is generated dynamically. That's why, for
example, every story published on the BBC news website is still there
with the same URL that it was first published with. This allows
content generation to be distributed around the BBC, without any risks
caused by exposing multiple CMSs to the outside world - as I'm sure
you can imagine, such a site would be a prime target for anyone
wanting to exploit any security holes in any dynamic content
generation system.

The CMSs themselves vary from fully in-house code (eg, the BBC news
site) to customised or almost out of the box commercial and Open
Source software for other, smaller departments. These CMSs use either
Oracle or MySQL, depending on department, and the static pages they
generate are published to an Apache server.

The only major sections which generate dynamic content on the fly are
the discussion and feedback sections. The "Have Your Say" message
boards on the news site uses Java servlets on a Zeus server with an
Oracle backend. The sport discussion forum ("606") runs on IIS with an
MS SQL backend. Other user-facing dynamic content (eg, news polls) is
mostly written in Perl.

Mark
--
Blog: http://Mark.Goodge.co.uk Photos: http://www.goodge.co.uk
"Every whisper, every waking hour"
 >> Stay informed about: Any SQL experts here? 

On Tue, 5 Feb 2008 12:18:05 -0800 (PST), Andy Dingley put finger to
keyboard and typed:

>On 5 Feb, 18:23, "Beauregard T. Shagnasty"
><a.nony.m....DeleteThis@example.invalid> wrote:
>
>> http://www.phpfreaks.com/quickcode/Get_the_ID_of_Last_MySQL_Entry/20.php
>
>Eeewwww....
>
>If you're going to read the last value returned by an identity column,
>do it within SQL and do it within some sort of atomic transaction.

It is atomic. It merely takes two calls to retrieve the data.

I don't know why that's the case. I don't see why the return value of
the insert query can't be an array, with one value in the array being
the success/failure flag and the other being the auto-increment id. In
fact, I can't see why it isn't possible to have a single return value
which gives this information - a positive integer representing the
auto-increment value for a successful auto-increment insert, zero for
a successful non-incrementing insert and -1 for a failed insert
(although that would break the principle that a successful insert
returns a value that evaluates to "true", so it would be less than
optimal for that reason). But that's a database issue, and nothing to
do with any programming language used to access the data.

> The
>idea of setting it in one line of PHP and then reading it in another
>line is just icky. Who's to say another process hasn't incremented the
>thing in the meantime?

MySQL ensures that it hasn't incremented in the meantime. The data is
created atomically by the statement which generates the increment
value, but that data isn't returned to the same query - there's a
separate query which returns that data. The PHP function is just a
wrapper to that query.

Mark
--
http://www.good-stuff.co.uk - stuff, some of it good
"Emotions run deep as oceans"
 >> Stay informed about: Any SQL experts here? 

Mark Goodge wrote:
> On Tue, 5 Feb 2008 12:18:05 -0800 (PST), Andy Dingley put finger to
> keyboard and typed:
>
>> On 5 Feb, 18:23, "Beauregard T. Shagnasty"
>> <a.nony.m....TakeThisOut@example.invalid> wrote:
>>
>>> http://www.phpfreaks.com/quickcode/Get_the_ID_of_Last_MySQL_Entry/20.php
>> Eeewwww....
>>
>> If you're going to read the last value returned by an identity column,
>> do it within SQL and do it within some sort of atomic transaction.
>
> It is atomic. It merely takes two calls to retrieve the data.
>
> I don't know why that's the case. I don't see why the return value of
> the insert query can't be an array, with one value in the array being
> the success/failure flag and the other being the auto-increment id. In
> fact, I can't see why it isn't possible to have a single return value
> which gives this information - a positive integer representing the
> auto-increment value for a successful auto-increment insert, zero for
> a successful non-incrementing insert and -1 for a failed insert
> (although that would break the principle that a successful insert
> returns a value that evaluates to "true", so it would be less than
> optimal for that reason). But that's a database issue, and nothing to
> do with any programming language used to access the data.
>
>> The
>> idea of setting it in one line of PHP and then reading it in another
>> line is just icky. Who's to say another process hasn't incremented the
>> thing in the meantime?
>
> MySQL ensures that it hasn't incremented in the meantime. The data is
> created atomically by the statement which generates the increment
> value, but that data isn't returned to the same query - there's a
> separate query which returns that data. The PHP function is just a
> wrapper to that query.
>
> Mark

Mark,

As a matter of fact, MySQL does return the insert id to the local cache,
from which the next call fetches it.

Try an insert over the network and trace the traffic. You'll see the
insert go over, and the return from MySQL come back with the insert id
(which I assume is stored in the connection's data area). When the
request for the id is processed, there is no network traffic - just a
call to the client library. So there is very little overhead.

I suspect the reason they don't use arrays is because of what you
mentioned - true/false for go/nogo.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex.TakeThisOut@attglobal.net
==================
 >> Stay informed about: Any SQL experts here?