Monday, April 9, 2012

SQL Server - Restore Database to different instance/DB name

Before executing the restore command, an empty DB with the new DB name, db_bbb in this example, have to be created under the SQL Server instance you like to move to.

Restore Database db_bbb  -- the New DB Name
from disk = 'D:\backup\db_aaa.bak'  -- Original DB Backup with the logical name db_aaa
with replace,
move 'db_aaa' to 'D:\data\db_bbb.mdf', -- Move the logical DB to the physical path with the new DB name
move 'db_aaa_log' to 'D:\data\db_bbb_log.ldf' -- Move the Logical Log to the physical path with the new "DB name_log"



--Set Single user Mode before restore:
ALTER DATABASE [db_bbb] SET SINGLE_USER

--Set Multi user Mode after restore:
ALTER DATABASE [db_bbb] SET MULTI_USER

--Show Logical name of DB
RESTORE FILELISTONLY
FROM DISK = 'D:\backup\db_aaa.bak'

use db_bbb
select FILE_ID, name as [logical_file_name], physical_name

from sys.database_files

Alter database [db_bbb] modify file ( name = db_aaa, newname = db_bbb  )
Alter database [db_bbb] modify file ( name = db_aaa_log, newname = ab_bbb_log )




Thursday, February 23, 2012

jqGrid - editCell not allows edit?

I spend an hour or two to figure out I forgot to return true in an empty beforeSelectRow event I had added, and this made the grid didn't allow cell editing...

Hope it may saves you a bit of times.

BTW, if you're looking for how to change the column editable in run time, here is an example:

