Problems with sqlrecord_create

This is the place to post technical queries about SQL Yoga

Moderators: FourthWorld, heatherlaine, Klaus, robinmiller, trevordevore

Post Reply
trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 1005
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Problems with sqlrecord_create

Post by trevordevore » Fri Oct 11, 2013 2:11 pm

[This post is an archive from the old SQL Yoga forums]

I am having a syntax problem come up when trying to use sqlrecord_create.

Below is:

1) the error its throwing

2) the sql the plugin is logging

3) A list of my db Fields and Datatypes

4) the routine in question

5) a printkeys() of the array being used to set the values.

I just cant seem to find where the syntax problems are.

Help?

The Error:

Code: Select all


sqlyoga_executesql_err,0,0,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 'Range,InsLocNo,FSANo) VALUES("17-117-","RJD Farm","LippoldGene2010","Alderson-Cr' at line 1 (INSERT INTO tleasefields (County,CropInsID,LeaseName,FieldName,AcresCrops,Section,AcresGIS,TownshipNumber,TownshipName,Range,InsLocNo,FSANo) VALUES(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12))

sqlyoga_executesql_err,0,0,dbconn_executeWBindings (3040)

sqlyoga_executesql_err,0,0,stack "C:/Rev/FOM/components/sql_yoga.rev"

sqlyoga_executesql_err,0,0,_InsertRecordIntoTable (6201)

sqlyoga_executesql_err,0,0,stack "C:/Rev/FOM/components/sql_yoga.rev"

sqlyoga_executesql_err,0,0,sqlrecord_create (6091)

sqlyoga_executesql_err,0,0,stack "C:/Rev/FOM/components/sql_yoga.rev"

sqlyoga_executesql_err,371,0,AddRecord

sqlyoga_executesql_err,371,0,card id 22837 of stack "Edit Fields" of stack "C:/Rev/FOM/components/LeaseStack.rev"

sqlyoga_executesql_err,4,0,mouseUp

353,4,0,button id 23074 of card id 22837 of stack "Edit Fields" of stack "C:/Rev/FOM/components/LeaseStack.rev"

The SQL Being Output:

Code: Select all


----------

START TRANSACTION

----------

INSERT INTO tleasefields (County,CropInsID,LeaseName,FieldName,AcresCrops,Section,AcresGIS,TownshipNumber,TownshipName,Range,InsLocNo,FSANo) VALUES(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12)

----------

ROLLBACK

----------

the DB FieldList:

Code: Select all


FIELD TYPE COLLATION NULL KEY DEFAULT Extra

-------------- ----------- --------------- ------ ------ ------- --------------

LeaseID INT(11) (NULL) NO PRI (NULL) AUTO_INCREMENT

LeaseName VARCHAR(50) utf8_general_ci NO MUL (NULL)

FieldName VARCHAR(40) utf8_general_ci NO MUL (NULL)

AcresGIS FLOAT (NULL) NO (NULL)

AcresCrops FLOAT (NULL) NO (NULL)

County VARCHAR(15) utf8_general_ci NO MUL (NULL)

FSANo VARCHAR(15) utf8_general_ci NO MUL (NULL)

TownshipName VARCHAR(50) utf8_general_ci NO MUL (NULL)

TownshipNumber VARCHAR(15) utf8_general_ci NO (NULL)

Section SMALLINT(5) (NULL) NO (NULL)

RANGE VARCHAR(15) utf8_general_ci NO (NULL)

CropInsID VARCHAR(40) utf8_general_ci YES MUL (NULL)

InsLocNo INT(10) (NULL) YES (NULL)

My Source:

Code: Select all


on AddRecord

try

put sqlrecord_createObject("tleasefields") into theRecordA

put FieldsToArray() into theValuesA -- Returns an Array with the to put into DB.

put theActiveLease into field "fLeaseName"

sqlrecord_set theRecordA, "LeaseName", theActiveLease

sqlrecord_set theRecordA, "FieldName", theValuesA["FieldName"]

sqlrecord_set theRecordA, "AcresGIS", theValuesA["AcresGIS"]

sqlrecord_set theRecordA, "AcresCrops", theValuesA["AcresCrops"]

sqlrecord_set theRecordA, "County", theValuesA["County"]

sqlrecord_set theRecordA, "FSANo", theValuesA["FSANo"]

sqlrecord_set theRecordA, "TownshipName", theValuesA["TownshipName"]

sqlrecord_set theRecordA, "TownshipNumber", 10

sqlrecord_set theRecordA, "Section", theValuesA["Section"]

sqlrecord_set theRecordA, "RANGE", theValuesA["Range"]

sqlrecord_set theRecordA, "CropInsID", theValuesA["CropInsID"]

sqlrecord_set theRecordA, "InsLocNo", "NULL"

answer printkeys(theRecordA)

sqlrecord_create theRecordA

put the result into theError

put it into theAffectedRows

catch e

put e into field "test"

end try

end AddRecord

PrintKeys of theRecordA:

@table: tleasefields

@passthru: LeaseID

County: 17-117-

CropInsID: RJD Farm

LeaseName: LippoldGene2010

FieldName: Alderson-Crews

AcresCrops: 27.0

Section: 27

@connection: development

AcresGIS: 27.0

@database: default

TownshipNumber: 10

TownshipName: Carlinville

Range:

InsLocNo: NULL

FSANo: 17-039-00000

LeaseID: NULL
Trevor DeVore
ScreenSteps - https://www.screensteps.com

LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 1005
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: Problems with sqlrecord_create

Post by trevordevore » Fri Oct 11, 2013 2:11 pm

'Range' is a reserved word in MySQL 5.1 and you have a field named "Range". I bet MySQL is complaining about that in your query.

You can either change your database schema or turn on the 'quote identifiers' property for the database connection.

Code: Select all

dbconn_set "quote identifiers", true
http://www.bluemangolearning.com/revolu ... nn_set.htm
Trevor DeVore
ScreenSteps - https://www.screensteps.com

LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 1005
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: Problems with sqlrecord_create

Post by trevordevore » Fri Oct 11, 2013 2:11 pm

Ohhhh. That explains a lot. I was getting pretty frustrated there!

Thanks!

Andrew
Trevor DeVore
ScreenSteps - https://www.screensteps.com

LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder

Post Reply

Return to “SQL Yoga”