[Gambas-user] Issue 442 in gambas: SQL connection.Edit bug

[paulwheeler]

Benoît

Thank you for your reply, and thank you for your patience in dealing 
with people like me who are not full-time programmers.

Thank you for taking the time to help me understand.

Paul



As for your last line about reading the documentation. I DID read it. 
However, I was confused by these lines:
' Same as Select * from tblDEFAULT where id = [parameter value]
sCriteria = "id = &1"
iParameter = 1012

It was not clear to me how "iParameter" fit into the select statement 
above it, since it was not in the statement. It was also not obvious to 
me that you leave out the "where".

I usually associate "Request" with the fact that you are doing a "where" 
statement, so leaving it out did not occur to me. I know you mention 
that in this line of the code: "The second argument is the "where" part 
without the where keyword," but again, it did not click. Maybe it was 
the drugs from the knee replacement surgery a week ago, or the fact that 
I am trying to remember things I did for only a year 6 years ago. Either 
way, please accept the fact that I am trying and that I do actually read 
the instructions.


Now that I know what you were trying to say, may I suggest this 
alternate wording for the definition (feel free to use any, all or none 
of it):


Connection.Edit (gb.db)
Function Edit ( Table As String [ , Request As String, Arguments As , 
... ] ) As Result
Returns a read/write Result object used for editing records in the 
specified table.

This fill-in-the-blank function is the same as the following SQL 
statement, but with a twist:
"Select * from tblDEFAULT where id = [Value or Variable Name]"

The syntax is Edit(<table>, <request>, <optional argument>)

Table (tblDEFAULT) is the only required input. Request and Arguments are 
optional.

Request is the selection criteria (id = [Value or Variable Name]) part 
of a "WHERE" clause used for filtering the table. However, the "WHERE" 
keyword is omitted from the statement. So, our sql would now look like this:
"Select * from tblDEFAULT id = [Variable Name]"

The third value may seem strange, but allows you to write requests that 
are independant of the underlying database type. The arguments are 
quoted as needed by the SQL syntax, and substituted inside the Request 
string.

So, visually we now have a statement that may look like this:
"Select * from tblDEFAULT id = [Variable Name = iArgument]"

To actually plug in the values you would need these definitions:
sCriteria = "id = &1"    AND    iArgument = 1012

Finally your statement looks like this:
hResult = $hConn.Edit("tblDEFAULT", sCriteria, iArgument)



I also made minor changes to your sample code to reflect the above 
explanation:

Example
DIM hResult AS Result
DIM sCriteria AS String
DIM iArgument AS Integer

' Same as "Select * from tblDEFAULT where id = [Variable Name = iArgument]"
sCriteria = "id = &1"
iArgument = 1012

$hConn.Begin

hResult = $hConn.Edit("tblDEFAULT", sCriteria, iArgument)
' Set field value
hResult!Name = "Mr Smith"

' Update the value
hResult.Update
$hConn.Commit


On 06/20/2013 02:33 PM, gambas at ...2524... wrote:
> Updates:
> 	Status: WontFix
>
> Comment #1 on issue 442 by benoit.m... at ...626...: SQL connection.Edit bug
> http://code.google.com/p/gambas/issues/detail?id=442
>
> The Edit() method does not work that way.
>
> The syntax is Edit(<table>, <where request>)
>
> The first argument must be the name of the table.
> The second argument is the "where" part without the where keyword. It
> handles argument substitution.
>
> Please read the documentation of the Edit() method.
>