Click here to Skip to main content
Click here to Skip to main content

MySQL-Fullltext: Start for a simple search-engine using MySQL

, 8 Sep 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
MySQL-Fullltext: Start for a simple search-engine using MySQL C-API, WCF, and jQuery.

Introduction

The article covers the start implements a simple search engine in C.

The first version released on the 08/22/2013 performs only Queries. The new version implements CRUD functions, which means, you can create new record, read records, update records and delete records. The new versions utilize the jQuery plugins DataTables and editable. The code from version 1 was cleaned up, and null values are better handled.

I normally use SQL-Server and C#. But the friend i teach C/C++ programming want's to stay away from Microsoft. In the past MySQL wasen't a database for me, because in the standard installation it lacks the support of transactions, but its getting mature. I use MySQL 5.6 64 bit with a innodb as Database and Unicode(utf8) encoding, which is the default for new databases.

Freetext is a fresh new feature for innodb, it was first introduced with version 5.6 of MySQL.

I generally prefer C++ over C, even in small projects: it's nice not to know all the functionnames, but have some classes with the needed operations bundled and nice intellisense support. in C++ you too have the STL or Boost for collections and strings.

The C++ interface of MySql is rather small, while there's a huge matured C-Interface, so i decided to use the C-Interface.

The C-dll is published with WCF for Ajax requests. In Visual Studio Ultimate 2012 i used the C#  "WCF Service Application" Template. I searched for ways to host the WebService in C++, but only found some samples for calling WebServices in managed C++.

The userinterface for the query only step is a html Page which uses JQuery and autocomplete from Jquery-UI, the page is added to the Site of the "WCF Service Application", the project is named VisionWeb. The page is named "DefaultQueryOnly.html".

The Webpage looks like this:

Search Page

For version 2 i used the file "default.html". Pages looks like this:

Search Page version 2

I configured the projects for framework 4.0, 64 bit and debug. If you use a 32 bit MySql server, you have to change this. You must leave the option UNICODE on its default value true.

Configuring for MySQL

You probably will open The VisionDAL project from the VisionSmall Solution, perhapst you have to modify the linkage of the MySQL C-Interface. I here describe how to setup the MySQL-Interface in an new project: check if these settings, are right for you, in special the path to the mysql.lib file and VisionDAL.dll.

In Visual Studio add a project VisionDAL, using the template "Other Languages/Visual C++/Empty Project". In the wizard, you only need to change the "Application type" to DLL. Rename VisionDAL.cpp to VisionDAL.c, this is enough to tell Visual Studio to switch from compiling C++ to C. Add a header file named "VisionDAL.h" to the project.

In the Solution Explorer right click on the VisionDAL project and choose Properties. Under "Configuration Properties"/Linker/Input choose "Additional Dependencies" and add libmysql.lib to the path, don't forget the separating ";".

Under "Configuration Properties"/Linker/General choose "Additional Library Directories" for me add C:\Program Files\MySQL\MySQL Server 5.6\lib> . Now we have linked in the C-Interface, but the dll implementing the calls in libmysql.lib must be in the system search path for excecutables: from the Control Panel choose System, click "Advanced system settings", "click Enviroment Variables" under "System Variables" choose Path and add the libmysql.lib path (dll is in the same folder with the lib file): ;C:\Program Files\MySQL\MySQL Server 5.6\lib .

We need to have VisionDal.dll in our Path too, IIS won't find it when you put the dll into its bin folder of the website. Add <path to the solution>/x64/debug to the path. I needed to reboot to get this setting effective, eventually helps restarting the Application Pool, by toggling the settting   "Managed Pipeline Mode" - you can do this in IIS Manager. When the website gets it first request it will load VisionDAL.dll, when you now rebuild the project, you will get a write error on the VisionDAL.dll: to fix it reset the Application Pool or use a tool like unlocker.

Than we specify the include properties for VisonDAL. Under "Configuration Properties"/"C/C++" add your MySQl header file path, for example: C:\Program Files\MySQL\MySQL Server 5.6\include .

Next we switch "Precompiled Headers" to "Not Using Precompiled Headers" which is under "C/C++"/"Precompiled Headers" . To avoid error messages when using strcpy and fopen set the Preproccessor definitions: USE_STANDARD_FILE_FUNCTIONS and _CRT_SECURE_NO_WARNINGS under "C/C++"/Preprocessor/"Preprocessor Definitions".

When you link now the mysqllib references are not resolved, because they are 64 bit procedures. Fix this by opening the project properties from VisionDAL, choose the button "Configuration Manager" and set the platform to x64.

Now we create our sample database named Vision

