Is It Okay To Store Images In A SQL Server Database?

There are advantages and disadvantages to storing pictures/images into an SS Database versus storing the physical file in a folder.

In my personal experience, I can point out  a few points that saving them into the database can be better:

• It protects the images from prying eyes or from malicious viruses that encrypt files.
• If the machine where the SQL Server is hosted from has good specs, then there's a good chance that downloading and uploading pictures to the database is going to be fast. 
• You are free to add more search parameters compared to doing searches in folders locally or over the network. You can add more tags to an image instead of relying only to its filename. For example, I can search for a picture using a unique code identifier (barcode) or do a wildcard search using its description.

Some disadvantages could be:

• If you're storing large images in a table particularly high-resolution pictures with over 2MB in size, you may find that running a query on that table may slow down in time as you insert more pictures. The solution to this is to downsize the image before inserting them to a varbinary(max) field. Instead of inserting a 4MB picture, reducing the image's size down to let's say, 80KB before uploading it can help a lot. (I created my own Image/Picture Downsizing program using VFP and GDI+ to convert high-res picture to a descent low-res picture to help the company that I currently work with.)

Transfer and Downgrade is a module for Tangosoft I created to reduce the size of the images stored in our database.

• Takes up space in the server. This is inevitable if you decide to store images into a SQL Server Database. But again, reducing the size of the image can help with saving space. I have stored tens of thousands of pictures in a SS database and the benefits that I gain have overshadowed those of the disadvantages.

Host Is Not Allowed To Connect To This MySQL Server Error In Visual Foxpro

When you encounter this type of error in VFP, it may mean that the MySQL account you are using does not have the right privileges.

For a quick fix, go to the server PC and open MySQL Command Line Client. Type the command below:

grant all privileges on *.* to root@'%' identified by 'YourPassword';

Change the 'root' if you're not using that user and change YourPassword with your own password.

Show MySQL Port

What is the open port that your MySQL is using? By default it should be 3306, but in any case that you might have changed it during the installation then perhaps you could include the port number in your connection string.

You can check it via the MySQL command line client by typing:

SHOW variables LIKE 'port';

Understanding Types of Joins In SQL

Understanding Types of Joins in SQL by All Saints

Connecting VFP To MySQL Database

Here's how to access a MySQL database from VFP.

First, you need to download an ODBC connector to MySQL from this link. Choose between 32 or 64 bit version.
http://dev.mysql.com/downloads/mirror.php?id=412782

Next is to connect to it via DSN or DSNless connection.
Here's an example of a connection string via a DSNLESS (ODBC) connection. Please note that the lines starting with an asterisk (*) are comments.

lcStringConnect = 'Driver=MySQL ODBC 5.2 Unicode Driver;Server=localhost;uid=root;pwd=password'

*---This part here sets connection errors hidden.

SQLSetprop(0, "DispLogin", 3)

SQLSetprop(0, "DispWarnings", .F.)

*---This is where the actual connection happens.

lnConnect = Sqlstringconnect(m.lcStringConnect)

*---The variable lnConnect holds the number returned by the function
*---SQLSTRINGCONNECT(), if the value is greater than zero (0) then
*---no error happened during the connection attempt. 

If m.lnConnect > 0

  *--- List the databases in MYSQL.

   SQLEXEC(m.lnconnect,'show databases','testcur')

   BROWSE

Endif

=SQLDisconnect(m.lnConnect) && Close Connection

If the MySQL Database is remote (or online), you will need to specify the IP address as a server name in the connection string.

Date Imported From Excel Changed Into Number

When importing data from an Excel file into VFP cursor, a date field may change into number field like the example below:

Date in Excel	Current Number In Cursor
19-Dec-2006	39070
30-dec-2008	39812
19-May-1989	32647

To fix the issue, we could either replace all the serial numbers in the new field with this command:

Scan

   Replace SomeField with SomeField + {^1899/12/30}

EndScan

Or use a select statement that should convert that serial number back to date readable by human eyes.

Select SomeField + {^1899/12/30} As NewField from SomeTable Into Cursor TestCursor

Extracting Date In 10-Digit Integer Format from MySQL To VFP

To extract date from a MySQL field in 10-digit integer format, we can use  FROM_UNIXTIME function.

Assuming you are connected via ODBC (SPT):

=SQLExec(m.nConnect,'select from_unixtime(somefield) as xdatetime from sometable','SomeViews') && should produce datetime

=SQLExec(m.nConnect,'select Date(from_unixtime(somefield)) as xdate from sometable','SomeViews') &&should produce date format

Create A New Table From An Existing Table In MS SQL Server

If I want to transfer (copy) table and its record from one database to another, I would do this for example:

Select * Into [Database2].[Dbo].[Table1] From [Database1].[Dbo].[Table1]

Basically, that's how it can be done. You can alter the structure of the newly created table
by adding records from JOIN or via creation of new fields using CAST.

Reindexing in VFP and MS SQL Server

In Visual Foxpro, there is no need to reindex an existing index once you create an index right after you open a table.

USE Temptable

INDEX on somefield TO someindex

In SQL Server, reindexing means building the index again. Rebuilding the index for a certain table in SQL Server is not cumbersome and how often you should do that would depend on your choice, if you wanted it done regularly or not at all.

You can either right click on an index (a node in indexes under a table) in SQL Server Management Studio and select REBUILD or run a query like my example below...

ALTER INDEX [pkid_index] ON [dbo].[Customers] REBUILD PARTITION = ALL WITH

   ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON,

   ALLOW_PAGE_LOCKS  = ON, IGNORE_DUP_KEY  = OFF, ONLINE = ON, SORT_IN_TEMPDB = OFF )

...or if you worry about your availability to execute a manual reindexing, you can assign the SQL Server Agent to do the thing automatically for you at a certain date and time. Mine for example is done every week at 00:00H when nobody is using my programs. You can create it in the Maintenance Plan.

Generating Unique ID's From An Auto-Increment Field

Here's an example of how we can create a reference to a 10-digit running number that we can use as a unique ID to anything we want.

We'll just need a table that contains nothing and will serve as a reference only to the last auto-generated number from its auto-increment field.

Here's the structure of the table in MSSQL to hold the 10-digit ID.

CREATE TABLE [dbo].[sometable](
 [lastuid] [int] IDENTITY(1000000000,1) NOT NULL,
 [dtstamp] [datetime] NULL
) ON [PRIMARY]

And here's the stored procedure to get the last ID from that table.

Create Procedure dbo.GetUID 
As 
Begin
 insert into sometable (dtstamp) values (getdate())
 select @@identity as lastUID
 delete from sometable
End

The procedure can be altered so that it retains a single row that contains the timestamp just in case we want to know when the last ID was generated. Just move the DELETE command to the line before the INSERT command. Either procedure will return the last generated ID.

Create Procedure dbo.GetUID 
As 
Begin
 delete from sometable
 insert into sometable (dtstamp) values (getdate())

 select @@identity as lastUID
End

And finally, we can extract a new unique ID in VFP by executing a call in an SPT connection.

=SQLExec(m.nConnection,'execute dbo.getuid','curTableID')

And from SSMS itself:

execute dbo.getuid will bring out: