[asterisk-users] create table in mysql using asterisk

[A J Stiles]

On Monday 09 January 2012, Danny Nicholas wrote:
> O.P. doesn't state his Asterisk version, but in 10.0(beta) I had a similar
> problem where sqlite3 couldn't create the new Asterisk DB.  From what I
> read in the archives, we really could use a guru to thoroughly "pound"
> these DB statements to make them a bit more bullet-proof.
> 
> -----Original Message-----
> From: asterisk-users-bounces at lists.digium.com
> [mailto:asterisk-users-bounces at lists.digium.com] On Behalf Of Tony
> Mountifield
> Sent: Monday, January 09, 2012 3:42 AM
> To: asterisk-users at lists.digium.com
> Subject: Re: [asterisk-users] create table in mysql using asterisk
> 
> In article <ACF1979B7D3CA54089C1ABDA3528B1F901DBC39A at media2.media.ltd>,
> 
> Eyal <eyal at mcr-m.com> wrote:
> > I try to create a new table using MYSQL command in asterisk.
> > This is what i write:
> > Query resultid ${connid} CREATE TABLE IF NOT EXISTS "conference_600"
> > ("id" int(11) NOT NULL auto_increment, "channel_id" varchar(40),
> > "number_in_line" int(2), PRIMARY KEY("id")") and this is the warning
> > that i get in the cli:
> > app_addon_sql_mysql.c:383 aMYSQL_query: aMYSQL_query: mysql_query
> > failed. Error: 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 '"conference_600" ("id" int(11) NOT NULL auto_increment,
> > "channel_id" varchar(40)' at line 1
> > 
> > What is the problem do you think?
> > Do I in the direction or have a completely different way to do this?
> 
> Yes, you need to solve your problem in a completely different way.
> 
> You appear to be wanting to create a new table for each conference, with
> the table containing a row for each channel that is a member of the
> conference.
> 
> You don't need to do that at all. Just have a single table, with an
> additional column containing your conference number. Then you only need to
> create the table once, outside of Asterisk, and within Asterisk you only
> need to write rows into the table, putting the conference number into the
> additional column instead of using it as part of the table name.

That's because your syntax is wrong.

Strict ANSI SQL specifies 'single speech marks' around values, and no reserved 
words in field names.

There are two non-standard extensions that have grown up since then.

PostgreSQL allows you to use "double speech marks" around a field name with a 
reserved word in it  (like your example above).

MySQL accepts "double speech marks" around *values*  as an alternative to 
'single speech marks'.  If you need to use a reserved word in a field name, you 
need to use `backward speech marks`  (old-fashioned shorthand for command 
output)  around the field name.

-- 
AJS

Answers come *after* questions.