Activity for The OpenHMS Project
-
marcello endo posted a comment on discussion SqlBuilder Help
lol, I just found this function now, and thanks for confirming this method, thank you very much, and congratulations the verygood framework, sorry for my english.
-
James Ahlborn modified a comment on discussion SqlBuilder Help
you would use a FunctionCall with a single parameter. https://openhms.sourceforge.io/sqlbuilder/apidocs/com/healthmarketscience/sqlbuilder/FunctionCall.html
-
you would use a FunctionCall with a single string parameter. https://openhms.sourceforge.io/sqlbuilder/apidocs/com/healthmarketscience/sqlbuilder/FunctionCall.html
-
I would like to know if there is any way to use Sequence, nexvtal('value') for example, or would I have to create a custom method for this?
-
I would like to know if there is any way to use Sequence in INSERT INTO, nexvtal('value') for example, or would I have to create a custom method for this? I'm waiting, thanks
-
I'm waiting, thanks
-
wait;
-
thanks
-
Grant Lee posted a comment on discussion SqlBuilder Help
ah, got it now, thanks.
-
the methods on InsertQuery are just helpers for inserts where you will have a number of columns corresponding with a number of values. It just helpfully stores the right number of SqlObject.QUESTION_MARK values for you. for UpdateQuery, you would just pass the SqlObject.QUESTION_MARK in for the value.
-
Thanks, that seems like a good solution for inserts. what about updates though? InsertQuery has the addPreparedColumns() method, but for the UpdateQuery, I don't see an equivalent. All I see is addSetClause(), which doesn't help in this case.
-
if you want to use some form of streaming, you would need to be using a prepared statement. you would use InsertQuery to insert prepared columns and then use the PreparedStatement methods to set the parameters using a stream. Alternately, you can use QueryPreparer to track the placeholders and again, you would use the PlaceHolder to assist in passing the stream to the PreparedStatement.
-
what is the recommended solution for inserting a very large block of text into a CLOB or TEXT field? The InsertQuery object doesn't seem to support streaming. Just passing in a large string seems fine for most cases, but what if the string is very large?
-
Louis Reinitz posted a comment on discussion SqlBuilder Help
I'm sorry I was parsing a bunch of table creation sql(s) and forgot to strip the comma of of the first argument so I was looking for a column named "sku," instead of "sku" (SMHAM)
-
dbTable = dbSchema.addTable("'ItemGroup'"); dbColumn = dbTable.addColumn("sku", Types.VARCHAR, null, null); dbColumn.references(null, "'Item'", "sku"); onDeleteMap.put(dbColumn, "ON DELETE CASCADE"); dbColumn = dbTable.addColumn("groupId", Types.INTEGER, null, null); dbColumn.references(null, "'Group'", "id"); onDeleteMap.put(dbColumn, "ON DELETE CASCADE"); dbTable.primaryKey("itemgroup_pri_key", "sku,", "groupId"); this throws the following exception Exception in thread "main" java.lang.NullPointerException...
-
dbTable = dbSchema.addTable("'ItemGroup'"); dbColumn = dbTable.addColumn("sku", Types.VARCHAR, null, null); dbColumn.references(null, "'Item'", "sku"); onDeleteMap.put(dbColumn, "ON DELETE CASCADE"); dbColumn = dbTable.addColumn("groupId", Types.INTEGER, null, null); dbColumn.references(null, "'Group'", "id"); onDeleteMap.put(dbColumn, "ON DELETE CASCADE"); dbTable.primaryKey("itemgroup_pri_key", "sku,", "groupId"); this throws the following exception Exception in thread "main" java.lang.NullPointerException...
-
i believe what you are looking for is https://openhms.sourceforge.io/sqlbuilder/apidocs/com/healthmarketscience/sqlbuilder/ForeignKeyConstraintClause.html#setOnDeleteAction-java.lang.Object-
-
DbTable dbTable = null; DbColumn dbColumn = null; ForeignKeyConstraintClause fkConstraintClause = null; DbForeignKeyConstraint dbForeignKeyConstraint = null; dbTable = dbSchema.addTable("Squid"); dbColumn = dbTable.addColumn("id", Types.INTEGER, null); dbColumn.primaryKey(); dbTable.addColumn("rant", Types.VARCHAR, null); dbTable = dbSchema.addTable("Items"); dbColumn = dbTable.addColumn("sku", Types.VARCHAR, null); dbColumn.primaryKey(); dbColumn = dbTable.addColumn("SquidId", Types.INTEGER, null);...
-
[maven-release-plugin] prepare for next development iteration
-
[maven-release-plugin] copy for tag openhms-parent-2.0.7
-
[maven-release-plugin] prepare release openhms-parent-2.0.7
-
update plugin version
-
[maven-release-plugin] prepare for next development iteration
-
[maven-release-plugin] copy for tag openhms-parent-2.0.6
-
[maven-release-plugin] prepare release openhms-parent-2.0.6
-
update ssh
-
[maven-release-plugin] prepare for next development iteration
-
[maven-release-plugin] copy for tag openhms-parent-2.0.5
-
[maven-release-plugin] prepare release openhms-parent-2.0.5
-
[maven-release-plugin] prepare for next development iteration
-
[maven-release-plugin] copy for tag openhms-parent-2.0.4
-
[maven-release-plugin] prepare release openhms-parent-2.0.4
-
switch to findbugs
-
thanks, i really appreciate that! glad you are finding the library both useful and enjoyable.
-
Gavin Ray posted a comment on discussion SqlBuilder Help
I registered an account on SourceForge to submit this. You have my thanks for open-sourcing and maintaining this library, it's brilliantly designed and of unusually high quality. Your efforts are much appreciated.
-
those don't currently exist. feel free to file a feature request https://sourceforge.net/p/openhms/feature-requests/ or, better yet, submit a patch https://sourceforge.net/p/openhms/patches/. i would imagine most of those would be pretty simple (although table renaming doesn't seem to have a standard syntax).
-
link to the unit test source is included in the "Getting Started" section on the main project page.
-
Denis modified a comment on discussion SqlBuilder Help
Hello. I did not find whether it is possible to create the following requests: 1) create schema "schema name" 2) drop schema "schema name" 3) alter table "table name" drop column "column name" 4) alter table "table name" rename to "new table name" If this is not possible now, is it planned?
-
Hello. I did not find whether it is possible to create the following requests: 1) create schema <schema name=""> 2) drop schema <schema name=""> 3) alter table </schema></schema> drop column rename to <new table="" name=""> If this is not possible now, is it planned?</new>
-
Thanks for the answer, you need to explicitly indicate the link to the tests on the project page - this will help to find answers to many questions.
-
Thanks for the answer, you need to explicitly indicate the link to the tests on the project page - this will help to find answers to many questions. Another question - is it possible to make a request with "drop column"?
-
so the BinaryCondition logic is attempting to interpret the value you passed in. since you have only passed in a String, it is being interpreted as a value. in order to use a column, it is best to use a Column instance, e.g. dbTable.findColumn("id"). note, there are a ton of examples in https://openhms.sourceforge.io/sqlbuilder/xref-test/com/healthmarketscience/sqlbuilder/SqlBuilderTest.html#SqlBuilderTest
-
Hey! when creating an UpdateQuery or SelectQuery query, for example: String column = "id"; String value = "123"; String query = newSelectQuery() .addFromTable(dbTable) .addAllTableColumns(dbTable) .addCondition(BinaryCondition.equalTo(column, value)) .validate() .toString() the resulting query string looks like this: "SELECT t0. FROM public.test1 t0 WHERE ('id' = '123')" and the problem is that this query does not return a result, because the table column name "id" in the where clause is enclosed...
-
Hey! when creating an UpdateQuery or SelectQuery query, for example: String column = "id"; String value = "123"; String query = newSelectQuery() .addFromTable(dbTable) .addAllTableColumns(dbTable) .addCondition(BinaryCondition.equalTo(column, value)) .validate() .toString() the resulting query string looks like this: "SELECT t0. FROM public.test1 t0 WHERE ('id' = '123')" and the problem is that this query does not return a result, because the table column name "id" in the where clause is enclosed...
-
Hey! when creating an UpdateQuery or SelectQuery query, for example: String column = "id"; String value = "123"; String query = newSelectQuery() .addFromTable(dbTable) .addAllTableColumns(dbTable) .addCondition(BinaryCondition.equalTo(column, value)) .validate() .toString() the resulting query string looks like this: "SELECT t0. FROM public.test1 t0 WHERE ('id' = '123')" and the problem is that this query does not return a result, because the table column name "id" in the where clause is enclosed...
-
can you show an example of what you are attempting?
-
Raj posted a comment on discussion SqlBuilder Help
@jahlborn I tried common table expression for joining a table with subquery but it is not working and getting validation exception, if i am not joining it is working fie. Can you please suggest
-
[maven-release-plugin] prepare for next development iteration
-
[maven-release-plugin] copy for tag openhms-parent-2.0.3
-
[maven-release-plugin] prepare release openhms-parent-2.0.3
-
update enforcer plugin version
-
Mike Taylor posted a comment on ticket #28
Excellent, thank you! (And, as I should have said up front: thank you for this package!)
-
update sql syntax types
-
SqlBuilder 3.0.2 released
-
The OpenHMS Project released /sqlbuilder/sqlbuilder-3.0.2/sqlbuilder-3.0.2-sources.jar
-
-
-
-
[maven-release-plugin] prepare for next development iteration
-
[maven-release-plugin] copy for tag sqlbuilder-3.0.2
-
[maven-release-plugin] prepare release sqlbuilder-3.0.2
-
prep for release
-
Add optional null-handling argument to addCustomOrdering and similar methods
-
added some relevant comments in 3.0.2 release.
-
Add convenience methods for constructing OrderObject instances. Add documention about using OrderObject to control null ordering.
-
yep, that's a great suggestion. some of the newer features haven't been as thoroughly documented.
-
Thanks, James, that's really helpful! I was able to get this to what I needed without too much trouble. Perhaps worth adding a note to the documentation?
-
so i admit that it is not super obvious, but this is already supported. you can see an example in the unit tests here https://openhms.sourceforge.io/sqlbuilder/xref-test/com/healthmarketscience/sqlbuilder/SqlBuilderTest.html#L692
-
Add optional null-handling argument to addCustomOrdering and similar methods
-
that's not really a self join, you actually want to use a subquery as a join source. this is not directly supported in sqlbuilder (should really add a FAQ entry for this). however, a "common table expression" is the modern form of this, which is supported in sqlbuilder. see this similar thread for more details. Note, i've created a FAQ entry for this since it's come up a few times before.
-
Note, i've created a FAQ entry for this.
-
add faq entry for ctes
-
add download link
-
that's not really a self join, you actually want to use a subquery as a join source. this is not directly supported in sqlbuilder (should really add a FAQ entry for this). however, a "common table expression" is the modern form of this, which is supported in sqlbuilder. see this similar thread for more details.
-
Neeraj Shah modified a comment on discussion SqlBuilder Help
I want to generate a self join query something similar to SQL showed below, I tried several ways but couldn't accomplish it. select time_stamp, metric_name, sum(metric_value) from metrics_table "t0" JOIN ( select metric_name, MAX(time_stamp) as time_stamp from metrics_table GROUP BY metric_name ) as "t1" where t0.metric_name = t1.metric_name and t0.time_stamp = t1.time_stamp Thanks for assistance!!!
-
I want to generate a self join query something similar to SQL showed below, I tried several ways but couldn't accomplish it. select timestamp, metric_name, sum(metric_value) from metrics_table "t0" INNER JOIN ( select metric_name, MAX(timestamp) as timestamp from metrics_table GROUP BY metric_name ) as "t1" where t0.metric_name = t1.metric_name and t0.timestamp = t1.timestamp Thanks for assistance!!!
-
the RejoinTable would not be added to the Spec. The Spec is generally intended for the objects which are declared explicitly in the database and would be reused across multiple queries. the RejoinTable is more targeted for use within a single query in which you need to join the same table multiple times. you can see some example usage in the unit tests, where you would manually add the join to the query using the RejoinTable.
-
Fernando Cuervo Hi posted a comment on discussion SqlBuilder Help
Hello, Im stuck at using a RejoinTable. I have created the Rejoin Table, but im not sure how to use it DbSpec spec = new DbSpec(); DbSchema sapWeb = spec.addSchema("sapweb"); String unitSapAlias = "unit"; DbTable unitSap = new DbTable(sapWeb, "unitsap", unitSapAlias); sapWeb.addTable(unitSap); ....//addColumn methods String unitSapRealAlias = "unitReal"; RejoinTable rejoinTable = unitSap.rejoin(unitSapRealAlias); Do I need to do a spec.addJoin. I have made normal addJoins, but exceptions are thrown...
-
update junit
-
upgrade junit
-
i assume you mean a library that is already created? you can certainly do this on your own. in fact, this library was born out of proprietary code which did exactly this. there was an xml model which was parsed into the ddl and dml using this library. however, i don't know of any currently open source implementations of this functionality.
-
Islam OUAHOUDA posted a comment on discussion SqlBuilder Help
Hello, Thanks for the library. I want to ask of there is anyway to build sql queries from XML file with columns data etc.
-
can you show the code you currently have?
-
Rusiru de silva posted a comment on discussion SqlBuilder Help
I tried to create this query using sqlbuilder, But can't use the arithmetic operators. Any solutions
-
more minor cleanups
-
https://sourceforge.net/p/openhms/code/HEAD/tree/sqlbuilder/trunk/src/main/java/com/healthmarketscience/sqlbuilder/SqlObjectList.java#l46
-
Riccardo Prandini posted a comment on ticket #25
ok but the property has to be a lot specific to not mess around with other apps/ packages
-
You can set system properties at runtime. you just need to use System.setProperty() before you start using the sqlbuilder classes (i'd do it in a static{} block in one of your main classes).
-
When we use the class we have to work with websphere and other application server managed by specific office an the customization via system propery is not an option everywhere. We have a config file and we act at runtime . Thanks
-
Add system property which allows setting the default list item separator to include a trailing space
-
this isn't the first time i've heard the request for a space after a comma. what if i added a system property to set the default list delimiter to include a trailing space (for all comma separated lists)?
-
Yes this one is the solution.
-
If you want a custom version of the equivalent functionality, you can create a "CustomFunctionCall" class by copying the FunctionCall class and changing the params initializer to: private SqlObjectList<SqlObject> _params = SqlObjectList.create(", ");
-
If you want a cusom version of the equivalent functionality, you can create a "CustomFunctionCall" class by copying the FunctionCall class and changing the params initializer to: private SqlObjectList<SqlObject> _params = SqlObjectList.create(", ");
-
Yes this is the second problem i think that with a customizable substring query the things cold be resolved.
-
I'm confused. i thought your original problem is that lack of spaces between the arguments, but the example you are showing does not have spaces. if you don't need the spaces, then everything should work correctly if you do something like: new FunctionCall("SUBSTR").addCustomParams(TBSTSETP_FLGABI,1,1);
-
I think this is how sql should be done correctly. if you call each table t1...tn also the occurence of each table should report the same tn and not the original one SELECT t13.CDSERVIZ,t13.DSSERVIZ, t8.CDTIPO,t8.DSTIPO FROM ~aliasDb.TBSTANSE t13, ~aliasDb.TBSTTIDP t8, ~aliasDb.TBSTSETP t12 WHERE ( (t8.CDTIPO = t12.TIPPRE) AND (t13.CDSERVIZ = t12.CODSER) AND (t8.CDABI = '05034') AND (t13.CDABI = '05034') AND (t12.CODABI = '05034') AND (t12.DATINI <= CURRENT DATE) AND (t12.FLGABI > CURRENT DATE) AND...
-
minor code cleanups
1 >