SQL Syntax - Forum - Kendo UI Builder by Progress: OpenEdge Edition - Progress Community
 Forum

SQL Syntax

This question is not answered

Hello All, do anyone knows how to properly write Progress SQL Syntax? I'm trying to connect Progress OpenEdge database thru JDBC connection. everything works fine, even achieved to read the data from base. but stacked at update, delete and create clauses, till now i have read 

https://documentation.progress.com/output/ua/OpenEdge_latest/index.html#page/dmsrf/update-statistics.html

http://knowledgebase.progress.com/articles/Article/000028845

https://documentation.progress.com/output/ua/OpenEdge_latest/index.html#page/dmsrf%2Fupdate.html%23wwID0ESF5Q

https://documentation.progress.com/output/ua/OpenEdge_latest/index.html#page/dmsrf%2Ffor-update-clause.html%23

https://documentation.progress.com/output/ua/OpenEdge_latest/index.html#page/bpm-manadapter%2Fdefining-sql-statements.html%23

http://sqlines.com/articles/progress/compared_with_sql

https://documentation.progress.com/output/ua/OpenEdge_latest/index.html#page/dmsrf%2Fupdate.html%23

http://knowledgebase.progress.com/articles/Article/P186445

http://knowledgebase.progress.com/articles/Article/P186445

 and 10th more but none of them has worked for me. 

I wish to update, delete and create new records in existing database. 

if anyone has any useful link or tutorial will much appreciate. 

thanks in advance

All Replies
  • Can you share some of the SQL statements you tried to execute and the resulting error for each?

  • UPDATE PUB.Client;

    UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.Client

    UPDATE ALL COLUMN STATISTIC FOR PUB.Client;

    UPDATE NDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.Client;

    i want to update any row in entire table.

    INSERT INTO PUB.Client (CLientNum, Name ......)

    VALUES(??????????????) i want to take user input at this line.

    DELETE FROM Pub.Client

    i have not tryed it much, when previous 2 failed;

    exception says syntax error mostly or progress openedge jdbc driver has failed and been shut down to prevent memory leak problem.

    SELECT clause works fines. SELECT * From PUB.Client

  • Hi Gorgi,
     
    >> UPDATE PUB.Client;
     
    This statement is not valid.  As per the SQL Reference manual, the correct syntax is this...
     
    UPDATE table_name SET assignment, assignment, .... WHERE search_condition;
     
    An example, change every record in sports2000.customer so that the balance field is 12345...
     
    UPDATE PUB.Customer SET Balance = 12345;

    >> UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.Client

    This should work.  Send the error information you got back.

    >> UPDATE NDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.Client;

    The word INDEX is not spelled correctly.

    >> want to update any row in entire table.

    >> INSERT INTO PUB.Client (CLientNum, Name ......)

    >> VALUES(??????????????) i want to take user input at this line.

    This -> i want to take user input at this line  <- is not possible in SQL.  SQL is a language for dealing with data, it is not a language for user interaction.  To do this you need to write code in whatever language you use to prompt for the values then pass them to the VALUES() phrase of the INSERT statement.

    >> DELETE FROM Pub.Client

    This is valid syntax for deleting all records in a table.  Please provide the error information that was returned.

    I also strongly recommend that you intall the offline PDF documentation then read the SQL Reference manual.

    Thanks, Brian

  • There is a bigger issue at play here.  There is a lack of documentation on how to manipulate data from within KUIB whether it be SQL based or OE based.  For someone just getting started with single page web applications after years of OE development, the transition is quite difficult in the current state.
     
    Bob Brennan
     
     
  • Hi Bob,

    Agreed. The best we have now is to use the built in views as "samples" of how to work with the data on the client side. Since the code is in the templates you would need to look at the source files after generating the web app. We also have samples on github github.com/.../kendo-ui-builder-samples.

    We are working on white papers explaining the new project structure on 2.0 (due out mid-July) and best practices for the client side extensions.

  • Hi Brian

    UPDATE PUB.Client not valid <-- roger that

    example to change every record in sports database looks logical. one issue I don't want to set value, I ant to allow user to set value. is there a work around like generic wildcard?  UPDATE Pub.Customer Set balance = ?, name=?, salary=? city=? and so on.

    ">> UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.Client

    This should work.  Send the error information you got back."  

    unfortunately while executing this line i have not had any report for a while. around 10 minutes later error message compile d, saying, "driver shutdown, preventing memory leak problem"

    >> INSERT INTO PUB.Client (CLientNum, Name ......)

    >> VALUES(??????????????) i want to take user input at this line.

    This -> i want to take user input at this line  <- is not possible in SQL.  SQL is a language for dealing with data, it is not a language for user interaction.  To do this you need to write code in whatever language you use to prompt for the values then pass them to the VALUES() phrase of the INSERT statement.

    Roger that as well

    >> DELETE FROM Pub.Client

    This is valid syntax for deleting all records in a table.  Please provide the error information that was returned.

    I also strongly recommend that you intall the offline PDF documentation then read the SQL Reference manual.

    Thanks, Brian

    can you provide from where I can get pdf?

    I will send error logs tomorrow.

    currently im trying to use OE database in JSP dynamic web page. before attempting to work with EO Data i have tried to work with normal SQL queries, to test my code. Code is working.

  • Hi Giorgi,
     
    You should have access to the PDF documentation in your ESD account (i.e. where you go to download the software you own).  www.progress.com/esd.
     
    Alernatively, online docs are here -> documentation.progress.com/.../index.html
     
    Brian

  • Error msg:

    java.sql.SQLFeatureNotSupportedException: [DataDirect][OpenEdge JDBC Driver]Unsupported method: Connection.prepareStatement

    at com.ddtek.jdbc.openedgebase.ddb9.b(Unknown Source)

    at com.ddtek.jdbc.openedgebase.ddb9.a(Unknown Source)

    at com.ddtek.jdbc.openedgebase.ddb8.b(Unknown Source)

    at com.ddtek.jdbc.openedgebase.ddb8.a(Unknown Source)

    at com.ddtek.jdbc.openedgebase.BaseConnection.prepareStatement(Unknown Source)

    at update method

  • Hi Giorgi,
     
    What is the non-SQL code that generated this error?
     
    Brian