var grid1 = $('#grid1');   
    grid1.jqGrid({
...
colNames:[ 
            'name','note','new'
 ], 
 colModel:[
{name:'name',index:'name',editable=false},
{name:'note',index:'note',editable=true},
{name:'new',index:'new',editable=false}
],
cellEdit:true,
cellsubmit: 'clientArray',
...

beforeSelectRow : function(rowid) {        
            var data = grid1.jqGrid('getRowData', rowid);
            var cm = grid1.jqGrid('getColProp','note'); 

            cm.editable = (data.new=='Y') ? true:false; //'note' will be allowed to edit if 'new' is 'Y'
            return true;
        },

javascript - call an inner function from outside

function outer(){
        var newObj = new Object();
        function inner(x){
                alert(x);
        }
        newObj.inner = inner;
        return newObj;
}
   
var outerObj = new outer(); 
outerObj.inner(5);

Wednesday, February 22, 2012

jqGrid - Move/Add - buttons/ toolbar/ pager to Top

$('#grid1).jqGrid({
...
pager: $('#pager1'),    
viewrecords: true,       
toppager:true,
...});


$('#grid1').jqGrid('navGrid',"#pager1", {edit:false,add:false,del:false,search:false,refresh:false,cloneToTop:true}); //You can disable/enable any jqGird default buttons you like


//Add a custom button on the top-pager
$('#grid1).jqGrid('navGrid','#grid1_toppager_left')
    .navButtonAdd('#grid1_toppager_left',{
        caption:"click me",
        buttonicon:"ui-icon-lightbulb",
        id: "lightbulb1",
        onClickButton: function(){       
            alert('clicked');
        }
    });

//Optional, Hide the bottom pager
$('#pager1').hide();



jqGrid - Server side paging sorting implementation with PHP, SQL Server

Recently, I faced to load a result set with around 5 thousand records, and found that the overall performance could not be accepted.

Therefore, server side paging is the way to go, and I found that there are only a few examples on the net to demonstrate how to do it.

So, I hope my simple example here can help you if you're also looking for it.

javascript :

var mygrid01 = $('#mygrid1');
    mygrid01.jqGrid({
        url:'php/loaddata.php',
        datatype: "json",        
        mtype: 'GET',
        colNames:[ 
            'customer_id','name','address'
        ],
        colModel:[    
            {name:'customer_id',index:'customer_id'},
            {name:'name',index:'name'},
            {name:'address',index:'address'}
        ],
        rowNum:50,
        rowList: [50,100,500],
        pager: '#pager1',    
        rownumbers: true,
        viewrecords: true,
        loadComplete: function() {
        }
    });

loaddata.php:

$limit=$_GET['rows']; 
$page=$_GET['page'];
$sidx=$_GET['sidx'];
$sord=$_GET['sord'];

...//your script of connection to db

$sidx = ($sidx=='')? 'customer_id':$sidx; //set default order if empty
$sord = ($sord=='')? 'asc':$sord;

$tsql = "select count(*) as count from customer";
$stmt = sqlsrv_query($conn, $tsql);
$row = sqlsrv_fetch_array($stmt);
$count = $row['count'];

$total_pages=ceil($count/$limit);
if ($page > $total_pages) { $page=$total_pages; }

$start = $limit*$page - $limit;
$end = $page * $limit;

$tsql = "WITH PAGED_CUSTOMERS  AS
    ( SELECT  customer_id, name, address,
     ROW_NUMBER() OVER (ORDER BY ".$sidx." ".$sord.") AS RowNumber FROM customer )

    SELECT customer_id, name, address  FROM PAGED_CUSTOMERS
   WHERE RowNumber BETWEEN ".$start." AND ".$end;

$stmt = sqlsrv_query($conn, $tsql);

$response = new stdClass();
$response->page = $page;
$response->total = $total_pages;
$response->records = $count;

$l_rowcount=0;
while ($row = sqlsrv_fetch_array($stmt)){ 
$response->rows[$l_rowcount]['id']=$l_rowcount+1; //set id starting from 1 instead of 0, and it's up to your design
    $response->rows[$l_rowcount]['cell']=array(
        $row['customer_id'],
        $row['name'],
        $row['address']
    );
    $l_rowcount++;               
}

echo json_encode($response);

Thursday, February 16, 2012

jqgrid - custom button in cell with jquery ui icon

colModel:[ 
{name:'mycolname',index:'mycolname', width:20,
     formatter: function(cellvalue, options, rowObject) {
        return '<span class="ui-icon ui-icon-folder-open" title="click to open" onclick="clickme('+options.rowId+');"></span>'; } //replace span with button  if you want a real button

},...],


function clickme(rowid) {
            alert(rowid);
}

Wednesday, February 15, 2012

jqGrid - how to use formatter rowobject json

I decided to change the datatype from xml to json, as I want to see the processing time since json is native to javascript and may have a bit benefit on the performance; another reason is I found the 'rowobject' argument in formatter is quite useful and easy to get the value of the row data rather than in xml datatype.

example:
colModel:[
{name:'account', index:'account', width:100} ,
{name:'credit', index:'credit', hidden:true} ,
{name:'name', index:'name', width:50,
                formatter: function(cellvalue, options, rowObject) {
                    var color = (rowObject[1]<=650 || rowObject['credit']<=650) ? 'red':'black'; 
//when the grid first load, it used integer as the index of the array, after loadComplete, the grid will use the column name as the index.
                    return '<span style="color:'+color+';">' + cellvalue + '</span>';
                }

}, ...]

jqGrid - php throw custom server error/exception and work with loaderror

If you want to throw a custom error/ exception in the server side script, php, and return to the jqGrid to capture your custom error code and error message, I hope my example here can help you.

After reading some threads and testing, it's actually quite simple and only need to throw an appropriate header in PHP, and then quit your script.

php:
header('HTTP/1.0 500 My custom error');

you can throw other status code but other than 2xx which represents a successful return. Also, you can see the reference here, Hypertext Transfer Protocol -- HTTP/1.1

javascript:
In the loadError event of jqGrid, you can get the status code and the message you return:

loadError : function(xhr,status,error) {
        alert(xhr.status+','+status+','+error); 
//alert 500, error, My custom error
},


jgGird - php json example with userdata

php:

header("Content-type: application/json; charset=utf-8");

$response = new stdClass();
$response->page = $page;
$response->total = $total_pages;
$response->records = $count;

$l_row=0;
$l_totalamt=0;

    while ($row = sqlsrv_fetch_array($stmt)){        
        $response->rows[$l_row]['id']=$l_rowcount+1; //set id starting from 1 instead of 0, and it's up to your choice.
        $response->rows[$l_row]['cell']=array(
            $row['inv_id'],
            $row['descp'],
            $row['amt']
        );       
        $l_totalamt+=$row['amt'];
        $l_row++;
    }
    sqlsrv_free_stmt( $stmt);

$response->userdata['amt']=$l_totalamt;

echo json_encode($response);

Amazon EC2 - publishing your website security group and port setting

If you cannot successfully see your webpage on internet but able to see it in the local remote server like me, it's probably about the tcp port 80 has not been open to public in your security group that is assigned to your instance.

Now, go to the AWS Management Console.

At the initial setting, the Amazon setup for my server instance, the security group is assigned to 'quicklanuch-1' .

Then, click on 'Security Groups' under 'Network & Security' on the left panel.
you should find your security group name there.

Select your security group name, and you can find the 'Detail' tab and 'Inbound' tab in the bottom panel.

In the 'Inbound' Tab, Add a custom TCP rule with port 80, then enjoy!

Tuesday, February 14, 2012

Amazon EC2 SQL Server Express local instance name

Finally, I decided to put my works to a public hosting server, in order to see how it performs on the world wide web.

After a day or two of reading the users' reviews and comparing the prices, I decided to give Amazon a try, since it offers 'monthly free tier' plan a year for the new user. It's almost free of charge if your system configuration, loading and usage is under their offering limit.

This is prefect for me since my testing data under 4Gb, and I just want to test how's the system working under a remote server at this state.

After spending some hours to set up PHP under IIS 7.5 on windows 2008 R2, I started to move my testing data to the SQL server Express. However, the weird thing is the SQL Manager cannot see the local instance in the drop-down list box. The lucky thing is I'm not alone, and found the answer on the AWS forum.

Just type in (local)\ec2sqlexpress as the server name and here you go.

Monday, February 13, 2012

jqgrid - column/cell with jquery autocomplete

I implemented a column with autocomplete in afterEditCell event and it works well!

var grid = $('#mygrid');

if (cellname=='myColumnName') {
                $("#"+iRow+"_"+cellname,"#grid1).autocomplete({
                    source: cList, //a json data from ajax and stored in javascript array, you can see how I get it from my previous post.
                    select: function( event, ui ) {
                        $(this).val( ui.item.value ); 
                        grid.saveCell(rowid,iCol);
                        return false;
                    }               
                }).data("autocomplete")._renderItem = function( ul, item ) {
                        return $( "<li></li>" )
                            .data( "item.autocomplete", item )
                            .append( "<a>" + item.label + "</a>" )
                            .appendTo( ul );
                }
            }

jquery ui autocomplete json setup key, "value"

 jquery autocomplete is easy to use and setup. The only thing I had spent time to figure out how it works is the configuration of the json. You must label the 'value' in the json, since I had used 'key' at the first time and it didn't work.

json example:
[{value:'ABC', label:'ABC company'},{value:'BBC',label:'BBC company'},...]

The 'value' is the item back to your input box when you selected from the list, and the 'label' is the item you see in the list.

The following is the example I implemented in the script:
$("#input1").autocomplete({
                    minLength: 0,
                    source: cList, //a json return from ajax
                    select: function( event, ui ) {
                        $(this).val( ui.item.value ); 
                        return false;
                    }               
                }).data("autocomplete")._renderItem = function( ul, item ) {
                        return $( "<li></li>" )
                            .data( "item.autocomplete", item )
                            .append( "<a>" + item.label + "</a>" )
                            .appendTo( ul );
                }

TCPDF - Chinese encoding UTF-08 fonts

At first, I was looking for FPDF instead of TCPDF, since FPDF seems light weighter than TCPDF, and people told their experience that TCPDF seems taking a bit longer processing time.

However, since my project requires to show the Chinese Characters, I found that I don't feel FPDF has sufficient support on UTF-08 encoding of Chinese Characters.

Then, I tried TCPDF and found that they give many examples on their webpage and show their product features. This helps me a lot to understand how it works and how to code.

The Chinese character encoding is pretty easy with the setting of UTF-08 and choosing the appropriate fonts, 'stsongstdlight' or 'kozminproregular'.

e.g.
$pdf = new TCPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);

