Table create/drop using InstantSQL for rm/cobol

Hi,

Using the document below, I was able to perform select/update/insert using rm/cobol to Postgres Database in linux.

https://www.microfocus.com/documentation/rm-cobol/1214/ISQLhelp/InstantSQLTableOfContents.htm

However, I am unable to create a table or drop a table using InstantSql. Below are the errors I see. I am unable to see any sample in the document above.

1.drop table

*********prepare query

MOVE "drop table persons1"
TO sql-QrySQL

SQL PREPARE QUERY sql-QueryHandle,
sql-ConnectionHandle,
sql-QrySQL.

IF NOT sql-OK
display "UNSUCCESFUL PREPARE!!!"
PERFORM WITH TEST AFTER UNTIL sql-EndOfData
SQL DESCRIBE ERROR
sql-Error-Description
IF NOT sql-EndOfData
PERFORM DISPLAY-ERROR-INFO
END-IF
END-PERFORM
GO TO Z
END-IF


*********start query

SQL START QUERY sql-QueryHandle.
IF NOT sql-OK
display "UNSUCCESFUL START!!!"
PERFORM WITH TEST AFTER UNTIL sql-EndOfData
SQL DESCRIBE ERROR
sql-Error-Description
IF NOT sql-EndOfData
PERFORM DISPLAY-ERROR-INFO
END-IF
END-PERFORM
GO TO Z
END-IF

**********Compilation success. But while running,

UNSUCCESFUL START!!!
Err Msg ERROR: syntax error at or near "drop" at character 29;
Error while executing the query

Err TypO
Err statement SQLExecute
ErrsqlSate HY000
err no {00007

2.table creation
*********prepare query

MOVE "CREATE TABLE Persn2(PersonID int)"
TO sql-QrySQL

SQL PREPARE QUERY sql-QueryHandle,
sql-ConnectionHandle,
sql-QrySQL.

UNSUCCESFUL START!!!
Err Msg ERROR: syntax error at or near "create" at character 28;
Error while executing the query

Err TypO
Err statement SQLExecute
ErrsqlSate HY000
err no {00007

However I am able to do these directly to my  postgres  database in linux. 

Parents
  • Verified Answer

    Here is my best guess...

    Try using 'integer' as the data type for PersonID.  The error message seems to be indicating the column where the data type starts.

    When using ODBC, you must use SQL statements that conform to the ODBC driver requirements for the database's ODBC driver.  ODBC has its own SQL grammar rules that may constrict the SQL grammar usable.  This is an issue connected to ODBC, not InstantSQL.  

    When looking at the PostgresSQL grammar, 'int' is not a supported SQL data type, but 'integer' is.

    So, when you are testing a query outside of InstantSQL, you should be using an ODBC testing environment.  On Windows, use ODBC Test from Microsoft (https://learn.microsoft.com/en-us/sql/odbc/odbc-test?view=sql-server-ver16).  If you are on Linux, then the ODBC test environment depends on your version of ODBC driver manager (UnixODBC or Iodbc).  By using one of these ODBC testing environments, you can test without the 'noise' that InstantSQL adds.

    (Sorry, no PostgresSQL available here.)

Reply
  • Verified Answer

    Here is my best guess...

    Try using 'integer' as the data type for PersonID.  The error message seems to be indicating the column where the data type starts.

    When using ODBC, you must use SQL statements that conform to the ODBC driver requirements for the database's ODBC driver.  ODBC has its own SQL grammar rules that may constrict the SQL grammar usable.  This is an issue connected to ODBC, not InstantSQL.  

    When looking at the PostgresSQL grammar, 'int' is not a supported SQL data type, but 'integer' is.

    So, when you are testing a query outside of InstantSQL, you should be using an ODBC testing environment.  On Windows, use ODBC Test from Microsoft (https://learn.microsoft.com/en-us/sql/odbc/odbc-test?view=sql-server-ver16).  If you are on Linux, then the ODBC test environment depends on your version of ODBC driver manager (UnixODBC or Iodbc).  By using one of these ODBC testing environments, you can test without the 'noise' that InstantSQL adds.

    (Sorry, no PostgresSQL available here.)

Children
  • Thank you Tom. As you mentioned above, the problem seems to be with ISQL (my ODBC test in linux). 

    While using ISQL in  linux for POSTGRES, I am able to make a connection to the database and execute select/update/insert/delete statements on tables.

    But I am unable to execute create/drop/grant statements. I tried with ini, integer etc but still does not work. the drop and grant also does not work.

    I am getting error as below,

    1.CREATE

    SQL> create table shop (item1 integer);
    [S1000]ERROR:  syntax error at or near "create" at character 29;
    Error while executing the query
    [ISQL]ERROR: Could not SQLExecute
    SQL> create table shop (item1 int);
    [S1000]ERROR:  syntax error at or near "create" at character 29;
    Error while executing the query
    [ISQL]ERROR: Could not SQLExecute
    

    2.DROP

    SQL> drop table persons2;
    [S1000]ERROR:  syntax error at or near "drop" at character 29;
    Error while executing the query
    [ISQL]ERROR: Could not SQLExecute
    

    3.SELECT

    SQL> select * from persons2;
    
    | personid   | pname                                                                                                                                                                                                                                                        
    +------------+---------------------------------------------------------- 
    ------------+
    | 1          | test1                                                                                                                                                                                                                                                        
    | 2          | test2                                                                                                                                                                                                                                                          
    | 3          | test3                                                                                                                                                                                                                                                          
    | 4          | test4                                                                                                                                                                                                                                                          
    SQLRowCount returns 4
    4 rows fetched
    

    Grant statement does not work as well.  Please find my permissions below

    SQL> SELECT rolname, rolsuper, rolcreaterole FROM pg_roles WHERE rolname 
    = 'me';
    +-----------+---------+--------------+
    | rolname   | rolsuper| rolcreaterole|
    +-----------+---------+--------------+
    | me        | 1       | 0            |
    +-----------+---------+--------------+
    

    I am able to execute all the above statements using PSQL  though,

    psqltest=# create table shop(item1 int);
    
    CREATE TABLE

    Is there any other permissions needed for executing DDL statements using ISQL?
  • Suggested Answer

    Your results indicate you would probably be more likely to find a solution in a postgresSQL support forum.  A less likely possibility might be a unixODBC support forum.  Since you have eliminated InstantSQL from your debug environment, you should be able to pursue support in these other sites without the 'stigma' of COBOL.

    It is curious that the syntax error message always indicates character 29.

    It doesn't look like the unixODBC tool isql has any way to make calls to the ODBC API.  (Microsoft's ODBCTEST does allow this.)  

    The fact that you can execute statements using psql is not overly significant.  The SQL syntax for ODBC is different from 'native' Postgres SQL.  The ODBC minimal SQL grammar does not include the GRANT statement.  In the ODBC CREATE TABLE statement, column data types have to match the data types  returned by the API call SQLGetTypeInfo, which do not necessarily match the column data types in the native Postgres syntax.  ODBC minimal grammar is defined here.

    Anyway, the good news is that this problem can be simplified in a way that it can be presented in one of the other suggest support sites, and that InstantSQL is not a contributor to the problem.

  • The issue got resolved when I connected to ISQL without the username and password. 

    In this case too it created a table under my userid but when I logged in with my userid it did not let me create the table (not sure why).

    SQL CONNECT DATASOURCE
    sql-ConnectionHandle
    "dbname".

    rather than,

    SQL CONNECT DATASOURCE
    sql-ConnectionHandle
    "dbname"

     "userid"

     "password".

    ie. I connect to isql directly as > ISQL dbname 

    instead of > ISQL dbname userid password