• create mysql querys using Webservice/Rest

  • Do you want to create a native client or integrate with third party applications: webservices are the solution.
Do you want to create a native client or integrate with third party applications: webservices are the solution.
Forum rules: Please, before asking something see the documentation wiki or use the search feature of the forum. And remember we don't have a crystal ball or mental readers, so if you post about an issue tell us which OpenKM are you using and also the browser and operating system version. For more info read How to Report Bugs Effectively.
 #39844  by MattFisch2
 
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;
}
 #39874  by jllort
 
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/
 #39877  by MattFisch2
 
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!
 #39888  by jllort
 
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 {
 #39897  by MattFisch2
 
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!
 #39906  by jllort
 
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);
	}
}

About Us

OpenKM is part of the management software. A management software is a program that facilitates the accomplishment of administrative tasks. OpenKM is a document management system that allows you to manage business content and workflow in a more efficient way. Document managers guarantee data protection by establishing information security for business content.