For displaying traditional Chinese characters, I recommend to use font, 'kozminproregular'; 'stsongstdlight' is better for displaying simplified-Chinese characters.

e.g.
$pdf->SetFont('kozminproregular', '', 10);

Updated: 2013-05-02
Lastly, I found 'cid0cs' and 'cid0ct' work better.

Thursday, January 26, 2012

jquery ui tab - add close icon to a dynamic added tab

I dynamically added tabs as the following way:


tabpgIndex++;
href="#tabpg"+tabpgIndex;
var title = 'New Tab-'+tabpgIndex;
$('#tabs').append('<div id="tabpg'+tabpgIndex+'">{content}</div>'); //you can add your content here, or any dynamic content you want after this body've been build.

$('#tabs').tabs( 'add', href, title);  //jquery ui tab method

$('#tabs ul li a span').last().prepend('<span id="closeicon-'+tabpgIndex+'" class="ui-icon ui-icon-circle-close" style="float:right; cursor: pointer;"></span>'); // prepend the close icon with an id, so it attached to the right of the tab title without a line break

//with the id I given, I managed a click event to it, like this:
$('#tabs ul li a').on('click','#closeicon-'+l_tabpgIndex,function(){
        alert('close icon clicked');
        //close this tab.
 });

I don't know whether it works for you or not in your case, but it works for me, and hope it will work for you too.

