SQL Update & Database Connection

Hello,

I have an app that makes use of the Database Connection. So far,
Inserts, Deletes and Reads work fine. The problem I have is updating an
object to a database table. The object in question has 16 unique items;
most are text fields, while some contain instances of TimeStamp. Has
anyone had this problem? If so, how was it solved?

All my insert statements are prepared during app start up and are
parameterized, for example.

self prepare: 'Insert Into Accounts' (First_Name, Last_Name,
Birth_Date) values (?, ?, ?)').
parameters := (self dbConnection columns: Accounts).
self accountInsert paramCols: parameters.

PersistentAccountManager>>commitAccount: anAccount

self dbConnection exec: accountInsert

Can one use the same technique for an Update statement? Thus far, my
attempts have not been successful. And my SQL is a bit rusty. The 'U'
in CRUD transactions is not working... HELP!!!

Pax wrote:
Hello,

What I need to perform is an update to the account:

'Update Into Accounts set First_Name=value1 set Last_Name=value2 set
Birth_Date=value3 where Acct_Number=(anAccount accountNumber)'.

For some reason, I'm getting a syntax error for this sql statement.
Anybody know what might be causing the error? anAccount is passed in as
the argument. value1, value2 and value3 are attributes of anAccount:

value1 := anAccount firstName.
value2 := anAccount lastName.
value3 := anAccount lastChanged (TimeStamp current).

The database is in MS Access. Creates, Reads and Deletes work fine. I
must be doing something wrong here. Any one work with the Database
Connection package that had to create methods to Update database
tables?

Appreciate any/all input.

Thanks

Pax wrote:

> 'Update Into Accounts set First_Name=value1 set Last_Name=value2 set
> Birth_Date=value3 where Acct_Number=(anAccount accountNumber)'.

I think the SQL syntax for this should be:

    Update Accounts
        set First_Name=xxx, Last_Name=yyy, Birth_Date=zzz
        where Acct_Number=nnn

Since you are preparing parameterised update "queries", the string would be

    Update Accounts
        set First_Name=?, Last_Name=?, Birth_Date=?
        where Acct_Number=?

And then you have to bind vaues to the four '?'s in the same way as for inserts
etc.

    -- chris

Chris Uppal wrote:
> Pax wrote:
>
> > 'Update Into Accounts set First_Name=value1 set Last_Name=value2
set
> > Birth_Date=value3 where Acct_Number=(anAccount accountNumber)'.
>
> I think the SQL syntax for this should be:
>
>     Update Accounts
>         set First_Name=xxx, Last_Name=yyy, Birth_Date=zzz
>         where Acct_Number=nnn
>
> Since you are preparing parameterised update "queries", the string
would be
>
>     Update Accounts
>         set First_Name=?, Last_Name=?, Birth_Date=?
>         where Acct_Number=?
>
> And then you have to bind vaues to the four '?'s in the same way as
for inserts
> etc.
>
>     -- chris

Chris,

here is a copy of my code. I made some changes based on your
suggestion. Still not working; I must be doing something wrong here...
Maybe you can see whats wrong as I haven't figured it out yet.

initializeAccountUpdate

| parameters tableName |
self accountUpdate: (self dbConnection prepare: 'Update ', tableName,
'(set First_Name=?, set Last_Name=?, set Department=?, set
Account_Number=?, set Employee_Id=?, set Hire_Date=?, set
Termination_Date=? where Account_Number=?) values (?, ?, ?, ?, ?, ?, ?,
? )' ).
parameters := (self dbConnection columns: tableName).
self accountUpdate paramCols: parameters.

This method compiles fine, but now I'm getting Field Count Errors...
Maybe its due to the fact that there is a mis-match between the number
of fields and parameters. Should the where clause be inside the
statement as it is above or must it be moved outside of the statement?
Like I said, my SQL is very rusty.

Thanks,

Pax

> | parameters tableName |
> self accountUpdate: (self dbConnection prepare: 'Update ', tableName,
> '(set First_Name=?, set Last_Name=?, set Department=?, set
> Account_Number=?, set Employee_Id=?, set Hire_Date=?, set
> Termination_Date=? where Account_Number=?) values (?, ?, ?, ?, ?, ?, ?,
> ? )' ).
> parameters := (self dbConnection columns: tableName).
> self accountUpdate paramCols: parameters.



Way too many SET commands. There is a single SET in an UPDATE statement.

UPDATE <TABLE>
SET
    <Field1> = <NewValue1>,
    <Field2> = <NewValue2>,
    <Field3> = <NewValue3>,
    <Field4> = <NewValue4>,
    <Field5> = <NewValue5>,
    <Field6> = <NewValue6>
WHERE
    <FieldX> = <Condition>


Your statement is looking like this:

Update <tableName>
(set First_Name=?, set Last_Name=?, set Department=?, set
Account_Number=?, set Employee_Id=?, set Hire_Date=?, set
Termination_Date=? where Account_Number=?) values (?, ?, ?, ?, ?, ?, ?)

You have managed to mix in the syntax for an insert statement into an update
statement.

It should probably look like this:


