[archive] AcuSQL, Postgres database problem

[Migrated content. Thread originally posted on 14 August 2008]

I used the sample program that connects to a database, creates table, inserts rows, updates a row and deletes a row.

I modified the program a little bit. I commented out the table creation part because I don't have permission.

After I compiled and ran the program, I found out that the program failed to do the INSERT. It gave me an error of SQLCODE = -1, SQLSTATE = 42000 and SQLERRMC = Attempt to execute nonexistent, unprepared or invalid statement SQLIST.

I commented out the INSERT part and inserted records mannually. I recompiled and ran again. This time the program was able to do SELECT and FETCH statements and it failed to do the UPDATE statement. I got the same error like above.

I commented out the UPDATE part and the program failed again at DELETE statement woth the same error message.

It looks like I only have ready-only permission.

Can anyone help?

Thanks

Anthony Tran

Your DBA needs to provide permissions. With only read permissions you can not create, insert, or delete, you can only fetch.

[Migrated content. Thread originally posted on 14 August 2008]

I used the sample program that connects to a database, creates table, inserts rows, updates a row and deletes a row.

I modified the program a little bit. I commented out the table creation part because I don't have permission.

After I compiled and ran the program, I found out that the program failed to do the INSERT. It gave me an error of SQLCODE = -1, SQLSTATE = 42000 and SQLERRMC = Attempt to execute nonexistent, unprepared or invalid statement SQLIST.

I commented out the INSERT part and inserted records mannually. I recompiled and ran again. This time the program was able to do SELECT and FETCH statements and it failed to do the UPDATE statement. I got the same error like above.

I commented out the UPDATE part and the program failed again at DELETE statement woth the same error message.

It looks like I only have ready-only permission.

Can anyone help?

Thanks

Anthony Tran

When I used the same user name and password, logged on to pgAdmin, ran the update or insert command and it worked.

Here is my code

MOVE "update ch_remit.customertest set c_info = 'Revised'
- "where c_number = 2"
to sql-statement

inspect sql-statement replacing trailing space by null

* declare cursor
exec sql
declare updtcursor cursor for user-query
end-exec

* prepare cursor
exec sql
prepare user-query from :sql-statement
end-exec

* open cursor
exec sql
open updtcursor
end-exec

IF SQLCODE
PERFORM ERROR-EXIT
END-IF

EXEC SQL
CLOSE updtcursor
END-EXEC

The error I got is SQLCODE = -1 SQLSTATE 42000 Attempt to execute nonexistent, unprepared or invalid statement updtcu

[Migrated content. Thread originally posted on 14 August 2008]

I used the sample program that connects to a database, creates table, inserts rows, updates a row and deletes a row.

I modified the program a little bit. I commented out the table creation part because I don't have permission.

After I compiled and ran the program, I found out that the program failed to do the INSERT. It gave me an error of SQLCODE = -1, SQLSTATE = 42000 and SQLERRMC = Attempt to execute nonexistent, unprepared or invalid statement SQLIST.

I commented out the INSERT part and inserted records mannually. I recompiled and ran again. This time the program was able to do SELECT and FETCH statements and it failed to do the UPDATE statement. I got the same error like above.

I commented out the UPDATE part and the program failed again at DELETE statement woth the same error message.

It looks like I only have ready-only permission.

Can anyone help?

Thanks

Anthony Tran

When I used the same user name and password, logged on to pgAdmin, ran the update or insert command and it worked.

Here is my code

MOVE "update ch_remit.customertest set c_info = 'Revised'
- "where c_number = 2"
to sql-statement

inspect sql-statement replacing trailing space by null

* declare cursor
exec sql
declare updtcursor cursor for user-query
end-exec

* prepare cursor
exec sql
prepare user-query from :sql-statement
end-exec

* open cursor
exec sql
open updtcursor
end-exec

IF SQLCODE
PERFORM ERROR-EXIT
END-IF

EXEC SQL
CLOSE updtcursor
END-EXEC

The error I got is SQLCODE = -1 SQLSTATE 42000 Attempt to execute nonexistent, unprepared or invalid statement updtcu

[Migrated content. Thread originally posted on 14 August 2008]

I used the sample program that connects to a database, creates table, inserts rows, updates a row and deletes a row.

I modified the program a little bit. I commented out the table creation part because I don't have permission.

After I compiled and ran the program, I found out that the program failed to do the INSERT. It gave me an error of SQLCODE = -1, SQLSTATE = 42000 and SQLERRMC = Attempt to execute nonexistent, unprepared or invalid statement SQLIST.

I commented out the INSERT part and inserted records mannually. I recompiled and ran again. This time the program was able to do SELECT and FETCH statements and it failed to do the UPDATE statement. I got the same error like above.

I commented out the UPDATE part and the program failed again at DELETE statement woth the same error message.

It looks like I only have ready-only permission.

Can anyone help?

Thanks

Anthony Tran

Are you delimeting your query correctly, such as:


STRING
"SELECT C_FIRST_NAME, C_LAST_NAME" delimited by size,
" FROM CUSTOMER" delimited by size,
" WHERE C_NUMBER >= ?" delimited by size
into
demo-query.

EXEC SQL
PREPARE QID FROM :DEMO-QUERY
END-EXEC.

[Migrated content. Thread originally posted on 14 August 2008]

I used the sample program that connects to a database, creates table, inserts rows, updates a row and deletes a row.

I modified the program a little bit. I commented out the table creation part because I don't have permission.

After I compiled and ran the program, I found out that the program failed to do the INSERT. It gave me an error of SQLCODE = -1, SQLSTATE = 42000 and SQLERRMC = Attempt to execute nonexistent, unprepared or invalid statement SQLIST.

I commented out the INSERT part and inserted records mannually. I recompiled and ran again. This time the program was able to do SELECT and FETCH statements and it failed to do the UPDATE statement. I got the same error like above.

I commented out the UPDATE part and the program failed again at DELETE statement woth the same error message.

It looks like I only have ready-only permission.

Can anyone help?

Thanks

Anthony Tran

yes, I did

initialize sql-statement
string "update ch_remit.customertest set c_info = 'Revised'"
delimited by size
" where c_number = 2" delimited by size
into sql-statement
end-string

inspect sql-statement replacing trailing space by null

I still have the same error