BLOGS@DiGiTSS

DiGiTSS Team's Programming experience with PHP, MySQL, Ajax, Javascript, jQuery, C# and Microsoft technologies

Posts Tagged Database

Reading MSSQL BLOG column data with PHP

Apr 1

Posted by Dharmavirsinh Jhala in PHP | No Comments

I was working on a migration script to transfer data from legacy desktop software to Portal we have developed. While migrating attachments (stored as TIFF files) in BLOB (Binary Large Object) in Microsoft SQL Database Server using PHP it was not a cake-walk. When I observed every time it was creating a file of same size (4KB). I quickly understood that there is some limitation (of size) for reading records in PHP configuration.

I searched through PHP.INI file and found following settings:

; Valid range 0 - 2147483647.  Default = 4096.
;mssql.textlimit = 4096
 
; Valid range 0 - 2147483647.  Default = 4096.
;mssql.textsize = 4096

I removed ";" and made values as zero, thinking that it will let PHP read unlimited size of data but didn't work out. So changed it to max value specified as following and worked perfect: Read the rest of this entry »

Tags: , , , , ,

Alternate to FIND_IN_SET for non-MySQL databases

Mar 6

Posted by Dharmavirsinh Jhala in Database | No Comments

Missing MySQL

Missing MySQL

If you are MySQL user initially at the beginning of your career and later on as the career progresses got in touch with other RDBMS then you might miss MySQL's FIND_IN_SET function badly. :)

What MySQL's FIND_IN_SET does is it returns position of the matched element in the SET. That SET of items must be comma-separated.

For example take following code into consideration:

SELECT FIND_IN_SET('b','a,b,c,d');

It will return "2" as the result. So generally what we have been doing is we use it not to find whether particular element is at what position in the comma-separated list but just to find simply that whether item exists in the list at all or not?

For that we use SQL something like following:

SELECT COUNT(0) FROM products WHERE FIND_IN_SET('7', category_id);

Above query will give you count of records which are having category_id 7 along with or without other categories. In table records might have values comma-separated like '4,5,7' or '7' or '2,7,8' or anything. So above expression in WHERE will return value greater than "zero" (0) and that row will be returned in result or calculated in count. Here category_id in products table is of type varchar.

Now when we are not using MySQL many times we can get similar result if we are not worried about the position of the item in the set but just want to make sure that it exists or not. It can be achieved with following SQL statement. Read the rest of this entry »

Tags: ,

MySQL 5.5 available for production

Jan 16

Posted by Dharmavirsinh Jhala in News | No Comments

Oracle MySQL 5.5.8 GA

MySQL 5.5.8 GA

Back in December when I updated my twitter status to announce MySQL 5.5 general availability, I was very happy at that moment. It was the first major and much awaited release of MySQL after Oracle - the database giant acquired Sun (and so MySQL). Though MySQL 6.0 is not an active project right now and there are many strategic changes after Oracle's acquisition of Sun for MySQL's development road-map, but as a AMP developer it was news to be happy about.

Good to see MySQL founder Michael "Monty" Widenius, has forked MySQL as MariaDB, which is binary compatible (drop-in replacement) with MySQL. Good to see open-source efforts to keep the momentum alive.

Moving back to MySQL, with version 5.5 there are lot of new enhancements including performance improvements for both MyISAM and InnoDB storage engine. InnoDB gets updated to latest version InnoDB 1.1. There are many improvements with replication and couple of new feature addition with replication as well. I have been using MySQL-Replication since long time (3 years now) and I am excited about the replication heartbeat and all new improvements which are made in that direction.

Some of the highlights from MySQL.com about 5.5 are as following:

MySQL Database 5.5 delivers enterprise features, including:

  • Improved! Up to 1500% faster performance on Windows
  • Improved! Up to 370% faster performance on Linux
  • Improved! Better scalabilty on modern, multi-core, multi-CPU hardware
  • New! Performance Schema for monitoring MySQL server run-time performance
  • New! Semi-synchronous replication to ensure data consistency and redundancy
  • New! Replication Heartbeat to immediately uncover replication interruptions
  • New! Partitioning options for faster lookups
  • New! Easier development and debugging of stored procedures, functions, and triggers
  • Reliability requiring little or no intervention to achieve continuous uptime
  • Ease of use with "15 minutes to success" installation and configuration
  • Low administration with very little database maintenance required
  • Replication providing flexible topologies for scale-out and high availability Read the rest of this entry »

Tags: , , ,

Possible Sybase SQL Anywhere bug

Apr 12

Posted by Dharmavirsinh Jhala in Database | 4 Comments

This post represents thoughts based upon my experience and I have not reported this issue to Sybase as of yet, I firmly believes that such result should not return in given case and so I am representing my thoughts with example over my blog here.

Topic: "Sybase SQL Anywhere Database is buggy when we sort results by column which is having granularity close to Zero".

As I am developing PHP Web Applications, some time back I have faced an issue while I was using a control panel of the web application. It was using Sybase SQL Anywhere as the database engine. The problem was wired, I was not able to search a particular menu item which I have to make active/inactive through the list of about 20 or so odd rows. I was switching through pages and the results were sorted by "status" column, which was having value of either "active" or "inactive". I searched by keyword and I got the result immediately in my data-grid..! It was surprising and unexpected. At first what came into my mind was that oh..! there must be something wrong with my application framework or DAL (Data Access Layer).

Then I took the same query and I fired in the SQL Anywhere - Interactive SQL. The results were unexpected, I felt like this is a "bug" with Sybase itself. To avoid any doubt I created new table with some records and repeated the test and results were same. This incident has taken place at my workplace.

After some time I downloaded Sybase SQL Anywhere Web Edition 11.0 at my own desktop. I thought I was using bit older version at my office and here with the latest version the bug will be fixed, as this edition is using same engine which is used for their Enterprise products and there are some connection/licenses stuff which makes it Web Edition. I repeated the sequence and bingo the bug is still there.

Here is the details of the test as of last tested on Sybase SQL Anywhere Web Edition 11.0: Read the rest of this entry »

Tags: , , ,

Paying the bills.!