List:MySQL++« Previous MessageNext Message »
From:Adam Nielsen Date:February 1 2010 3:18am
Subject:Re: How to do a large template query?
View as plain text  
> 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.
Thread
How to do a large template query?Adam Nielsen27 Jan
  • Re: How to do a large template query?Warren Young27 Jan
    • Re: How to do a large template query?Adam Nielsen28 Jan
      • Re: How to do a large template query?Joel Fielder28 Jan
        • Re: How to do a large template query?Warren Young28 Jan
          • Re: How to do a large template query?Adam Nielsen29 Jan
            • Re: How to do a large template query?Joel Fielder29 Jan
              • Re: How to do a large template query?Joel Fielder29 Jan
              • Re: How to do a large template query?Adam Nielsen1 Feb
            • Re: How to do a large template query?Warren Young29 Jan
              • Re: How to do a large template query?Adam Nielsen1 Feb
                • Re: How to do a large template query?Warren Young1 Feb
                  • Re: How to do a large template query?Warren Young7 Feb
          • Re: How to do a large template query?Warren Young7 Feb