SQL join help

[Steve Workings]

Re: SQL join help

Thanks all. Robert got it right - the table had a primary index that I was not filling. I broke through all sorts of barriers in understanding and working with SQL last night, so I think I'm on my way to some new stuff.

BTW, I'm putting together a system that I would hope would be very portable to other databases, and provide a nice set of management tools for multiple languages for a site.

[Steve Workings]

Re: SQL join help

Continuing this conversation:

I have this sql statement:

UPDATE lang_translation 
   SET lang_translation.translate = "test1"
 WHERE lang_translation.Translate_ID = "fbe8c0cb-2faa-4fdd-b6c0-1957d8a7cba7"

It works in Toad.

The exact same code fails in the grid event (I'm also successfully using the same grid event to insert records).

Here are some of the connection properties as shown in the A5 debugger:

{Code=0,Text=Success,NativeCode=0,NativeText=,Syntax=,API=,RowsAffected=0,Success=.T.,Error=.F.,Canceled=.F.,Warnings=.F.}

Yet the update fails.

Any suggestions?

[cavj1]

Re: SQL join help

Steve...

I've recently upgraded to the latest build and started getting the same error. In the build I was using the following worked on an AfterInsertRecord Event

dim conn as SQL::Connection
conn.open("::Name::arcapp")
v1 = e.DataSubmitted.DD_CARRIER1
v2 = e.datasubmitted.DD_CARRIER2
v3 = e.datasubmitted.DD_LINK_FK
v4 = e.datasubmitted.DD_CARRIER3
v5 = e.datasubmitted.DEC_DATE1
v6 = e.datasubmitted.DEC_DATE2
v7 = e.datasubmitted.DEC_DATE3
v8 = e.datasubmitted.DEC_BY1
v9 = e.datasubmitted.DEC_BY2
v10 = e.datasubmitted.DEC_BY3
v11 = e.datasubmitted.Phone1
v12 = e.datasubmitted.Phone2
v13 = e.datasubmitted.Phone3
v14 = e.datasubmitted.DEC_Reason1
v15 = e.datasubmitted.DEC_Reason2
v16 = e.datasubmitted.DEC_Reason3
sqlstatement = "insert into due_diligence (DD_CARRIER1, DD_CARRIER2, DD_LINK, DD_CARRIER3, DEC_DATE1, DEC_DATE2, DEC_DATE3, DEC_BY1, DEC_BY2, DEC_BY3, PHONE1, PHONE2, PHONE3, DEC_REASON1, DEC_REASON2, DEC_REASON3) Values('"+v1+"', '"+v2+"', '"+v3+"', '"+V4+"', '"+V5+"', '"+V6+"', '"+V7+"', '"+V8+"', '"+V9+"', '"+V10+"', '"+V11+"', '"+V12+"', '"+v13+"', '"+V14+"', '"+V15+"', '"+V16+"')"
conn.Execute(sqlstatement)
conn.Close()
end function

I'm not sure if something changed in this build...I have to look at the release notes. BTW...my dB is MySQL.

Joe

[bea2701]

Re: SQL join help

on exact same table name both fieldtypes char(40), trans_id = prim key
towards a SQLSERVER

UPDATE lang_translation
SET translate = 'test1'
WHERE (translate_id = 'fbe8c0cb-2faa-4fdd-b6c0-1957d8a7cba7')

the update is succesfull, [ change from " to ' ]

[Steve Workings]

Re: SQL join help

I'm working with MySQL like Joe.

My Inserts using double quotes work fine.

I tried changing the double quotes to single quotes as suggested by Eric, but got errors about that.

So, no solution yet.

[cavj1]

Re: SQL join help

From release notes...not sure what we would need to change to make things work again...

Version 10.5 - Build 2972_3565 Jun 30 2010
Behavior Change

MySQL - AlphaDAO - Update and Delete Statements - Call Result - Previously, with MySQL, when you executed an UPDATE or DELETE statement and there were no records that satisfied the WHERE clause in the statement, the call result was .T. (and AffectedRows was, of course, 0). This was inconsistent with all other Databases. Therefore, the behavior of MySQL has been changed so that it is consistent with all other Databases.

Now, the call result if is .T. if matching records were found and the UPDATE or DELETE was executed. If no matching records were found, the call result is .F. and the error text is 'The database could not find the data you requested'.

To differentiate between a 'critical' error (such as invalid column name, invalid table name, no open connection, etc.) and a 'benign' error (no records match the WHERE clause), you can look at the call result text.

If the call result text starts with 'Database API specific error', the error is a 'critical' error. If the call result text is 'The database could not find the data you requested', then the error is simply that no records matched the WHERE clause.

[Steve Workings]

Re: SQL join help

In my first post about this particular problem, I didn't have a callresult error. Now I do:

{Code=28,Text=Error parsing SQL statement�� ,NativeCode=51,NativeText=line 2:29: expecting A5SQLTOKEN_...

Again, just trying to execute this:

	sqlStatement = <<%sql%
	UPDATE lang_translation 
	SET lang_translation.translate = "test8"
	WHERE lang_translation.Translate_ID = "fbe8c0cb-2faa-4fdd-b6c0-1957d8a7cba7"
	%sql%

[Steve Workings]

Re: SQL join help

Anyone have further thoughts on this update problem?

[workaholic06]

Re: SQL join help

Steve,

I did try to duplicate your table, and the running of the SQL and didn't have a problem here.

However, I'm not 100% sure on the table setup - can you send me your table, and the component that you're running your script from? I'll take a quick look.

Thanks,

Robert

[RMarmion]

Re: SQL join help

Everyone seems to agree that the SQL itself is OK - you have after all executed the code successfully directly on a SQL Server.
I haven't done any SQL calls from A5 so far, so not familiar with the <<%sql%......%sql "wrapper".
However, the error message doesn't look like an SQL error message, but more like an A5 message since it ends with "expecting A5SQLTOKEN_...". An SQL server would not be famliar with an A5SQLTOKEN... so it looks like it is an error detected in the A5 environment from which you are executing the script and the script isn't getting to the SQL server at all.
You could confirm that by running the MySQL Profiler which will show you all SQl statements which the server receives and executes.
HTH
-Rob

[Steve Workings]

Re: SQL join help

Figured out my SQL Update problem:

I was doing this, which worked in many places:

UPDATE lang_translation 
   SET lang_translation.translate = 'test1'
 WHERE lang_translation.Translate_ID = 'fbe8c0cb-2faa-4fdd-b6c0-1957d8a7cba7'

But this is what works in the grid event:

UPDATE lang_translation 
   SET translate = 'test1'
 WHERE Translate_ID = 'fbe8c0cb-2faa-4fdd-b6c0-1957d8a7cba7'

[bea2701]

Re: SQL join help

'Glad to here Steve its solved '.;)

[kingcarol]

Re: SQL join help

Figured out my SQL Update problem:

I was doing this, which worked in many places:

UPDATE lang_translation 
   SET lang_translation.translate = 'test1'
 WHERE lang_translation.Translate_ID = 'fbe8c0cb-2faa-4fdd-b6c0-1957d8a7cba7'

But this is what works in the grid event:

UPDATE lang_translation 
   SET translate = 'test1'
 WHERE Translate_ID = 'fbe8c0cb-2faa-4fdd-b6c0-1957d8a7cba7'

Steve, I know this is an old thread, but thanks for posting. This just solved the exact problem I was having.
I have been using the update code with the table name followed by period in your first example and it had been fine until now.
This time I was calling the xbasic function from server side event afterUpdateRecord and it kept failing until I removed the table name and period from in front of field names (thanks to your post... I would never have thought of that since it had been working elsewhere). It seems completely strange to me that the syntax would work in some places, but not others???? Is that an a5 bug or is it a matter of where the Xbasic code is called from?

[kingcarol]

Re: SQL join help

Just thought I'd share what Selwyn just told me about the above:

PER SELWYN: no, it is not a bug
since an update can by definition (at least in portable sql, and in most sql databases), only update a single column, it is not necessary to qualify the fields in the 'SET' clause.

it is OK, however, to qualify the fields in the WHERE clause if you want (although again, unnecessary in this case since there is only a single table and therefore there is no ambiguity).

note that if you turn portable sql off, your update will work