Gwen doesnt support for sql statements using DSL (apart from select)

[anshu781126]

defined in the database by sql "" this dsl supports select, have tested it but we dont have any dsl for update or create (any other ddl or dml)

[bjuric]

We haven't provided this capability because it is a data management concern. But you can call out to external scripts (like sqlplus scripts for example) using the system process DSL as follows to achieve what you need:

I execute system process "insert call to script here"

[bjuric]

@anshu781126, did you give the above a try?

[anshu781126]

Yes this works but for this I need to maintain a piece of code which invokes some sql client. It would be more convenient to directly use dsl; like other automation tool supports. Data Management can be part of testcases so in that case user will be at ease if he gets to do it by DSL rather than maintaining additional piece of code.

[bjuric]

My concern is that this could open up a whole world of data management problems and issues that could result in high maintenance and instability for Gwen. Also, since you are using a native SQL client, it makes sense to call out to it through the system process DSL. That's the precise reason we provide that DSL; so native capabilities can be utilised in a way that they are not coupled to Gwen.

[bjuric]

@anshu781126, we've given it some thought and propose to provide some basic support for executing SQL insert, update, or delete statements with the following DSL:

• I update the <dbName> database by sql "<updateSmt>"

  • <dbName> = the name of database to execute the query on. This name is used to configure the database as follows:
  • The GWEN_CLASSPATH environment variable must be set to include a path to the JDBC driver JAR
  • A gwen.db.<dbName>.driver setting must be set to the name of the JDBC driver implementation class
  • A gwen.db.<dbName>.url setting must be set to the JDBC URL of the database
  • <updateStmt> = the SQL insert, update, or delete statement (can be a DocString)

• Example using mysql:

  • Set GWEN_CLASSPATH variable in the environment to include the JDBC driver JAR
  • Unix: export GWEN_CLASSPATH=/path-to-driver/mysql-connector-java-5.1.40-bin.jar
  • Windows: set GWEN_CLASSPATH c:/path-to-driver/mysql-connector-java-5.1.40-bin.jar
  • Configure the database in your Gwen settings. In this example, <dbName>=feedback
  • gwen.db.feedback.driver = com.mysql.jdbc.Driver
  • gwen.db.feedback.url = jdbc:mysql://localhost/feedback?user=myUser&password=myPassword&serverTimezone=UTC&useSSL=false
  • Execute an update statement
  • Example using DocString syntax:

    When I update the feedback database by sql
       """
       update comments set email='new.email@server.com' where email='old.email@server.com'
       """

  • Or standard one line syntax

    When I update the feedback database by sql "update comments set email='new.email@server.com' where email='old.email@server.com'"

  • Check the number of rows affected:
    • Then feedback rows affected should be "1"

We've implemented this in the snapshot build below. Please download it and try it out (see example usage above):

• https://oss.sonatype.org/content/repositories/snapshots/org/gweninterpreter/gwen-web/2.11.1-8-g4343bf5-SNAPSHOT/gwen-web-2.11.1-8-g4343bf5-SNAPSHOT.zip

Let us know how it goes.

[anshu781126]

Working fine for update.

[bjuric]

Released in gwen-web v2.12.0