> As Joel pointed out, you don't *have* to use template queries or
> SSQLS. Those are just convenience mechanisms. MySQL++ doesn't have
> problems with large column counts when you provide the SQL to it, or
> in dealing with wide replies from the server. The stock
> configuration just can't generate such things internally.
True, but like I said in the reply to Joel, I'm paranoid and don't trust
escaping mechanisms. Aside from that, if I'm continually asking the
server to parse large SQL statements I'm sure it's going to slow things
down.
> You're assuming that I have an instance of every compiler/OS/CPU
> combination MySQL++ supports, and test on every one of them before
> releasing. I don't know of any open source project that works that
> way. It would be rather impractical, given how many combinations
> there are.
Of course, but my point was "it might break these compilers" can't
really be used as an argument against a new feature, when those same
compilers aren't tested with day to day changes that could just as
easily break things.
> That depends on the nature of the problem, now doesn't it? Since
> almost the entire contents of ssqls.h are preprocessor directives, if
> the bug in these compilers is due to overrunning some buffer for
> holding preprocessor code, it won't matter that some of it is ifdef'd
> out. It'll still be megs of macros either way.
Hmm, that's a fair point. Still, I'm not sure that an old compiler
nobody has bothered to fix should be allowed to stifle innovation in a
useful library like mysql++.
> You know, this is the first time someone's actually offered a
> substantial response to my challenge to their schema design! I've
> been using that argument for years.
>
> I *am* willing to be swayed, but every previous time when I've said
> "show me your schema" people just go away, and I can't tell whether
> that's because I won the argument or they just went away.
>
> If your table's CREATE statement is human-readable (i.e. column names
> that make sense to outsiders, etc.) I'd love to see it. You can send
> it to my email address only, if you feel it's proprietary
> information.
Well it's not really human readable, but in the interest of discussion
here is a straightforward example that covers the data I am wanting to
retrieve:
CREATE TABLE `books` (
`id` INT NOT NULL,
`status` INT NOT NULL,
`object_type` INT NOT NULL,
`depositor` VARCHAR(255) NOT NULL,
`depositor_affiliation` VARCHAR(255),
`created_date` DATE NOT NULL,
`updated_date` DATE NOT NULL,
`title` TEXT NOT NULL,
`formatted_title` TEXT NOT NULL,
`description` TEXT NOT NULL,
`abstract` TEXT NOT NULL,
`citation` TEXT NOT NULL,
`genre` VARCHAR(255) NOT NULL,
`collection_year` DATE,
`publication_year` DATE NOT NULL,
`date_available` DATE,
`total_pages` INT NOT NULL,
`total_chapters` INT,
`notes` TEXT NOT NULL,
`publisher` VARCHAR(255) NOT NULL,
`place_of_publication` VARCHAR(255) NOT NULL,
`country_of_issue` VARCHAR(255) NOT NULL,
`edition_number` VARCHAR(255),
`issue_number` VARCHAR(255),
`volume_number` VARCHAR(255),
`series` VARCHAR(255),
`sequence` INT NOT NULL,
`language` VARCHAR(3) NOT NULL,
`language_of_title` VARCHAR(3),
`translated_title` TEXT,
`phonetic_title` TEXT,
`isbn` VARCHAR(20) NOT NULL,
`is_available` CHAR(1) NOT NULL,
`is_sensitive` CHAR(1) NOT NULL,
`is_refereed` CHAR(1) NOT NULL,
`scopus_id` VARCHAR(255),
`thomson_id` VARCHAR(255),
`download_count` INT,
`view_count` INT,
PRIMARY KEY (`id`)
);
I'm sure the list could be longer depending on how much detail someone
wanted to gather about a single item (font, colour of cover, mood of
person doing data entry, etc.) I guess the issue is not whether these
fields are relevant, but more about what to do when you wish to collect
so much detail about a specific item.
>> At any rate this system needs to be finished in a couple of days
>
> Why are you arguing about it, then?
Because I might want to use mysql++ on my next project :-)
>> I'll have to try another library this time
>
> If you do, I'd be curious to know what you find. I don't know of
> another C++ wrapper for MySQL++ that can generate 40+ column SQL
> statements for you and is likely to be found in your OS's package
> repositories already. If there *is* another, I'd like to know what
> it is.
Well in this case I went back to the MySQL C library, as its prepared
statements are exactly what I am after. Generate the query and parse it
once, then use bind variables to provide different data for each row.
No messy escaping mechanisms and should be very fast.
As to the C++ side, I have previously used the OTL library for accessing
Oracle. I believe it supports MySQL, but I didn't have enough time to
test that for this project. (I originally used it for Oracle because I
couldn't figure out the native Oracle API, and found it nice enough to
stick with.) It supports Oracle's version of "prepared statements" in
(IMHO) a very nice C++ manner, for example:
otl_stream query("INSERT INTO table (name, age) VALUES (:name<int>,
:age<char[255]>)");
std::string name = "John";
int age = 30;
query << name << age;
query << "Max";
query << 40;
The query runs each time the last column is provided, and it means you
can stick it in a loop, adding one column value to the query in each
iteration, should all your fields be in an array. It's available from
SourceForge and is just a .h file you #include in your code. Apparently
everything expands to near enough native API calls.
http://otl.sourceforge.net/otl3.htm
Cheers,
Adam.
