Using DOCSQL command to view or update records in the WebDocs - iSeries Edition document table DOCS00 - Support

Using DOCSQL command to view or update records in the WebDocs - iSeries Edition document table DOCS00

From Support

Jump to: navigation, search

If your AS/400 - iSeries system has the interactive SQL utility available, you can use the STRSQL command and then type in SQL statements interactively.

In case you don't have interactive SQL on your iSeries, you can use the DOCSQL command in the RJSIMAGE library to run SQL statements.

The DOCSQL utility is provided as a courtesy by RJS with the Image Server/400 software.

[edit]

Available Index and Folder Keyword Fields in the documents table DOCS00

  • TITLE
  • FOLDER1
  • FOLDER2
  • FOLDER3
  • KEYWORD1
  • KEYWORD2
  • KEYWORD3
  • KEYWORD4
  • KEYWORD5
  • KEYWORD6
  • KEYWORD7
  • KEYWORD8
  • KEYWORD9
  • KEYWORD10
  • DOCTYPE2


[edit]

Sample SQL for displaying records in the DOCS00 table

Example 1: Display all records in DOCS00 

DOCSQL SQLSTM('SELECT * FROM DOCS00')

Example 2: Display all records where the document title starts with TEST 

DOCSQL SQLSTM('SELECT * FROM DOCS00 WHERE TITLE LIKE "TEST*"')

Example 3: Display all records where the document title = TEST 

DOCSQL SQLSTM('SELECT * FROM DOCS00 WHERE TITLE = "TEST"')

Example 4: Display all records where the document title field contains the text string TEST 

DOCSQL SQLSTM('SELECT * FROM DOCS00 WHERE TITLE LIKE "*TEST*"')
[edit]

Updating Fields in the Image Server/400 document table via SQL

WARNING: Make sure to back up the RJSIMAGE library or the DOCS00 file prior to using SQL to update any fields in the Image Server database. Improper use of SQL statements can inadvertently overrite data.


Example 1: Update all records where KEYWORD10 is initially = 'TEST' with the value of 'TEST UPDATE' 

DOCSQL SQLSTM('UPDATE DOCS00 SET KEYWORD10 = "TEST UPDATE" where KEYWORD10 = "TEST"')

Example 2: Update all records where KEYWORD10 is initially = 'TEST' with the value of 'TEST UPDATE' in KEYWORD10 and 'YES' in KEYWORD9 

DOCSQL SQLSTM('UPDATE DOCS00 SET KEYWORD10 = "TEST UPDATE",KEYWORD9 = "YES" where KEYWORD10 = "TEST"')

Example 3: Update all records where TITLE is initially = 'BOB SMITH' with the value of 'ROBERT SMITH' 

DOCSQL SQLSTM('UPDATE DOCS00 SET TITLE = "ROBERT SMITH" where TITLE = "BOB SMITH"')
Retrieved from "http://wiki.rjssoftware.com/wiki/index.php/Using_DOCSQL_command_to_view_or_update_records_in_the_WebDocs_-_iSeries_Edition_document_table_DOCS00"
Personal tools