Open the MySql Workbench under SQL Development open your instance. A new window "SQL File 1" appears. Double Click on the Sql.txt file in the VisionDAL project. Copy all the content to the clipboard, paste in into the "SQL File 1" window in the workbench. Click on the flash icon (the third icon on the left) to create the sample database.

Next we need the usual information for a database login.

We have a configuration file for this: <installation director>VisionSmall\x64\Debug\VisionConfiguration.txt, which looks like this:

Host: localhost
User: root
Password: frob4frob
Database: vision
Port: 3306

Modify the values to match your MySQL-Configuration.

The Vision database

There is only one table in the database

    CREATE TABLE `document` (
  `DocumentID` int(11) NOT NULL AUTO_INCREMENT,
  `Title` varchar(255) DEFAULT NULL,
  `Text` text,
  PRIMARY KEY (`DocumentID`),
  FULLTEXT KEY `ft` (`Title`,`Text`),
  FULLTEXT KEY `ftTitle` (`Title`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

When we search we use the fulltext index named `ft`, when looking for autocomplete words we use the fulltext named 'ftTitle'.

If you have a fulltext index including many fields, you can choose in Microsoft SQL Server when querying, which fields should be included in the search. In MySql always all fields from the fulltext index are searched, so we have to specify the additional fulltext index 'ftTitle'.

Querying MySQL with the C-Interface

First we need to connect to the database and get a MYSQL pointer for further access:
        MYSQL *Connect(){
	MYSQL *conn; // Connection

	// Connect to MySQL 
	conn = mysql_init(NULL);
	if(mysql_real_connect(
		conn, Configuration.Host, Configuration.User, Configuration.Password,
		Configuration.Database, Configuration.Port, NULL, 0) == NULL) {
			fprintf(stderr, "sorry, no database connection ...\n");
			return NULL;
	}
	return conn;
}
    

At startup we fill the global Configuration struct with the values from the configuration file VisionConfiguration.txt, which should be in the same directory as VisionDAL.dll. The routine to read the settings is ConfigurationRead. To get the path of the currently executing module it uses GetModuleFileName from the Win32-Api:

TCHAR *GetExecutablePath(){
	HMODULE hMods[1024];
    	HANDLE hProcess;
    	DWORD cbNeeded;
    	unsigned int i, j;
	DWORD processID = 0L;
	TCHAR fileName[MAX_PATH];
	TCHAR *szModName=(TCHAR *) malloc(MAX_PATH*2);
	int size=MAX_PATH*2;

	processID = GetCurrentProcessId();
	// Get a handle to the process.
    	hProcess = OpenProcess( PROCESS_QUERY_INFORMATION |
                            PROCESS_VM_READ,
                            FALSE, processID );
	if (NULL == hProcess)
        return NULL;
	if( EnumProcessModules(hProcess, hMods, sizeof(hMods), &cbNeeded)){
        for ( i = 0; i < (cbNeeded / sizeof(HMODULE)); i++ ){
			szModName[0] = 0;
			fileName[0]=0;
            // Get the full path to the module's file.
            if ( GetModuleFileNameEx( hProcess, hMods[i], szModName,
                                     size )){
		int lastBackSlash=0;
		int k=0;
		for(k=0; k <(int) wcslen(szModName) -1; k++){
			if(szModName[k] == '\\') lastBackSlash = k;
			}
			if(lastBackSlash == 0) continue;
			szModName[lastBackSlash]=0;
			for(k=lastBackSlash + 1, j=0; szModName[k] != 0; k++, j++){
				fileName[j]=szModName[k];
			}
			fileName[j]=0;
			wStrLower(fileName);
			if(!wcscmp(L"visiondal.dll", fileName)){
					return szModName;
			}
            }
        }
	}
	return NULL;
}


 

There is only one routine we want to expose: GetDocuments. Definition in the header file:

#define FORMAT_TEXT 0
#define FORMAT_JSON 1>
#define FORMAT_DATATABLE 2 
__declspec(dllexport) wchar_t*   __cdecl GetDocuments(TCHAR *search, int format, int forAutocomplete);

Definition in the source file:

__declspec(dllexport) wchar_t* GetDocuments(TCHAR *search, int format, int forAutocomplete)

__declspec(dllexport) on the declaration and definition effectuate that the call is added to the file VisionDAL.lib and exported in the VisionDAL.dll file. __cdecl defines how to call the procedure, here we use C-Style calling conventions. TCHAR is a define which is the same as WCHAR when the UNICODE definition is set otherwise its a simple char, in our case UNICODE is turned on.

    Note that there are different Unicode-Formats:
  • in C code we use a 2 byte value to denote a char value
  • in MySql and the .Net framework the format is UTF-8, which means one byte is used for each character and only on demand more than one byte are used
  • in Console Applications you usually use one byte for each char and use Codepage 850 for the values greater than 127.

The Parameter format is FORMAT_TEXT or FORMAT_JSON, to toggle the output between text and JSON. Version 2 adds the format FORMAT_DATATABLE, which formats the output in the standard format for DataTable.

If forAutocomplete is true only the Title is searched and returned.

VisionDALClientConsole

VisionDALClientConsole is a tiny Windows Console Application, to test our GetDocuments procedure.

It has a reference to the VisionDAL project set. It's outputfiles go to VisionSmall\x64\Debug together with the output from VisionDAL.

VisionDALClientConsole asks for the search string, the wildcard is "*", it searches the columns title and text and outputs the text from the GetDocuments call.

A sample run:

Console Output

The main routine:

int _tmain(int argc,TCHAR* argv[])
{
	char c;
	TCHAR *result;
	TCHAR *search = (TCHAR *)malloc(1000*2);
	char *searchA = (char *)malloc(1000);
	int retval = 1;
	char buffer[32000];

	buffer[0]=0;
	printf("Search for: ");
	/* wscanf doesn't get umlauts */
	if(scanf("%[^\n]", searchA) <= 0){
		printf("Could not read input - retrieving all Documents \n");
		*search=0;
	}else{
		MultiByteToWideChar(850,0,searchA, -1,search, 999);
	}
	result=GetDocuments(search, FORMAT_TEXT, 0);
	if(result == NULL){
		retval = 0;	
	}else{
		WideCharToMultiByte(850,0,result, -1,buffer, 32000,NULL,NULL);
		printf("%s", buffer);
	}
	fflush(stdin);
	printf("Press RETURN Key to Exit\n");
	getchar();
	return retval;
}  

In Microsoft C V. 12 you have routines to deal with Unicode-16 strings. The have a starting additional w or replace str with wcs, for example: wscanf, wprintf instead of scanf and printf and wcslen instead of strlen.Using wscanf did not get the umlauts right. I used MultiByteToWideChar using codepage 850 to get the wide chars and WideCharToMultiByte to convert back to chars.

Querying the MySQL Database

Above i showed how to connect to the database and get a MYSQL pointer named conn.

Next we build the SQL-Query, here the code for generating the list for autocomplete:

mysql_query(conn, "SET NAMES 'utf8'");
if(forAutocomplete){
	if(search == NULL || wcslen(search) ==0){
		WideCharToMultiByte(CP_UTF8,0,L"SELECT Title from Document LIMIT 20",-1,sql,1000,NULL,NULL);
	}else{
		wsprintf(lbuffer, L"SELECT Title, match(Title) against('%ls' IN BOOLEAN MODE) as Score from Document where match(Title) against('%ls' IN BOOLEAN MODE) > 0.001 order by Score Desc LIMIT 20",
			search, search);
		WideCharToMultiByte(CP_UTF8,0,lbuffer,-1,sql,1000,NULL,NULL);
	}
}

match(Title, Text) against('%ls' IN BOOLEAN MODE)searches for the search string in the columns Title and Text which returns a value how good the match is. Only documents with a Score greater 0.001 are displayed, the result is ordered by the score.

IN BOOLEAN MODE effectuatethat the search for the multiple words are interpreted as or.

In the search string, you can use "*" as wildchar, which will match 0 to n characters. For example "as*" will find ASP. The search is not case-sensitive. Some things are annoying: "as**" won't find anything; "*sp"  or "*"won't match anything - in Microsoft SQl Server, you can match wildcards at the beginning of a string.

Getting the data

...
if(mysql_query(conn, sql)) {
    fprintf(stderr, "%s\n", mysql_error(conn));
    fprintf(stderr, "%s\n", sql);
    return NULL;
}
 ...
// Process results
result = mysql_store_result(conn);
...
    while((row = mysql_fetch_row(result)) != NULL) {
        ...
	    }else if(format == FORMAT_JSON){
			if(!forAutocomplete){
				MultiByteToWideChar(CP_UTF8,0,row[0], -1,buffer, 255);
				wsprintf(resultBufferp,L"{\"DocumentID\": %s, ", buffer);
				resultBufferp+=wcslen(buffer)+wcslen(L"{\"DocumentID\": , ");
				if(row[1]){
					MultiByteToWideChar(CP_UTF8,0,row[1], -1,buffer, 255);
					wsprintf(resultBufferp,L"\"Title\": \"%s\", ", buffer);
					resultBufferp+=wcslen(buffer)+wcslen(L"\"Title\": \"\", ");
				}else{
					wsprintf(resultBufferp,L"\"Title\": null, ");
					resultBufferp+=wcslen(L"\"Title\": null, ");
				}
				if(row[2]){
					MultiByteToWideChar(CP_UTF8,0,row[2], -1,buffer, 32000);
					wsprintf(resultBufferp,L"\"Text\": \"%s\"},", buffer);
					resultBufferp+=wcslen(buffer)+wcslen(L"\"Text\": \"\"},");
				}else{
					wsprintf(resultBufferp,L"\"Text\": null},");
					resultBufferp+=wcslen(L"\"Text\": null},");
				}
			}
	    ...
        }
    ...
	}

mysql_query sends the query to the server. mysql_store_result prepares the result as a collection, which you iterate using mysql_fetch_row(result). The row is an array of strings, ignoring which datatype the column has. I prefer the typed columns which you have in ADO.Net. In .Net we probably would use a StringBuilder to arregate the result string, here we allocate a char[] with malloc and increment the pointer resultBufferp, to which we write. I use MultiByteToWideChar to convert to WCHAR.

The JSON Format

I decided to use the lighweight JSON-Format, for communication from the Webpage using Ajax to the Webservice, instead from XML.

The JSON-output when using FORMAT_DATATABLE looks like this

[{"DocumentID": 1, "Title": "ASP MVC 4", "Text": "Was für Profis"},{"DocumentID": 2, "Title": "JQuery", "Text": "Hat Ajax Support"},{"DocumentID": 3, "Title": "
WebServices", "Text": "Visual C++ kanns nicht"},{"DocumentID": 4, "Title": "Boost", "Text": "Muss Extra installiert werden"}]
    

The JSON-output looks like this when the parameter autocomplete is true:

["ASP MVC 4","JQuery","WebServices","Boost"]

DataTable expects a 2 dimensional array which look like this:

        [[1, "ASP MVC 4", "Was für Profis"],[2, "JQuery", "Hat Ajax Support"]]

But its possible to use a array of objects, as shown above, for format FORMAT_DATATABLE.

The "[]" denotes the start and end of array, "{}" mark the beginning and end of a objetct. In a object the part before the ":" is the property name, the part after is the value. This is nearly the same, as when you code the values in Javascript. With the Javascript-Command JSON.parse you get a full fledged object, whose properties are accessable using the usual "." notation.

Hosting the Webservice for the GetDocuments method

I created the project VisionWeb using the template "Visual C#/WCF/WCF Service Application", what added the needed references like System.ServiceModel.

Next we use NuGet to add the needed Javascript libraries. Choose "Tools/Library Packet Manager/Package Manager Console" and issue the commands:

 Install-Package jQuery
Install-Package jQuery.UI.Combined

Next we define the service contract in the file "App-Code/IVisionService.cs":

 namespace VisionServices
{
    [ServiceContract(SessionMode = SessionMode.Allowed)]
    public interface IVisionService
    {
        [OperationContract]
        [WebInvoke(
            Method = "POST",
            BodyStyle = WebMessageBodyStyle.WrappedRequest,
            RequestFormat = WebMessageFormat.Json,
            ResponseFormat = WebMessageFormat.Json)]
        string GetDocuments(string search, int format, int forautocomplete);    
    }   
}

The WebInvoke attribute insures that the service can be called by Ajax calls. As Method i choosed POST which sends the parameter in the body of the HTTP-Request. The alternative GET, would encode and unveil the parameters in the Url.

We specify that request and response are send in JSON-Format. BodyStyle = WebMessageBodyStyle.WrappedRequest must be used when more than one parameter are used. You can use WebMessageBodyStyle.Bare if you have zero or one Parameter.

Implementation of the Webservice

We define the implementation of GetDocuments in "App-Code/VisionService.cs":

namespace VisionServices
{
    public class PInvoke
    {
        [DllImport("VisionDAL.dll", CharSet = CharSet.Unicode)]
        [return: MarshalAs(UnmanagedType.LPWStr)]
        public static extern string GetDocuments(string search, int format, int forAutocomplete);
    }
    public class VisionService : IVisionService
    {
        public string GetDocuments(string search, int format, int forautocomplete)
        {
            string result = PInvoke.GetDocuments(search, format, forautocomplete).ToString();
            return result;
        }
    }
}

Implementation of VisionService.svc

<%@ ServiceHost Language="C#" Debug="true" Service="VisionServices.VisionService" CodeBehind="App_Code\VisionService.cs" %>

This defines the endpoint on which our service can be called to "http://<your webserver>:<your port>VisionService.svc", the url to call our GetDocuments function is "http://<your webserver>:<your port>/VisionService.svc/GetDocuments".

The Web.config

<?xml version="1.0"?>
<configuration>
  <appSettings/>
  <system.web>
    <httpRuntime/>
    <compilation debug="true"/>
  </system.web>
  <system.serviceModel>
    <services>
      <service name="VisionServices.VisionService">
        <endpoint address="" binding="webHttpBinding" contract="VisionServices.IVisionService" behaviorConfiguration="webHttpEndpoint"/>
        <endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange"/>
      </service>
    </services>
    <behaviors>
      <endpointBehaviors>
        <behavior name="webHttpEndpoint">
          <webHttp helpEnabled="true"/>
        </behavior>
      </endpointBehaviors>
      <serviceBehaviors>
        <behavior>
          <serviceMetadata httpGetEnabled="true" httpsGetEnabled="true"/>
          <serviceDebug includeExceptionDetailInFaults="true"/>
        </behavior>
      </serviceBehaviors>
    </behaviors>
    <serviceHostingEnvironment aspNetCompatibilityEnabled="false" multipleSiteBindingsEnabled="true"/>
  </system.serviceModel>
  <system.webServer>
    <modules runAllManagedModulesForAllRequests="true"/>
    <directoryBrowse enabled="true"/>
  </system.webServer>
</configuration>

This are the settings to allow Ajax-calls. There are quite a lot options for configuring WCF. You might look at further documentation like [2], which is on Safari.

<endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange"/> defines the endpoint for metada exchange. From the metadata, you can automatically generate code to get a proxy for the Webservice, for example with svcutil. Choose "Programs/Microsoft Visual Studio 2012/Visual Studio Tools/Developer Command Prompt for VS2012". Enter svcutil http://localhost:8001/VisionService.svc. A file VisionService.cs is generated, in other cases a file containing the configuration of the Webservice are generated too.

Note that Visual Stdio has a graphical Editor for the Webservice- Configuration. Its under "Tools/WCF Service Configuration Editor".

Hosting the website

Start "Settings/ControlPanel/Administrative Tools/Internet Information Services (IIS) Manager". When its missing install iis. Navigate to "Application Pools", grab the name of a pool running .Net Framework Version 4.0*, or add a new Application Pool.Navigate to the "Sites" node, right click it and choose "Add Website...". Use Vision as Site name, for the Application Pool choose a pool running .Net Framework 4, as physical path use <vision installdir>/VisionWeb, set the port to 8001 . Choose Properties on the VisionWeb project, choose Web, check "Use Custom Web Server", enter http://localhost:8001 as Server Url. You can host the site with other options, for example in IIS Express, but if you don't want to change the files Default.html and DefaultQueryOnly.html , you have to set the port to 8001 .

TH HTML/JQuery page

In the VisionWeb  version 1 project is a single HTML-Page named "DefaultQueryOnly.html", which contains the HTML and Javascript content. In version 2 the solution has "Default.html" as startpage.

Here's again who the webpage looks like:

Search Page
 <html>
<head>
    <title>Search</title>
    <script src="Scripts/jquery-2.0.2.js"></script>
    <script src="Scripts/jquery-ui-1.10.3.js"></script>
    <link href="Content/themes/base/jquery.ui.autocomplete.css" rel="stylesheet" />
    <style type=text/css>
        .ui-menu-item {
            background: White;
        }
       .ui-helper-hidden-accessible { display:none; }
    </style>
</head>

´The <code>html</code> states that this  has a doctype of  HTML 5. Then we include the necessary javascript files. From JQuery-UI we use only the autocomplete widget, for which we include it's css file.

For the autocomplete object, which contains the class .ui-menu-item, we set the background to white, otherwise its transparent and the content of the table shows through..ui-helper-hidden-accessible { display:none; }Removes a annoying help-message from the autocomplete-widget.

<form>
    <label for="search" >Search:</label>
    <input type="text" id="search", name="search"  />
    <input type="button" id="update" name="update" value="Update"  />
    <div id="result"></div>
</form>

The elements in the form are given ids, so that you can retrieve them with JQuery like $('#result'). Instead of the shortcut $ you can use jQuery, for example jQuery('#result'). The Javascript call document.getElementById('result'), has the same effect, but  JQuery supports all kinds of css-selectors and more.

I use unobtrusive Javascript, which means the html-code is not mixed with javascript. THe eventhandler are set in the function $(document).ready(function () which is called when the page has loaded and is ready.

function GetDocuments(e) {
    var searchstring = AddWildcards($('#search').val());
    $.ajax({
        type: 'POST',
        url: 'http://localhost:8001/VisionService.svc/GetDocuments',
        dataType: 'json',
        crossDomain: true,
        data: JSON.stringify({ search: searchstring, format: 1, forautocomplete: 0 }),
        processData: true,
        contentType: "application/json ; charset=utf-8",
        success: function (json, textStatus) {
            var result = JSON.parse(json);
            var display;
            display = "";
            display += "<table id='mytable' border=2>  <thead><tr><th style='text-align:left' >ID</th><th style='text-align:left' >Title</th><th style='text-align:left' >Text</th></tr></thead>";
            display += "<tbody>";
            $.each(result, function (index, value) {
                display += "<tr>";
                display += "<td>" + value.DocumentID + "</td>";
                display += "<td>" + ((value.Title != null)?value.Title:"") + "</td>";
                display += "<td>" + ((value.Text != null)?value.Text:"") + "</td>";
                display += "</tr>";
            });
            display += "</tbody></table>";
            $('#result').empty()
            $('#result').html(display);
        },
        error: function (xhr, textStatus, errorThrown) {
            alert('An error occurred! ' + (errorThrown ? errorThrown : xhr.status) + " xhr: " + xhr + " textStatus: " + textStatus);
        }
    });
}

The GetDocuments call executes, when you click on the Update-Button or press enter in the search fiels. It performs a fulltext query and displays the result in a HTML-Table:

We get into the variable searchstring the value of our search form field, and add the wildcard * to each of its words in AddWildcards, when it is not present. JQuery has AJAX support, for example the $.ajax call.

You can find a reference for this call here: jQuery.ajax().

The url: points to the address we configured in our WCF-application. As set in our WCF-Application, we use JSON as dataformat. In the function success:which is asynchronous called when the ajax call suceeds, we get in the variable json, the output from our GetDocuments call. A simple call to JSON.parse(json), gives us a fully fledged Javascript object, from which we generate a HTML-Table. The variable result is an array of Javascript objects. The jQuery $.each call iterates over the array and calls the given function with the index of the current array element and the object at the current position. With $('#result').html(display) we display our html string display as the html from our result div. Under data: we specify the parameters for the call as Javascriptobject which we prepare for the transport with the JSON.stringify call. When an error occures, the code after error:gets executed.

How autocomplete works.

At the beginning of our Javascript code we declare a global variable to hold the words for autocomplete in an array: var Documents = [];. The function GetAutocomplete fills the Documents array. The Autocomplete function:

 function GetAutocomplete(e) {
   var searchstring = $('#search').val();
    if (searchstring.length > 0) {
        if (searchstring[searchstring.length - 1] != "*") {
            searchstring += "*";
        }
    }
    $.ajax({
        type: 'POST',
        url: 'http://localhost:8001/VisionService.svc/GetDocuments',
        dataType: 'json',
        data:  JSON.stringify({ search: searchstring, format: 1, forautocomplete: 1}),
        processData: true,
        async: false,
        contentType: "application/json ; charset=utf-8",
        success: function (json, textStatus) {
            Documents = JSON.parse(json);
        },
        error: function (xhr, textStatus, errorThrown) {
            alert('An error occurred! ' + (errorThrown ? errorThrown : xhr.status) + " xhr: " + xhr + " textStatus: " + textStatus);
        }
    });
}

This looks quite similar to the GetDocuments function.The success function only updates the Documents variable, with a JSON.parse over the output of our Webservice. Note the async: false, which causes the call to be synchronized.The autocomplete widget will call our GetAutocomplete function and immediately display the Documents.

Initialization from the autocomplete widget in the $(document).ready(function ():

$("#search").autocomplete({
    source: function (request, callback) {
        GetAutocomplete();
        callback(Documents);
    },
    open: function (event) {
        var $ul = $(this).autocomplete("widget");
    }
});

You can find infos about autocomplete here autocomplete.

Handling from the [RETURN] key in the search box:

 $('#search').bind("keydown", GetInput);
function GetInput(e) {
            if (e.keyCode == 13) {
                e.preventDefault();
                GetDocuments(e);
                $('#search').autocomplete("close");
            }
        }

The e.preventDefault(); stops the processing of the current event.

Debugging

You can enter the url http://localhost:8001/VisionService.svc in your web browser. If there's a service activation error, a message will be displayed, for example that the VisionDAL.dll could not be loaded., or WCF is not installed, which will cause a message about a missing handler You can examine the http-Communication with a tool like Fiddler.

Debugging VisionDAL, when running VisionWeb doesn't work. Start Vision without debugging and attach to w3wp.exe.

VisionSmall Version 2 - implementing CRUD operations

Until now i showed you DefaultQuery, we switch over to Default,html.

We click "Tools/Library Packet Manager/Manage NuGet Packages for Solution...", search for jquery.datarables, and add the result package.

We add a global variable, called oTable to hold a reference for the created table. We change GetDocuments to use DataTable.

function GetDocuments(e) {
    var searchstring = AddWildcards($('#search').val());
    $.ajax({
        type: 'POST',
        url: 'http://localhost:8001/VisionService.svc/GetDocuments',
        dataType: 'json',
        data: JSON.stringify({ search: searchstring, format: 2, forautocomplete: 0 }),
        contentType: "application/json ; charset=utf-8",
        success: function (json, textStatus) {
            var result = JSON.parse(json);
            var display;
            display = "";
            display += "<table id='mytable' border=2>  <thead><tr><th style='text-align:left' >ID</th><th style='text-align:left' >Title</th><th style='text-align:left' >Text</th></tr></thead>";
            display += "<tbody>";
            display += "</tbody>";
            display += "</table>";
            if (oTable) {
                $('#mytable').remove();
                oTable = null;
            }
            $('#result').empty()
            $('#result').html(display);                   
            oTable = $('#mytable').dataTable({
                "aaData": result,
                "bPaginate": false,
                "bLengthChange": false,
                "bFilter": false,
                "bSort": true,
                "bInfo": false,
                "bAutoWidth": false
            });
            ...
    }
    ...
}

oTable = $('#mytable').dataTable(converts the HTML-Table to a DataTable. The contents oft the table apre passed by the "aaData": result, property. The table header is generated the some way as in version 1 by emitting HTML-Code. Instead you specify columns for the DataTable.

function makeSelectable(e) {
    if ($(this).hasClass('row_selected')) {
        $(this).removeClass('row_selected');
    }
    else {                
        $(this).addClass('row_selected');
    }
};

Implementing delete

First we make the tablerows selectable by calling oTable.$("tbody tr").click(makeSelectable) in our GetDocuments function. Later only the first column is selectable, bcause we appy editable on all columns, despite the first "ID" column.

/* Get the rows which are currently selected */
function fnGetSelected(oTableLocal) {
    var aReturn = new Array();
    var aTrs = oTableLocal.fnGetNodes();

    for (var i = 0 ; i < aTrs.length ; i++) {
        if ($(aTrs[i]).hasClass('row_selected')) {
            aReturn.push(aTrs[i]);
        }
    }
    return aReturn;
}
...
    $('#delete').click(function () {
        if(!oTable)return;
        var ids = new Array();
        var anSelected = fnGetSelected(oTable);
        if (anSelected.length == 0) return;
        for (i = anSelected.length - 1; i >= 0; i--) {
            // anSelected[i].cells[0].innerText works with ie and Chrome but not in Firefox(version 23.01)
            if (anSelected[i].cells[0].innerHTML != "" && anSelected[i].cells[0].innerHTML != null) {
                ids.push(parseInt(anSelected[i].cells[0].innerHTML));
                oTable.fnDeleteRow(anSelected[i], null, false);
            }
        }
        oTable.fnDraw();
        $.ajax({
            type: 'POST',
            url: 'http://localhost:8001/VisionService.svc/DeleteDocuments',
            dataType: 'json',
            data: JSON.stringify({"ids": ids}),
            contentType: "application/json ; charset=utf-8",
            asyn: false,
            success: function (json, textStatus) {
                var i = JSON.parse(json);
                if(i != ids.length) alert("Error: should delete " + ids.length  +  " but deleted " + i + " records");
            },
            error: function (xhr, textStatus, errorThrown) {
                alert('An error occurred! ' + (errorThrown ? errorThrown : xhr.status) + " xhr: " + xhr + " textStatus: " + textStatus);
            }
        });
    });
   ...

We iterate over the selected table rows from oTableand call the DataTable function fnDeleteRow which removes the given row from the table.

The ajax call is handled by DeleteDcouments in the WCF project:

public int DeleteDocuments(int[] ids)
{
    int retval = -1;

    IntPtr intPtr = Marshal.AllocHGlobal(ids.Length * sizeof(int));
    Marshal.Copy(ids, 0, intPtr, ids.Length);
    retval = PInvoke.DeleteDocuments(intPtr, ids.Length);
    Marshal.FreeHGlobal(intPtr);
    return retval;
}

Note that WCF is smart enough to automatically convert the JSON-Array into a C# int Array. But to call our C-routine, we have to prepare with the Marshal-functions a raw C array.

The C-Routine from the DAL:

...
if(mysql_query(conn, buffer)) {
...
printf("%i records found\n", retval=mysql_affected_rows(conn));

The routine first generates a SQL-string like

 Delete from Document where DocumentID in (2,4)

. mysql_query sends the SQL-string to the server. Note that mysql_query is used without regards if the call is a read, update, delete or new statment. mysql_affected_rows returns the number of rows, which were affected by the last mysql_query statement, in this case the number of deleted rows.

Implementing new

    ...
$("#new").unbind('click');
$('#new').click(function () 
{
    if(!oTable)return;
    var id=-1;    // generated on the server
    var Title=null;
    var Text=null;
    $.ajax({
        type: 'POST',
        url: 'http://localhost:8001/VisionService.svc/NewDocument',
        dataType: 'json',
        data: JSON.stringify({"title": Title, "text": Text }),
        contentType: "application/json ; charset=utf-8",
        aysnc: false,
        success: function (json, textStatus) {
            id = JSON.parse(json);
            if (id == -1) {
                alert("Error: inserting a new record failed");
                return -1;
            }
            var values = new Array(id, Title, Text);
            oTable.fnAddData(values);
            // var tds = oTable.$("tr:last-child td:gt(0)"); // doesn't work
            $("#mytable tr:last-child td:gt(0)").addClass("editable").editable(makeEditable, { "placeholder": "" });
            oTable.$("tbody tr:last-child").click(makeSelectable);
            $("#mytable tr:last-child td:eq(1)").click();
        },
        error: function (xhr, textStatus, errorThrown) {
            alert('An error occurred! ' + (errorThrown ? errorThrown : xhr.status) + " xhr: " + xhr + " textStatus: " + textStatus);
        }
    });
});
...

Currently Title and Text are unused. We generate in the database an empty record, which causes that a new DocumentID is generated, set in the new record and returned to the calling ajax function. The submitted SQL look likethis:

Insert Document(Title) values(NULL)

We add the new record to the table with the DataTablecalloTable.fnAddData(values). Then we need to make the row selectable and editable.

Implementing Update

We download jQuery editable from editable.


 In <code>GetDocuments</code>function we bind the click even from our New-button.<p>

</p><pre> ...
oTable.$('td:gt(0)').addClass("editable").editable(makeEditable,{ "placeholder": "" }); // when placeholder is not set empty cells display "Click to edit"
...
function makeEditable(value, settings) {
    var columnName = this.parentNode.parentNode.parentNode.rows[0].cells[this.cellIndex].innerHTML;
    var id = parseInt(this.parentNode.cells[0].innerHTML);
    $.ajax({
        type: 'POST',
        url: 'http://localhost:8001/VisionService.svc/UpdateDocument',
        dataType: 'json',
        data: JSON.stringify({ "id": id, "columnName": columnName, "newValue": value }),
        async: false,
        contentType: "application/json ; charset=utf-8",
        success: function (json, textStatus) {
            var i = JSON.parse(json);
            if (i != 1) alert("Error: should update " + ids.length + " but updated " + i + " records");
        },
        error: function (xhr, textStatus, errorThrown) {
            alert('An error occurred! ' + (errorThrown ? errorThrown : xhr.status) + " xhr: " + xhr + " textStatus: " + textStatus);
        }
    });
    return (value);
};

When an HTML-Element is made editable it edits the object, when you click it once. The new data is submitted. So every update modifies a single column.

The generated SQL looks like this:

Update Document set Title='new value' where DocumentID=4

References
[1]Michael Kofler;MySQL 5: Einführung, Programmierung, Referenz; Addison-Wesley Verlag 2009
[2]Steven Cheng; Microsoft Windows Communication Foundation 4.0 Cookbook for Developing SOA Applications; Packt Publishing 2010
[3]DataTables
[4]editable

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Winfried Wille
Web Developer Winfried Wille Systemberatung
Germany Germany
I am a Dotnet programmer working since 23 years as software developer and system engineer. I live in Hamburg, Germany.
Currently i'm playing around with HTML 5 and JQuery.
My application and blog are here http://w2ww.de.
Currently i work as freelancer.My hourly price is currently discounted to 25€, normal is 40€.
Follow on   Twitter

Comments and Discussions

 
QuestionFormatting - contains spurious HTML PinprotectorOriginalGriff26-Jul-13 19:39 
QuestionFormatting PinprofessionalKenneth Haugland26-Jul-13 12:30 
AnswerRe: Formatting PinprofessionalKenneth Haugland26-Jul-13 12:31 
AnswerRe: Formatting PinprotectorOriginalGriff26-Jul-13 19:35 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141216.1 | Last Updated 9 Sep 2013
Article Copyright 2013 by Winfried Wille
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid