sql Tip 3: getting DB schema in MYSQL
long time since my last post here, well i was bit busy , hopefully i will not stop bloging for long times again.
for this blog the title actually shouldn’t be SQL tip 3 , it should be MYSQL tip 3, since 
it is talking about something specific to MYSQL, but since i had two (very old) posts about sql tips SQL tip1 SQL tip2 ,well they were geniral but were demonistrated in MYSQL too.
back to the topic. i needed to know how to get the database schema for a MYSQL DB in one of my projects and knowing the how was not stright forward so thats why i think it will be good and usefull tip.
A very usefull information in this topic is that information on database tables is stored in a system table called “information_schema.tables “.However a better information is writting the query “select * FROM information_schema.tables ” will get you some of the tables that you never had created (system tables) + your tables. to exclude system tables you have to add a where condition
“select * FROM information_schema.tables where version=’10′“ this will show only the tables you have created. version is not related to the version of your MYSQL, but older versions of MYSQL you have to write “select * FROM information_schema.tables where version=’9′“.Another simpller way you can know the table information without taking care of table version is to query based on the database name “select * FROM information_schema.tables where table_schema =’DB_Name’“
ok we have the table information but still we will need column information of these tables. These information could be extracted from another system table called information_schema.`COLUMNS`
a very simple query could give the most important information of your table’s columns is
“SELECT column_name,data_type,column_key FROM information_schema.`COLUMNS` WHERE table_schema=’DB_Name‘ and table_name = ‘your_table_name’;“
hope this very simple tip will be helpful to the readers as an extra knowledge or for those who come to this page searching through google hope this information will aid you
Read MoreHow to change the Size of a Legend in Crystal Report and MS Access
Steps on how to change a legend font size on Crystal Reports and text on MS ACCESS pivot charts.
Well as easy as it may seem, this sometimes proves to be a tedious task to look for. Simply because the legends functionality are not provided on the legend display area. Well enough talk and lets learn how to do that in a few steps
Obviously you are creating a graph (chart of some kind),
* Right click on the Chart Area,
* Chart Expert
* Text Tab
* On the Format Area choose Legend,
* Click the Font button
* Change the font to the your desired display
For MS ACCESS
changing the legend size does not seem to be a problem but the legend caption is.
The Simplest way to change/remove the “Sum of…” text, or any text, in a LEGEND PivotChart.
* Switch from PivotChart View to PivotTable View
* Click once to highlight the field name that reads “Sum of…”
* Go to properties
* Select the “caption” tab
* On the title “caption” type in the new text you would like
* Change the view to pivot chart
sharepoint SPListItem
Shareponit SPListItem
Hello there, well to be honest I am not a fan of Microsoft technologies well any how I cannot live without my XP these days (one of the best things the big M have done), but SharePoint is completely different story. It has a lot of potentials and abilities and once I saw the first time I said to myself this will finish all of my working place’s problems. But I had a feeling something will be wrong in it. To be honest I have faced a lot of challenges many times I was surprised with what SharePoint provides and many many more times I was disappointed with its limitations. But thanks to friends and fellow bloggers who helped a lot out of the problems.
This time I was facing a very strange problem that I am even not sure if it is SharePoint problems or it is just our environment, I was not able to find any similar problem on the net so thought I could document it here.
SharePoint API provides a great API to access list items , search them, add, delete, and update them that save you the effort of reading list information from SharePoint’s database directly, and it is provided in a very nice way. But the updating I have had some strange issue with that I have solved now, but I couldn’t understand why I got it in the first place.
Here I will give you the basics of accessing items from SharePoint Lists and show the issue
To access a list is very simple make a variable and assign it the url where you see the list in the sharepoint
string strDashListRoot = “http://[Servername]/[subsite/subsite]/Lists/[list name]/AllItems.aspx“;// just copy past the link form your browser
Then from the link you can get the site and website and the list easily
using (SPSite site = new SPSite(strDashListRoot))
{
using (SPWeb web = site.OpenWeb())
{
SPList list = web.Lists["listname"]; // do not forget to close the brakets after your code is done } }
Now here you can access the list items easily
String information=Lists.items[2][“fieldname”].toString(); // simply put the index of the item you need and then the field name that you want to show
Now the main thing here is when we want to change field of the list item we could do easily this
Lists.items[2][“fieldname”]= “myvalues”;
Lists.items[2].Update();
but that didn’t worked in our environment while doing the folling code worked !!!
SPListItem item = Lists.items[2];
item[“fieldname”]= “myvalues”;
item.Update();
Well may be that is something related to our environment but hope through this simple blog you know how to access and edit SPList items.
Read MoreJavaScript, the language for them all!! (Jquery with gridview in asp.net 2.0)
Once a long time ago (in 80s) I think Assembly was considered the savior for when ever some one need to do some dirty work with their C or whatever language code and its not clear for him how to do it there or few things are not supported assembly comes to give you control on the tiniest transistor on the PC so you can do what ever in your mind (this is not just 80s it is applicable for all times )
In 90s the days of C++, Visual Basic 6.0, JAVA, PHP,… etc I think the savior was the C language, really when ever there was something you cannot do in any of these languages that you are working on, you would just connect it to a c DLL or any c library file!!, using JNI for Java or active or DLL library for visual basic and similarly for others.
These days (WEB 2.0 days) all of the applications are moving to web using PHP J2EE, .NET 2005/2008, R on R and others. The common factor between them is they all need JavaScript to do the dirty work for them (working on client machines) so as in old days when all programmers needed to know assembly or as in 90s when all programmers needed to know C to do wonders with their softwares, these days developers must know JavaScript; cause it is the language for them all (applicable to any JavaScript framework (jquery, mootool, prototype))!!!
Ok that’s enough chit chat, now why I am talking all about this?? Because jquery enabled me to do something fun with asp.net 2.0’s gridview
I will show you
I have added a jscript folder to my project and in it I have added the jquery and one of its plug-in: datepicker which will show a date picker calendar
Added jquery to my master page code so it will be available to all of my pages in the application that uses this master page
<script type=”text/javascript” src=”Jscript/jquery-1.2.1.min.js”></script>
Now I have a gridview (well for my application need I have a very complex 3 level nested gridview)
Now close date is a template column where I have a textbox I made the cssclass of the text box equals to “datepickerz” <asp:TextBox ID=”TextBox1″ runat=”server” Text=’<%# Bind(“close_date”) %>‘ CssClass=”datepickerz” Width=”89px”></asp:TextBox>
Now I need for each row in this complex gridview the textbox to be a datepicker so simply I wrote the following jquery script
<script type=”text/javascript” src=”Jscript/ui.datepicker.js”></script>
<script type=”text/javascript” >
$(document).ready(function() {
$(‘.datepickerz’).each( function () {
$(this).attachDatepicker();
});
});
</script>
Which is one of the beautiful magic of jquery it allows to pick all controls that have been assigned the class datepickerz and then for each control I just attach it with the datepicker.
The result whenever I click on the textbox in any editable row in the complex gridview I will get a floating calendar to select a date that will be stored in the textbox.
I know I have not discovered the atoms by doing this simple activity, but I wanted to mention it because as I see around me (in Oman) most of .NET developers are not aware of Jquery and other JavaScript frameworks and their features.
Read MoreAdding a friendly splash (waiting screen) while the code is being executed in ASP
Nowadays, most websites are connected to databases. the larger the data in the database, the longer it takes for the browser (or the code) to fetch/retrieve the required data for the user. and ofcourse, that’s where the developers’ skills comes handy, where developing an efficient code and using the minimum queries as possible is a must. i would like to add that having a little data in the database is not an excuse for not developing an efficient code for retrieving information from databases.
now, i assume that you “developers” did the most efficient way to retrieve the data, but above that there is still a considerable “fetching” time to wait inorder to fully display the records queried, here comes the benefit of adding a splash screen for the user, so he/she doesn’t panic and think that the page is stuck or something.
easy steps to follow inorder to get an attractive waiting screen (see below) while the code is processing and the user is waiting:
first of all you need to add one important line of code to the top of your ASP page, that line is:
<%Response.Buffer = True%>
This line tells the server not to send anything to the client until the page is finished processing or you use “Response.Flush” code in part of your ASP page to send everything fully loaded so far.
now just add the following to javascript functions to your code between “script” tags, those functions are used to “show” and “hide” the splash screen when needed:
function showObj(obj){obj.style.visibility = "visible";}
function hideObj(obj)
{obj.style.visibility = "hidden";}now after we created the functions we create the part that gonna show as “splash” screen, i created it as “div” as it can contain more then one element inside it not just an animated image or text..
<div id="splashScreen" style="position:absolute;z-index:5;align:center;"> Searching, please wait .. <br> <img src="loada.gif"/>
</div>
its strongly recommended to put this div as close as possible to the nearest element after you invoke a “search” button or a “submit” button so the div would show as soon as the “fetching” face starts
you can put any animated “gif” pic in the img src, i choose to put a loading indicator, you can find a very nice collection of activity indicators from http://www.ajaxload.info/
after you put the div in the right position, you should put the “Response.Flush” code so you tell the browser to show to the user everything loaded so far “including the splash screen” and then do the processing, code executing and searching. after that you do “Response.Flush” again to show the information fetched, and finally you hide the “splash screen” .. so to make it simple, your code would be like the following:
<%..Response.Flush 'this will show everything loaded so far including the splash screen.'... put the searching/fetching code here and show the results ...'... remember that your results wont show until you flush again ....Response.Flush 'this will show everything so far including splash screen AND results
%>and after we finished from everything we hide the “splash” screen with the following command (put the command in javascript script tag)
var splashsplash = document.getElementById("splashScreen");hideObj(splash);remember that you can customize the splash to your liking, after all its a div and can be pretty flexible.
Read MoreIncreasing performance and adding flexibility using arrays instead of recordsets in ASP with databases
Those days, inorder to increase the websites/intranets features and interactivity it is essential to have a connectivity to a database wither its an oracle database or MS access or other types of databases, .. and with databases comes the issue of performance, so in this article i would like to share some of the things that a developer can do to maintain a reasonable performance and even adding more flexibility to the ASP code dealing with databases that has huge data that needs to be fetched.
in this article am using vbscript as a server-side script.
for convenience i will show the steps on how to connect to MS access database first and after that i will start on the tweaks that developers can do in the coding to enhance performance and adding flexibility
<%
dim conn
set conn=Server.CreateObject(“ADODB.Connection”)
conn.Provider=”Microsoft.Jet.OLEDB.4.0″
conn.Open “c:YOUR_DATABASENAME.mdb”
%>
first of all, after you set up a connectivity to the database, you create a query, for example:
<%
dim rs ‘recored set
set rs=Server.CreateObject(“ADODB.recordset”)
rs.Open “Select * from TABLE_NAME”, conn
%>
at this point not much can be done, connecting to the database and querying to get data is pretty much fixed.
the thing start when manipulating the data we just fetched, most of the people do is manipulating the data like the following code
<%
do while not rs.EOF
‘ manipulate data here, go through the query exucuted, movenext, movelast move previous, etc
response.write(rs(“FIELD_NAME”))
rs.MoveNext
loop
rs.Close
conn.Close
set rs=nothing
set conn=nothing
%>
the above code would work perfectly if you have to deal with one query, relatively small data set in the query, and only small code is used to manipulate the recordset, and as long as you want to deal with the data (in the above code) the recordset should stay open .. what if we wanted to do a new query while we are dealing with the existing one while it is still not closed? .. we would have to create another record set and set it and open connection to it, and this would make a nightmare interms of performance if we have a larg data and multiple quieries to deal with. to avoid this we can copy the query information to a multi-dimensional array and close the recordset so we can use it again while we are working with the existing data. instead of doing the previous code we can do the following code:
<%
dim rsArray
rsArray=rs.getrows()
rs.close
for i=0 to UBound(rsArray,2)-1
‘ …. work with the data here using a multi-dim array, its easier and faster, we can even
‘use another query with the same recordset, ..
‘ we can work with the old data and with the new query data without creating new record set
next
conn.Close
set rs=nothing
set conn=nothing
%>
after we fetched the information we needed into “rsArray” we finished from the record set and we closed it “rs.close” so we can use it later while dealing with the existing data without having to create a new recordset. now while we have a multi dimensional array in our hand, we can take advantage of that to our benefit, even we can redim it if we want to add new columns or rows, but when redim-ing the data is lost, so we have to use “preserve” keyword to preserve the data after rediming, for example if we have number of rows and we want to add a new one we do the following code
<%
.
.
.
dim cols
dim rows
cols = UBound(rsArray,1) +1
rows = UBound(rsArray,2) +1
redim preserve rsArray(cols,rows+1) ‘ we have extra row
%>
notice the preserve keyword only works when redim-ing the last dimension, if we want to redim the first one (or the second one or third if we have more then 2) we have to go around it, copying the array to another array and put the dimension we want to change in the last place, and then we can use the “redim preserve” and work with the temp. array.
As a conclusion, using the actual record sets while manipulating data and creating new ones whenever we need is not efficient, on the other hand knowing the features of the arrays we can use it to our advantage so dealing with arrays is more flexible and more efficient then the actual record set.
