dumb question?

Hi,

I started with lasso 3.x & FMP, jumped to lasso 8.5 with SQLlite (and loved it), now need to work with MySQL and Laso 8.6 (on Mac mini with Snow Leopard server 10.6.7 ). I am not a programmer but quite familiar with the traditional encoding when using lasso. Now the question:

I created the DB on MySQL 5.0x that came with the server and can access it using ODBC and FMP for example ; no problems in creating records etc. BUT when using the traditional code of Lasso [inline][/inline] to simply add a single simple record it returned this:

(error code) 1064
(error message) HY000 [Actual][MySQL] 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 '"DMPPRuser") VALUES ('MYNAME')' at line 1

The dead simple code I used is (mysql is on another machine):

[var:'Vuser'=(form_param:'somefield')]

[inline:
-Host=(Array:-Datasource='odbc', -Name='whatever', -Username='xxx', -Password='yyy'),
-database='DBname', -table='DBtable', 'fieldname'=$Vuser, -add]


then I found this from Fletcher (http://lasso.2283332.n4.nabble.com/What-s-wrong-with-my-sql -INSERT-syntax-td3107790.html ) [OK, that was back in 2006]:

{ ..... Or, you can use a traditional inline to do the same thing. Lasso handles the quoting and encoding for you.

[inline: -database='...', -table='events', -add, 'e_date'=$e_date, 'e_time'=$e_time, 'eventname'=$event_name, 'place'=$place, 'agenda'=$agenda, 'contact'=$contact, 'email'=$email, 'phone'=$phone, 'calendar'=$calendar, 'details'=$details]
[/inline]

.....}

Now I checked with another lasso user but running Lasso version 8.1.0 and he is using the same 'traditional' approach with no problems. But I am using the mysql that came with Snow Leopard Server 10.6.7 with Lasso 8.6 , it looks like the MySQL is version 5.0x .

I need to keep it simple, and am looking for a plain traditional approach ...


Thanks





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: dumb question?

On Wed, 2011-07-06 at 07:15 -0700, XL Cordemans wrote:
> (error code) 1064
> (error message) HY000 [Actual][MySQL] 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 '"DMPPRuser") VALUES ('MYNAME')' at line 1

You'll need to enable ANSI_QUOTES to be able the use '"' as an
identifier quote character rather then MySQL's default '`'.

See:
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html# sqlmode_ansi_quotes

This can be done in the server configuration, so no client/lasso
alterations are necessary.
--
Rik


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: dumb question?

On Wed, 2011-07-06 at 16:27 +0200, Rik Wasmus wrote:
> On Wed, 2011-07-06 at 07:15 -0700, XL Cordemans wrote:
> > (error code) 1064
> > (error message) HY000 [Actual][MySQL] 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 '"DMPPRuser") VALUES ('MYNAME')' at line 1
>
> You'll need to enable ANSI_QUOTES to be able the use '"' as an
> identifier quote character rather then MySQL's default '`'.
>
> See:
> http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html# sqlmode_ansi_quotes

Oh, and if possible, go for even more standards-compliant:
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html# sqlmode_ansi

--
Rik



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: dumb question?

--_2c8fa3c0-0bb6-48c0-baa4-b3fe4374ba6a_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


Rik and Crew
String values are always ticked VALUES('MYNAME') unless ANSI_QUOTES are ena=
bled
Column names are never surrounded by ticks or double quotes so
TABLE table_name
FOO VARCHAR(30)

where the INSERT statement for table table_name column FOO would look like:
INSERT INTO table_name FOO VALUES('VALUES ARE ALWAYS SURROUNDED BY TICK MAR=
KS UNLESS ANSI_QUOTES ARE ENABLED')=3B

Bedankt=2C
Martin

> Subject: Re: dumb question?
> From: rik.wasmus@grib.nl
> To: mysql@lists.mysql.com
> Date: Wed=2C 6 Jul 2011 16:39:16 +0200
>=20
> On Wed=2C 2011-07-06 at 16:27 +0200=2C Rik Wasmus wrote:
> > On Wed=2C 2011-07-06 at 07:15 -0700=2C XL Cordemans wrote:
> > > (error code) 1064
> > > (error message) HY000 [Actual][MySQL] You have an error in your SQL s=
yntax=3B check the manual that corresponds to your MySQL server version for=
the right syntax to use near '"DMPPRuser") VALUES ('MYNAME')' at line 1=20
> >=20
> > You'll need to enable ANSI_QUOTES to be able the use '"' as an
> > identifier quote character rather then MySQL's default '`'.
> >=20
> > See:
> > http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html# sqlmode_ans=
i_quotes
>=20
> Oh=2C and if possible=2C go for even more standards-compliant:
> http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html# sqlmode_ansi
>=20
> --=20
> Rik
>=20
>=20
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmgainty@hotmail.c=
om
>=20
=

--_2c8fa3c0-0bb6-48c0-baa4-b3fe4374ba6a_--

RE: dumb question?

On Wed, 2011-07-06 at 11:03 -0400, Martin Gainty wrote:
> Rik and Crew

Please keep your replies to the list only, I don't need to double on
e-mail...

> String values are always ticked VALUES('MYNAME') unless ANSI_QUOTES are enabled
> Column names are never surrounded by ticks or double quotes

They surely can, and in this case, are.

> so
> TABLE table_name
> FOO VARCHAR(30)
>
> where the INSERT statement for table table_name column FOO would look like:
> INSERT INTO table_name FOO VALUES('VALUES ARE ALWAYS SURROUNDED BY TICK MARKS UNLESS ANSI_QUOTES ARE ENABLED');

May I quote:

> > > (error code) 1064
> > > > (error message) HY000 [Actual][MySQL] 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 '"DMPPRuser") VALUES ('MYNAME')' at line 1

See the double quotes around the column name? Aye? 'nuff said.

> Bedankt,
> Martin

Graag gedaan,
--
Rik


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: dumb question?

Goede morgen, and thank you for your suggestion.=0AI am actually wond=
ering if the difference between lasso 8.1 and 8.6 is so big that traditiona=
l lasso code can not be used when connecting w/ MySQL ? You mentioned =
"... This can be done in the server configuration, so no alterations are ne=
cessary ...". Do you mean one of the mode that came with lasso (Server, sit=
e) to resume may I ask you to confirm: the simple code [inline:-add, -=
database=3D..., -table=3D..., field=3Dvalue] can not be used anymore w/ MyS=
QL, or can it? Alvast Bedankt =0A--- On Wed, 7/6/11, Rik Wa=
smus wrote: > From: Rik Wasmus l>=0A> Subject: Re: dumb question?=0A> To: mysql@lists.mysql.com=0A> Date: =
Wednesday, July 6, 2011, 10:27 PM=0A> On Wed, 2011-07-06 at 07:15 -0700, XL=
=0A> Cordemans wrote:=0A> > (error code) 1064=0A> > (error message) HY000 [=
Actual][MySQL] You have an=0A> error in your SQL syntax; check the manual t=
hat corresponds=0A> to your MySQL server version for the right syntax to us=
e=0A> near '"DMPPRuser") VALUES ('MYNAME')' at line 1 =0A> =0A> You'll need=
to enable ANSI_QUOTES to be able the use '"' as=0A> an=0A> identifier quot=
e character rather then MySQL's default=0A> '`'.=0A> =0A> See:=0A> http://d=
ev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_ ansi_quotes=0A>=
=0A> This can be done in the server configuration, so no=0A> client/lasso=
=0A> alterations are necessary.=0A> -- =0A> Rik=0A> =0A> =0A> -- =0A> MySQL=
General Mailing List=0A> For list archives: http://lists.mysql.com/mysql=
=0A> To unsubscribe:=A0 =A0 http://lists.mysql.com/mysql?unsub=3Dlunanxl@ya=
hoo.com=0A> =0A>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: dumb question?

>>>> 2011/07/06 23:56 -0700, XL Cordemans >>>>
Goede morgen, and thank you for your suggestion. I am actually wondering if the difference between lasso 8.1 and 8.6 is so big that traditional lasso code can not be used when connecting w/ MySQL ? You mentioned "... This can be done in the server configuration, so no alterations are necessary ...".
<<<<<<<<
This mode is set in "my.cnf" (under Windows "my.ini"), found in one of a variety of standard places, in the variable "sql-mode", say

sql-mode="ANSI,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_EN GINE_SUBSTITUTION"

.. The mode in question is "ANSI". There is always a copy of this file in the directory into which the program MySQL was installed, but that is not the first place where it is sought. Under Linux, say, "/etc/my.cnf" takes precedence.

Quote from help:

On Windows, MySQL programs read startup options from the following files, in the specified order (top items are used first).

File Name Purpose
WINDIR\my.ini, WINDIR\my.cnf Global options
C:\my.ini, C:\my.cnf Global options
INSTALLDIR\my.ini, INSTALLDIR\my.cnf Global options


On Unix, Linux and Mac OS X, MySQL programs read startup options from the following files, in the specified order (top items are used first).

File Name Purpose
/etc/my.cnf Global options
/etc/mysql/my.cnf Global options
SYSCONFDIR/my.cnf Global options
$MYSQL_HOME/my.cnf Server-specific options
defaults-extra-file The file specified with --defaults-extra-file=path, if any
~/.my.cnf User-specific options

~ represents the current user's home directory (the value of $HOME).

SYSCONFDIR represents the directory specified with the SYSCONFDIR option to CMake when MySQL was built. By default, this is the etc directory located under the compiled-in installation directory.

MYSQL_HOME is an environment variable containing the path to the directory in which the server-specific my.cnf file resides. If MYSQL_HOME is not set and you start the server using the mysqld_safe program, mysqld_safe attempts to set MYSQL_HOME as follows:

Let BASEDIR and DATADIR represent the path names of the MySQL base directory and data directory, respectively.

If there is a my.cnf file in DATADIR but not in BASEDIR, mysqld_safe sets MYSQL_HOME to DATADIR.

Otherwise, if MYSQL_HOME is not set and there is no my.cnf file in DATADIR, mysqld_safe sets MYSQL_HOME to BASEDIR.

In MySQL 5.5, use of DATADIR as the location for my.cnf is deprecated.

Typically, DATADIR is /usr/local/mysql/data for a binary installation or /usr/local/var for a source installation. Note that this is the data directory location that was specified at configuration time, not the one specified with the --datadir option when mysqld starts. Use of --datadir at runtime has no effect on where the server looks for option files, because it looks for them before processing any options.

MySQL looks for option files in the order just described and reads any that exist. If an option file that you want to use does not exist, create it with a plain text editor.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org