Open Source Document Management System | OpenKM

PostPosted:**Tue Jun 09, 2015 8:12 am**

Hello,

When using the Webinterface of OpenKM i can make Database Querys under Administrator>Database query.

Is it possible to make the same query with the Webservice SDK for .net or REST SDK for .net

For example:
Under Administrator>Database query, the query "Select * FROM okm_user" returns all the users.

in my code i want something like this:

Code: Select all

string query = "Select * FROM okm_user";
public string[] doQuery(string query)
{
	//code to query the database, which i can't figure out;
	//return all users;
}

PostPosted:**Wed Jun 10, 2015 8:37 am**

It's done in professional version where the api supports sql queries and hsql queries
http://demo.openkm.com/OpenKM/services/ ... tory?_wadl and looking for executeSqlQuery

Unfortunately this feature still has not included in community version.

PostPosted:**Wed Jun 10, 2015 10:05 am**

Thanks for the information!
Are you going to implement SQLQueries into the community edition at any time soon?

PostPosted:**Thu Jun 11, 2015 9:17 am**

No, it will not be moved on nearly future, we do a major release each year and then corrections, until next year we will not do another major release. Now we're only correcting minor bugs. If you need it fast on community edition, can take the source code and extend it, it's not much complicated, here you have the OpenKM portable developement environment https://sourceforge.net/projects/openkmportabledev/

PostPosted:**Thu Jun 11, 2015 12:24 pm**

Thank you!
I appreciate your help very much!

I downloaded the OpenKM portable developement environment and did everything according to the README.txt.
Everything seems working fine so far.

Would it be possible, to give me a little push in the right direction?
Some information where and how i have to modify the Sourcecode to enable SQL-queries?

Thanks in advance!

PostPosted:**Sat Jun 13, 2015 4:26 pm**

You should extend RespositoryService.java
http://doxygen.openkm.com/openkm_6.3.0/ ... rvice.html

And also take a look at LegacyDAO.java
http://doxygen.openkm.com/openkm_6.3.0/ ... d_a_o.html

At the begining the RepositoryService method we created was based on passing query string ( but it has some characters limitation ) for it we changed as document upload ( if you will execute single sql queries, first option will be enought to you ).

Code: Select all

@POST
	@Path("/executeSqlQuery")
	@Consumes(MediaType.MULTIPART_FORM_DATA)
	// The "query" parameter comes in the POST request body (encoded as XML or JSON).
	public SqlQueryResults executeSqlQuery(List<Attachment> atts) throws GenericException {

PostPosted:**Mon Jun 15, 2015 8:17 am**

Thank you!
I will try to make something happen!

I'm not sure if I can do it, since I'm pretty new to Java and SQL...

I'm having trouble with the "SqlQueryResults" in
public SqlQueryResults executeSqlQuery(List<Attachment> atts) throws GenericException

where/how do i have to define the SqlQueryResult?

Sorry for your inconvenience!
I appreciate your help very much!

PostPosted:**Tue Jun 16, 2015 2:35 pm**

Hope this give you some idea

Code: Select all

package com.openkm.ws.rest.util;

import javax.xml.bind.annotation.XmlElement;
import javax.xml.bind.annotation.XmlRootElement;
import java.util.ArrayList;
import java.util.List;

@XmlRootElement(name = "sqlQueryResults")
public class SqlQueryResults {
	@XmlElement(name = "sqlQueryResult", required = true)
	List<SqlQueryResultColumns> sqlQueryResults = new ArrayList<>();
	
	public List<SqlQueryResultColumns> getResults() {
		return sqlQueryResults;
	}

	@Override
	public String toString() {
		return String.valueOf(sqlQueryResults);
	}
}

import javax.xml.bind.annotation.XmlElement;
import javax.xml.bind.annotation.XmlRootElement;
import java.util.ArrayList;
import java.util.List;

@XmlRootElement(name = "sqlQueryResultColumns")
public class SqlQueryResultColumns {
	@XmlElement(name = "sqlQueryResultColumn", required = true)
	List<String> sqlQueryResultColumns = new ArrayList<>();
	
	public List<String> getColumns() {
		return sqlQueryResultColumns;
	}

	@Override
	public String toString() {
		return String.valueOf(sqlQueryResultColumns);
	}
}

Open Source Document Management System | OpenKM

create mysql querys using Webservice/Rest

create mysql querys using Webservice/Rest

Re: create MySQL querys using Webservice/Rest

Re: create MySQL querys using Webservice/Rest

Re: create MySQL querys using Webservice/Rest

Re: create MySQL querys using Webservice/Rest

Re: create MySQL querys using Webservice/Rest

Re: create MySQL querys using Webservice/Rest

Re: create MySQL querys using Webservice/Rest