jquery tab - check existing tab by tab lable/title

function checkTabExists(tabname) {
    var tabExists = false;
    $('#tabs ul li a').each(function(i) {
        if (this.text == tabname) {
            tabExists = true;
            $("#tabs").tabs("select", i); // if found, I immediately select to the tab.
        }            
    });  
    return tabExists;
}

jqGrid jquery - dynamic Caption with input textbox

when defining the jqgrid, set a variable to the option, caption, like following:

caption: myCaption,

then, whenever you want to set your caption, make html statement to the caption variable, myCaption; in the example I even try to make an input to the caption for another purpose.


e.g.
var data = $('#grid1').jqGrid('getRowData',1);    
myCaption = 'Customer ID: '+data.customer_id+'<br>';
myCaption = myCaption+'Name: <input id="icname'" type="text" value="'+data.cname+'" />';

finally, set the caption by the jqgrid method, setCaption.

$('#grid1').jqGrid('setCaption',myCaption);

for capturing the change of the input, you need to bind a change event to the input element with jquery .on function.

$('#gview_grid1').on('change','#icname', function() {
     alert( $(this).val() );
}

javascript - completely hide the jqgrid with wrapper div

<div id="gridWrapper" style="display: none;" >
<table id="grid1"></table>
</div>

and then, I can use

$('#gridWrapper').toggle();

to toggle the wrapper and show the grid when I want.

Friday, January 6, 2012

jqgrid - dynamic add/remove subgrid in run time

I'm using jqGrid 1.5.2, and I have a grid as subgrid, both parent and subgrid are in cell edit mode with no sorting allow.

In loadComplete event of the parent grid, I successful removed some of the subgrids which depended on the data of the row by unbind event to the element as following:

loadComplete: function() {
  var dataIds = $('#mygrid1').jqGrid('getDataIDs');
  for (var i = 0;i < dataIds.length; i++) {
    var data = $("#mygrid1").jqGrid('getRowData', dataIds[i]);
    if (data[i].hasChild='N') {
      var grid = $("#mygrid1");      
      currChild[dataIds[i]=$( "#"+dataIds[i]+"td.sgcollapsed",grid[0].clone(true,ture); //Before unbind, store up the event and data of the element in an array for dynamically added back
      $("#"+dataIds[i]+"td.sgcollapsed",grid[0]).unbind('click').html(''); //then, it's safe to remove 
    } 
} }

Then, I allow user to change the data[i].hasChild in the parent gird, then I call this dynamically change in afterSaveCell event as follow:

if (change/toggle happened) {
var grid = $("#mygrid1"); 
  if (data[rowid].hasChild=='Y') {
   $("#"+rowid+" td.sgcollapsed", grid[0].replaceWith(currChild[rowid]); //replace the element with the clone
  } else {
    grid.collapseSubGridRow(rowid); //require to collapse the subgrid before remove
    currChild[rowid]=$("#"+rowid+"td.sgcollapsed",grid[0].clone(true,ture); //Before unbind, store up the event and data of the element again in its array
    $( "#"+rowid+"td.sgcollapsed",grid[0]).unbind('click').html(''); //then, it's safe to unbind again
}}

Tuesday, January 3, 2012

Example: php json_encode json string put into javascript object array

//javascript:
...
//myjsonstring will catch the string returned from the url, getmydata.php
var myjsonstring = $.ajax({
url: 'getmydata.php',
async: false,
success: function(data, result) {
if (!result) { alert('Failure to retrieve the items.'); }
}
}).responseText;

var myDataStore =  $.parseJSON(myjsonstring);
//then, I can get any field in any row that are stored in myDataStore
// e.g. var l_fname = myDataStore[0].firstname; //the first row of data

//getmydata.php
...
$tsql = "EXEC proc_sel_my_data ?, ?"; //says 2 args
$param1 = "*"; $param2 = "*";
$params = array( &$param1, &$param2);
sqlsrv_query($conn,"SET CHARACTER SET 'utf8'"); //I'm using sqlserver
if ($stmt = sqlsrv_query( $conn, $tsql, $params)) {
$data = array();
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)){ //fetch each row and put into an array
$data[] = $row;
}
// print_r($data);
} else { echo 'Query fail.';}

echo json_encode($data); // encode the $data array to json, and it'll be return to ajax

p.s. PHP 5.3.8, Apache 2.2.21, PHP SQL Server 2.0.1, jquery 1.6.2

jqGrid - set select option width

I did it in loadComplete event: (or, you can do it when you define the colModel)

$('#grid1').setColProp(mycolname, { editoptions: {
value: myvalues(), // a function to get the select options
dataInit: function(elem){ $(elem).width(70); } //set the width you want
}});

jqGrid - dynamic Enable/Disable column editable

I did it in beforeSelectRow event of jqGrid (cell edit mode):

beforeSelectRow: function(id) {
var data = $("#grid1").jqGrid('getRowData', id);
var cm = $('#grid1').jqGrid('getColProp',mycolname);// mycolname is the column you're going to enable/disable
if (data.myfield == 'Y') {
cm.editable = false;// Not Editable
} else {
cm.editable = true;// Editable
}}

jqGrid - Remove subgrid/ expand sign on selected row

I did it in jqgrid loadComplete event:

loadComplete: function() {
var dataIds = $('#grid1').jqGrid('getDataIDs');
for (var i = 0;i < dataIds.length; i++) {

var data = $("#grid1").jqGrid('getRowData', dataIds[i]);
if (data.myfield =='Y') { // a condition {optional}
$("#"+dataIds[i]+"td.sgcollapsed",$('#grid1')).unbind('click').html('');}
}}