`GENERATE_SQL` generating lines too long

[worksofliam]

CALL QSYS2.GENERATE_SQL('ACTIVESACTIVESACTIVESACTIVESACTIVESACTIVESACTIVESACTIVESACTIVESACTIVESACTIVESACTIVESACTIVESACTIVESACTIVES', 'SAMPLE', 'ALIAS', CREATE_OR_REPLACE_OPTION => '1', PRIVILEGES_OPTION => '0')

Possible fix is using the parameter to write to the IFS, then read in the IFS contents with a vscode-ibmi API.

https://www.ibm.com/docs/en/i/7.3?topic=services-generate-sql-procedure

[ThePrez]

Root cause is that the result set returns a column which is a CHAR(80). I suspect we will also run into problems using a temporary QSYS file.

I have verified that the results are correct when writing to a stream file. This appears to work

CALL QSYS2.GENERATE_SQL('ACTIVESACTIVESACTIVESACTIVESACTIVESACTIVESACTIVESACTIVESACTIVESACTIVESACTIVESACTIVESACTIVESACTIVESACTIVES', 'SAMPLE', 'ALIAS', CREATE_OR_REPLACE_OPTION => '1', PRIVILEGES_OPTION => '0',DATABASE_SOURCE_FILE_NAME =>'*STMF', SOURCE_STREAM_FILE =>'/home/LINUX/.vscode/my_ddl.sql');

SELECT LINE FROM TABLE(QSYS2.IFS_READ(PATH_NAME => '/home/LINUX/.vscode/my_ddl.sql',
                                   END_OF_LINE => 'LF')) ORDER BY LINE_NUMBER ASC;

[lgiammattei]

Hellò everybody, I am the author of issue #89 , if i may offer any suggestion it would be to create stream file under the /home/USER folder (maybe creating anothere folder named DDL or Generated DDL and the stream file should be named after the database object). Then let the user do what they want with the generated ddl files. i.e. I usually run generate ddl to build docs for my pgms documenting the database side of the procedure. I'll go closing #89 if you are ok with it.

[worksofliam]

@ThePrez Looks like this is throwing a null pointer exception:

    await JobManager.runSQL(
      `CALL QSYS2.GENERATE_SQL(?, ?, ?, CREATE_OR_REPLACE_OPTION => '1', PRIVILEGES_OPTION => '0', DATABASE_SOURCE_FILE_NAME => '*STMF', SOURCE_STREAM_FILE => ?);`,
      { parameters : [object, schema, internalType, tempFile] }
    );

    const content = await getInstance().getContent().downloadStreamfile(tempFile);

[ThePrez]

@ThePrez Looks like this is throwing a null pointer exception:

    await JobManager.runSQL(
      `CALL QSYS2.GENERATE_SQL(?, ?, ?, CREATE_OR_REPLACE_OPTION => '1', PRIVILEGES_OPTION => '0', DATABASE_SOURCE_FILE_NAME => '*STMF', SOURCE_STREAM_FILE => ?);`,
      { parameters : [object, schema, internalType, tempFile] }
    );

    const content = await getInstance().getContent().downloadStreamfile(tempFile);

Get me a server trace. It's on by default

[worksofliam]

This is currently using Server Component 1.2.0...going to try 1.4.5 next.

java.lang.NullPointerException
	at com.github.theprez.codefori.requests.PreparedExecute.go(PreparedExecute.java:63)
	at com.github.theprez.codefori.requests.PrepareSql.go(PrepareSql.java:70)
	at com.github.theprez.codefori.ClientRequest.run(ClientRequest.java:80)
	at java.lang.Thread.run(Thread.java:825)

[worksofliam]

Using 1.4.5

{"id":"query7","type":"prepare_sql_execute","sql":"CALL QSYS2.GENERATE_SQL(?, ?, ?, CREATE_OR_REPLACE_OPTION => '1', PRIVILEGES_OPTION => '0', DATABASE_SOURCE_FILE_NAME => '*STMF', SOURCE_STREAM_FILE => '/tmp/vscodetemp-O_4NJsijOe')","rows":2147483647,"parameters":["MANZANMSG","LIAMA","TABLE"]}

---

[ERR]: 2023-12-04.22.30.51.905

java.lang.NullPointerException
	at com.github.theprez.codefori.requests.PreparedExecute.go(PreparedExecute.java:63)
	at com.github.theprez.codefori.requests.PrepareSql.go(PrepareSql.java:70)
	at com.github.theprez.codefori.ClientRequest.run(ClientRequest.java:81)
	at java.lang.Thread.run(Thread.java:825)

[worksofliam]

This still works in 1.4.5:

{"id":"query4","type":"prepare_sql_execute","sql":"CALL QSYS2.GENERATE_SQL(?, ?, ?, CREATE_OR_REPLACE_OPTION => '1', PRIVILEGES_OPTION => '0')","rows":2147483647,"parameters":["MANZANPAL","LIAMA","TABLE"]}

I should note.. this is oss73dev.

Edit: tested on oss74dev with the same results.