Hello All,

I am having a problem using the Update statement with DBISAM 4.34

When I was using DBISAM 3.x my SQL code worked.

I know it is running 4.34 because I am getting "line 1, column 29" in
the error message. And DBISAM 3.x didn't give the
line and column numbers.

"Engine Error # 11949 SQL parsing error - Expected column name but
instead found "SYSU0001" in UPDATE SQL statement at line 1, column 29"

This is the SQL :-

      SQL.Add('UPDATE SYSTUSER' +
              ' SET ' + valuesTxt +
              ' WHERE (Owner = "' + UserID + '")');

Now valuesTxt is like :-

  Owner = "SYSU0001", OwnerText = "System operator 1", ...

I think line 1, column 29 is the first " in "SYSU0001".

Now the word Owner should be the first field in the table.

Does this mean that the field Owner is not being recognised?

Something to do with initialising the database in DBISAM 4.34?

How do I get this working? At the moment can't even log onto my ported
(version 3 to version 4) system!

Regards,
  Peter Evans

On 11/03/2013 3:20 PM, Peter Evans wrote:

Well I managed to overcome those problems, I think.

Changing the SQL to like :-

  AllGroup = TRUE
  this is the column AllGroup which is boolean.

  Previously the following did not work :-

    AllGroup = "TRUE"


I am now getting the error "Engine Error # 11013 Access denied to table
or backup file 'SYSTUSER'.

When I use the 'Database System Utility' to open the table - down the
bottom of the window is stated "Shared/Read-Only".

How do I make the table writeable?

Note - All my tables were converted using the 'Database System Utility'
from DBISAM 3 to 4.

Regards,
  Peter Evans

On 3/11/2013 12:20 AM, Peter Evans wrote:
> I am having a problem using the Update statement with DBISAM 4.34
>
> When I was using DBISAM 3.x my SQL code worked.

There have been changes.  See manual here for changes from 3->4:

http://www.elevatesoft.com/manual?action=viewtopic&id=dbisam4&product=rsdelphiwin32&version=XE2&topic=Changes

> "Engine Error # 11949 SQL parsing error - Expected column name but
> instead found "SYSU0001" in UPDATE SQL statement at line 1, column 29"
>
> This is the SQL :-
>
>        SQL.Add('UPDATE SYSTUSER' +
>                ' SET ' + valuesTxt +
>                ' WHERE (Owner = "' + UserID + '")');
>

Constants need to be enclosed in single quotes in v4 so SQL would need
to have Owner='SYSU0001' and similar for OwnerText.

Raul

On 3/11/2013 2:08 AM, Peter Evans wrote:
> On 11/03/2013 3:20 PM, Peter Evans wrote:
>
> Changing the SQL to like :-
>
>    AllGroup = TRUE
>    this is the column AllGroup which is boolean.
>
>    Previously the following did not work :-
>
>      AllGroup = "TRUE"
>

Sorry Peter - your previous post and this seem to cover different SQL so
you've lost me.

As to the boolean values - you do not need quotes for them.


> I am now getting the error "Engine Error # 11013 Access denied to table
> or backup file 'SYSTUSER'.
> When I use the 'Database System Utility' to open the table - down the
> bottom of the window is stated "Shared/Read-Only".
>
> How do I make the table writeable?

It should be writable by default and likely nothing to do with dbsys.
Most likely cause would be that the table files are marked read-only on
the file system - DBSYS would open them read-only  as well.

Or if you're using Vista/Win7 and your DB files are under program files\
then windows might force read-only unless you run dbsys in admin mode.

Raul

On 12/03/2013 4:26 AM, Raul wrote:
> On 3/11/2013 12:20 AM, Peter Evans wrote:
>> I am having a problem using the Update statement with DBISAM 4.34

> Constants need to be enclosed in single quotes in v4 so SQL would need
> to have Owner='SYSU0001' and similar for OwnerText.
>
> Raul

Thanks for that advice. It seems I have to change all double quotes to
single quotes. Sigh.

Regards,
  Peter Evans

On 12/03/2013 4:32 AM, Raul wrote:
>
> On 3/11/2013 2:08 AM, Peter Evans wrote:

>
> As to the boolean values - you do not need quotes for them.

Thanks for that advice. Looks like I have to change all the boolean
values too. Sigh.

>
>
>> I am now getting the error "Engine Error # 11013 Access denied to table
>> or backup file 'SYSTUSER'.
>> When I use the 'Database System Utility' to open the table - down the
>> bottom of the window is stated "Shared/Read-Only".
>>
>> How do I make the table writeable?
>
> It should be writable by default and likely nothing to do with dbsys.
> Most likely cause would be that the table files are marked read-only on
> the file system - DBSYS would open them read-only  as well.
>
> Or if you're using Vista/Win7 and your DB files are under program files\
> then windows might force read-only unless you run dbsys in admin mode.
>

I will look into this further.

Regards,
  Peter Evans