CMS Made Simple Forums

Posted: **Fri Jul 14, 2006 3:52 pm**

Here is the module install code:

                // Typical Database Initialization
		$db = &$this->cms->db;
		
		// mysql-specific, but ignored by other database
		$taboptarray = array('mysql' => 'TYPE=MyISAM');
		$dict = NewDataDictionary($db);
		
        // table schema description
        $flds = "
			id I AUTO KEY,
			job_title C";
	

		// create it. This should do error checking, but I'm a lazy sod.
		$sqlarray = $dict->CreateTableSQL(cms_db_prefix()."module_prosourcejobs",
				$flds, $taboptarray);
		$dict->ExecuteSQLArray($sqlarray);
               die( "<pre>".print_r( $sqlarray )."</pre>".mysql_error() );

I put the die in there to see why the table was NOT being created.

Here is what I get out when I install.

Array ( [0] => CREATE TABLE cms_module_prosourcejobs ( id INTEGER NOT NULL AUTO_INCREMENT, job_title VARCHAR, PRIMARY KEY (id) )TYPE=MyISAM )

1

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ' PRIMARY KEY (id) )TYPE=MyISAM' at line 3

Am I missing something? Shouldn't there be a ) before PRIMARY KEY?

Posted: **Fri Jul 14, 2006 7:34 pm**

brownrl wrote: Here is the module install code:
Code: Select all
...		
        // table schema description
        $flds = "
			id I AUTO KEY,
			job_title C";
...
I put the die in there to see why the table was NOT being created.

Here is what I get out when I install.

Array ( [0] => CREATE TABLE cms_module_prosourcejobs ( id INTEGER NOT NULL AUTO_INCREMENT, job_title VARCHAR, PRIMARY KEY (id) )TYPE=MyISAM )

1

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ' PRIMARY KEY (id) )TYPE=MyISAM' at line 3

Am I missing something? Shouldn't there be a ) before PRIMARY KEY?

It seems like most modules use something like:

Code: Select all

comment_id I KEY,

So it looks like they stay away from using "id" as a field name. Maybe switching to something like the following would work:

Code: Select all

...		
        // table schema description
        $flds = "
			job_id I KEY,
			job_title C";
...

Also instead of using "AUTO_INCREMENT" it's more portable to use something like this in the install code:

Code: Select all

  $db->CreateSequence(cms_db_prefix()."module_prosourcejobs_seq");

And then use something like this to get the value of the next auto increment:

Code: Select all

  $job_id = $db->GenID(cms_db_prefix()."module_prosourcejobs_seq");

I know that when exporting from Mysql 5 to Mysql 4 the autoincrement attribute is lost, has caused me pain with SMF as I have Mysql 5 on local test server and remote server has Mysql 4

And according the calguy1000:

some versions of postgres didn't have auto increment I think.

So it's better not to use auto increment.

I think most most (if not all modules use db->CreateSequence() & db->GenID() instead).

I hope this helps.

Posted: **Sun Jul 16, 2006 5:08 pm**

wasn't the id needing a job_ infront.

It was that on the adodb lite site they say that 'C' are varchars capped at 255. However, this is not the case. You need to have C(255) to explicitely cap the varchar at 255.

Not worried about portable, I LOVE MY AUTO INCREMENTS! CAN"T LIVE WITH OUT THEM.

People that use db's and not know the loveliness of auto increment are truly in the dark.

CMS Made Simple Forums

adodb lite has some issues maybe

adodb lite has some issues maybe

Re: adodb lite has some issues maybe

Re: adodb lite has some issues maybe