Update record with ADO(vb 6.0) using SQL_92 ODBC ?

[fuji36]

We have the Merant 3.6 sql-92 ODBC driver , set up to connect with our progress 9.1D database. I've been developing ,to date, app's which only 'get' data, in mostly vb 6.0 using ADO and recordsets.
I now need to to some data modifications , I've been trying to use the .Update method with my recordset , with various combinations of 'cursor' and 'lock' settings, still keep getting 'Access denied' error message, I'm sure it has to do with my ODBC set-up/ permissions, etc.
We have set our Merant driver up with a different user name than 'sysprogress'. Would I need to use a different 'DSN' than has log-ins that allow changing data?

Thanks.

 

[Casper]

Do you get access denied or permission denied?
Access denied means authorization failed.

Permission denied means you have to grant update to that user....

Casper.

 

[fuji36]

Thanks Casper, I'm not at work now and cannot check for sure but I believe it was Access Denied . But , I know that the ODBC user/password are valid and I can retreive data.

Granting 'update' to a user is done thru a Progress tool?

I will get back to you on Tuesday.

Do you get access denied or permission denied?
Access denied means authorization failed.

Permission denied means you have to grant update to that user....

Casper.

 

[Casper]

Hmm, after reading the documentation more properly I found out that it is also possible to get 'access denied' if the priviliges are not granted...

Anyway, to give a user permission on some (or all) table you have to grant this permission with a user who has DBA priviliges in the following way:

GRANT select|insert|delete|update|index|reference  ON [I]table-name[/I]  to username;

You have to do this for each table you want to update. If you need to do this with all or many tables it is easier to make a script like:

/* sample script to generate update priviliges for a user */
define stream sOut.
 
output stream sOut to pathtosomedir\allfilesupdate.sql.
 
for each _file where _file._hidden = no:
 
put stream sOut unformatted
"GRANT UPDATE ON PUB."  + _file._file-name " TO [I]username;[/I]" + CHR(10).
put stream sOut unformatted "commit;" + CHR(10).
end.
 
output stream sOut close.

HTH,

Casper

 

[fuji36]

Casper,
Is this 'granting' scripting done thru a progress tool?
Also, is the 'username' a 'username' defined thru a System DSN/ODBC Driver?

Thanks

 

[tamhas]

GRANT is normally done through a SQL session. The sample code above is showing the SQL done in a Progress session in order to use the FOR EACH. The SQL in a Progress session is actually SQL89 and is deprecated in more recent releases, so I'd tend to go in the direction of either hand entered GRANTs entered through a SQL tool or generating a SQL script if you have a lot of tables and users to cover.

 

[Casper]

...sample code above is showing the SQL done in a Progress session in order to use the FOR EACH. The SQL in a Progress session is actually SQL89 and is deprecated in more recent releases...

Nop, the sample is using progress to generate an ordinary sql script, nothing to do with deprecated sql89....


The user is a user with password which has to be in the _user table. You can use any sql tool you like to execute the sql statements.

To use the script to generate SQL grant scripts you have to have a full development Progress license....

In 9.1E Progress used to have the SQL explorer tool, IN 10.1A and above you're suppose to do it with the DB navigator from OE architect, but you can use any sql tool you like.

HTH,

Casper.

 

[tamhas]

Shows how carefully I was reading! :blush1:

I don't know that I would go for this blanket authorization, though, unless one anticipated continued development in the direction of doing updates of lots of tables. At this point it sounds like there is just one or two tables involved and so it would be more secure to GRANT the privileges for just those until more are needed.

BTW, this is a good example of the kind of situation in which it is a good idea to have a special username used for making these updates so that you don't need to extend privileges to lots and lots of users.

 

[Casper]

Agreed!

And you have to be carefull and not forget that plain old 4GL triggers won't work with SQL, so you have to take care of the database integrity yourself. Lots of people forget that triggers wont fire with SQL updates.

Casper.