UPDATE <tableName>
SET
    First_Name = ?,
    Last_Name = ?,
    Department = ?,
    Account_Number = ?,
    Employee_Id = ?,
    Hire_Date = ?,
    Termination_Date = ?
WHERE
    Account_Number=?

> This method compiles fine,

Since you mention it, that does not mean too much in Smalltalk.  It's
nice of course, but the real test is whether all of the objects
understand the messages sent to them, and (as in any language) whether
the logic is correct.  If your problem is what I think it is, a C*
program would similarly compile but would not work; it would also
include a lot more code :)

BTW, I recall the days when I was glad to see a method merely compile.
If it seems as though the compiler is not being of much help, please
keep in mind that it is likewise not getting in your way.


 > but now I'm getting Field Count Errors...
> Maybe its due to the fact that there is a mis-match between the number
> of fields and parameters. Should the where clause be inside the
> statement as it is above or must it be moved outside of the statement?
> Like I said, my SQL is very rusty.

I could be missing somnething, but have you cut down the
parameters/columns to just the fields in your SQL statement?  If all
columns are involved, then it probably will not matter.  I have a method
that does pretty much what you are doing (I think<g>) and then (with
careful attention to string/symbol and case snags) picks out the
required fields by name.  My code is an interesting mix of a loop and
#detect: that looks like it could more directly be written using
#select:, but I will fight that battle another day.

Have a good one,

Bill

--
Wilhelm K. Schwab, Ph.D.
[hidden email]

Pax wrote:

> Like I said, my SQL is very rusty.

Sean has already given you the correct syntax for a SQL update.  I want to add
a word of advice.  If you are messing with SQL (and are not totally fluent in
it) then its a good idea to test your SQL in some interactive environment
before you try to turn it into parameterised SQL.

Most databases have tools that allow you to type SQL in and see what happens,
but if you don't have easy access to such a tool then you can use Dolphin by
executing code in a workspace (using DBConnection>>exec: and
DBConnection>>query:).

SQL syntax is pretty weird, so -- unless you are equally weird ;-) -- you are
unlikely to be able to get the correct syntax just by guessing.  Most (all?)
databases come with decent tutorial material that will describe how to use SQL
for common cases (SQL is intended for use by non-technical people, after all --
which is a laugh).  So there should be something helpful either installed as
part of the DB or out on the Net somewhere.

BTW:

> parameters := (self dbConnection columns: tableName).

That may work, but only if the parameters (the '?'s) in your query are exactly
the same, and in the same order, as the columns in your table.

    -- chris

Chris,

> Most databases have tools that allow you to type SQL in and see what happens,
> but if you don't have easy access to such a tool then you can use Dolphin by
> executing code in a workspace (using DBConnection>>exec: and
> DBConnection>>query:).

Accss is particularly good at it.  One can design by drag and drop, get
the desired results, and then switch to the SQL view to obtain working
SQL.  There is one SNAFU to avoid.  A search of the archives will turn
up my story of switching to MySQL, which was greatly complicated by
Access' use of a HAVING clause for things that could (_should_) be
placed in the WHERE clause.  Access obviously pre-selected records to
work around it, but MySQL did not.  The result was hideous performance.
  Is Access correct to optimize the query?  Probably, but there is no
argument that the SQL Access generated was poorly designed.


> SQL syntax is pretty weird, so -- unless you are equally weird ;-) -- you are
> unlikely to be able to get the correct syntax just by guessing.  Most (all?)
> databases come with decent tutorial material that will describe how to use SQL
> for common cases (SQL is intended for use by non-technical people, after all --
> which is a laugh).  

Designed for a non-technical audience - no wonder it's so comlicated :)
     Actually, my favorite SQL irony is that the people who reflexively
advocate it (and apparently connot conceive of storing and retrieving
data any other way), will sneer at Smalltalk because it is dynamically
typed.  At least we don't convert everything to and from text for each
message send ;)


> BTW:
>
>
>>parameters := (self dbConnection columns: tableName).
>
>
> That may work, but only if the parameters (the '?'s) in your query are exactly
> the same, and in the same order, as the columns in your table.

Order!!  *THAT'S* why I used #detect: - thanks!  That method needs
another comment.

Have a good one,

Bill

--
Wilhelm K. Schwab, Ph.D.
[hidden email]

Everyone,

thanks for the help, advice and tips. Finally have the update working
now. Went out and purchased SQL for Dummies so I can refresh on the
topic. As far as CRUD transactions are concerned, my problem was with
the Update(s).

I only use the parameterized statements for Inserts and Updates. For
SQL Reads and Deletes I just write the code by hand. As a temporary
stop gap, I was deleting the record followed by an Insert. It kept the
records in sync, but this is not something I would want in a production
level application.

I did find two fields that had the wrong names so I had to correct
those entries. Bill, your right about field ordering... my code that
marshals out object attributes had one field in the wrong place. The
debugger won't help you find things like that but it does offer a few
hints. After tracking that problem down, everything is working fine.

Next, I'll see if its as simple as changing out the ODBC driver for MS
Access and try out Oracle once the database schema has been
exported/imported. In theory, no code changes should be required, but
that is the theory. Soon to find out what reality holds (grin).

Pax