Board index » delphi » Dynamic SQL Error - SQL error Code = -206

Dynamic SQL Error - SQL error Code = -206

I have the following select in a TIBDataset (ibx 4.2).
select RDB$ROLE_NAME ROLENAME, RDB$OWNER_NAME OWNERNAME from RDB$ROLES

Then I let the dataset Editor generate the Modify, Insert and delete sql

When I go into the Fields Editor and the do an add all fields I get the
above error.

Can you not rename field names?

Is there a workaround for this?

Thanks

 

Re:Dynamic SQL Error - SQL error Code = -206


I'm not able to duplicate this.  I was able to generate the other SQL and add
all the fields just fine.  What does your other SQL look like after you
generated them?

Quote
Ross Davis wrote:

> I have the following select in a TIBDataset (ibx 4.2).
> select RDB$ROLE_NAME ROLENAME, RDB$OWNER_NAME OWNERNAME from RDB$ROLES

> Then I let the dataset Editor generate the Modify, Insert and delete sql

> When I go into the Fields Editor and the do an add all fields I get the
> above error.

> Can you not rename field names?

> Is there a workaround for this?

> Thanks

--
Jeff Overcash (TeamB)
      (Please do not email me directly unless  asked. Thank You)
Have you ever met a lady screaming angst potential?
Have you ever dreamed of romance no matter how experimental?
Have you ever felt an alien drifting back into your hometown?
Did you think you were buying safety when you bought that piece of ground?
             (Fish)

Re:Dynamic SQL Error - SQL error Code = -206


Here is the text from the dfm that component is on.

  object Access_Roles_Database: TIBDataSet
    Database = PassAnywhereDatabase
    Transaction = PassAnywhereTransaction
    BufferChunks = 1000
    CachedUpdates = False
    DeleteSQL.Strings = (
      'delete from RDB$ROLES'
      'where'
      '  ROLENAME = :OLD_ROLENAME and'
      '  OWNERNAME = :OLD_OWNERNAME')
    InsertSQL.Strings = (
      'insert into RDB$ROLES'
      '  (ROLENAME, OWNERNAME)'
      'values'
      '  (:ROLENAME, :OWNERNAME)')
    RefreshSQL.Strings = (
      'Select '
      '  RDB$ROLE_NAME,'
      '  RDB$OWNER_NAME'
      'from RDB$ROLES '
      'where'
      '  ROLENAME = :ROLENAME and'
      '  OWNERNAME = :OWNERNAME')
    SelectSQL.Strings = (

        'select RDB$ROLE_NAME ROLENAME, RDB$OWNER_NAME OWNERNAME  from RD' +
        'B$ROLES')
    ModifySQL.Strings = (
      'update RDB$ROLES'
      'set'
      '  ROLENAME = :ROLENAME,'
      '  OWNERNAME = :OWNERNAME'
      'where'
      '  ROLENAME = :OLD_ROLENAME and'
      '  OWNERNAME = :OLD_OWNERNAME')
    Left = 48
    Top = 160
  end

Jeff Overcash (TeamB) <overc...@onramp.net> wrote in message
news:398252A5.3AD2BB1D@onramp.net...

Quote
> I'm not able to duplicate this.  I was able to generate the other SQL and
add
> all the fields just fine.  What does your other SQL look like after you
> generated them?

> Ross Davis wrote:

> > I have the following select in a TIBDataset (ibx 4.2).
> > select RDB$ROLE_NAME ROLENAME, RDB$OWNER_NAME OWNERNAME from RDB$ROLES

> > Then I let the dataset Editor generate the Modify, Insert and delete sql

> > When I go into the Fields Editor and the do an add all fields I get the
> > above error.

> > Can you not rename field names?

> > Is there a workaround for this?

> > Thanks

> --
> Jeff Overcash (TeamB)
>       (Please do not email me directly unless  asked. Thank You)
> Have you ever met a lady screaming angst potential?
> Have you ever dreamed of romance no matter how experimental?
> Have you ever felt an alien drifting back into your hometown?
> Did you think you were buying safety when you bought that piece of ground?
>              (Fish)

Re:Dynamic SQL Error - SQL error Code = -206


You can't use your aliases in your Insert, Modify and Delete SQL's, you need to
use the column names that the server knows.  So your Delete SQL should look like

delete from RDB$ROLES
where
  RDB$ROLE_NAME = :OLD_ROLENAME and
  RDB$OWNER_NAME = :OLD_OWNERNAME
Where are you bypassing the normal way to update roles?  I highly recommend not
messing with System tables like this, use the methods that IB itself exposes.
System tables should normally be using in read only mode.

Quote
Ross Davis wrote:

> Here is the text from the dfm that component is on.

>   object Access_Roles_Database: TIBDataSet
>     Database = PassAnywhereDatabase
>     Transaction = PassAnywhereTransaction
>     BufferChunks = 1000
>     CachedUpdates = False
>     DeleteSQL.Strings = (
>       'delete from RDB$ROLES'
>       'where'
>       '  ROLENAME = :OLD_ROLENAME and'
>       '  OWNERNAME = :OLD_OWNERNAME')

--
Jeff Overcash (TeamB)
      (Please do not email me directly unless  asked. Thank You)
Have you ever met a lady screaming angst potential?
Have you ever dreamed of romance no matter how experimental?
Have you ever felt an alien drifting back into your hometown?
Did you think you were buying safety when you bought that piece of ground?
             (Fish)

Re:Dynamic SQL Error - SQL error Code = -206


Thanks Jeff, Is this going to be fixed in an upcoming update so that we
don't have to update all of the insert,refresh,delete,modify sql?

By the way, you are quite right about updating system tables this way, I'm
not.  I am using this in a midas application for doing user security over
the internet, and in the beforeupdaterecord event I call a routine on the
server to addusers, give access to roles, etc and then I set the updated
flag on the event ot true to stop it from saving.

Jeff Overcash (TeamB) <overc...@onramp.net> wrote in message
news:398580C4.E81B02A1@onramp.net...

Quote
> You can't use your aliases in your Insert, Modify and Delete SQL's, you
need to
> use the column names that the server knows.  So your Delete SQL should
look like

> delete from RDB$ROLES
> where
>   RDB$ROLE_NAME = :OLD_ROLENAME and
>   RDB$OWNER_NAME = :OLD_OWNERNAME

> Where are you bypassing the normal way to update roles?  I highly
recommend not
> messing with System tables like this, use the methods that IB itself
exposes.
> System tables should normally be using in read only mode.

> Ross Davis wrote:

> > Here is the text from the dfm that component is on.

> >   object Access_Roles_Database: TIBDataSet
> >     Database = PassAnywhereDatabase
> >     Transaction = PassAnywhereTransaction
> >     BufferChunks = 1000
> >     CachedUpdates = False
> >     DeleteSQL.Strings = (
> >       'delete from RDB$ROLES'
> >       'where'
> >       '  ROLENAME = :OLD_ROLENAME and'
> >       '  OWNERNAME = :OLD_OWNERNAME')

> --
> Jeff Overcash (TeamB)
>       (Please do not email me directly unless  asked. Thank You)
> Have you ever met a lady screaming angst potential?
> Have you ever dreamed of romance no matter how experimental?
> Have you ever felt an alien drifting back into your hometown?
> Did you think you were buying safety when you bought that piece of ground?
>              (Fish)

Re:Dynamic SQL Error - SQL error Code = -206


I'll look at it.  This is a 'borrowed' editor from ADOExpress.

Quote
Ross Davis wrote:

> Thanks Jeff, Is this going to be fixed in an upcoming update so that we
> don't have to update all of the insert,refresh,delete,modify sql?

--
Jeff Overcash (TeamB)
      (Please do not email me directly unless  asked. Thank You)
Have you ever met a lady screaming angst potential?
Have you ever dreamed of romance no matter how experimental?
Have you ever felt an alien drifting back into your hometown?
Did you think you were buying safety when you bought that piece of ground?
             (Fish)

Other Threads