Database Upsize - Where is the SQL data?

[Tom Cone Jr]

Pardon me if this is stupid. I'm at the foot of the SQL learning curve.

Using SQL Server 2014 Express.

I open AlphaSports and choose the option to upsize my database. The connection is made. Upsizing occurs. I can open the new "Alpha" database and see the new Active Link tables. All is good until I search for the "actual" SQL data. Can't find it. Was expecting to see a new primary database file ( MDF filename extension).

What am I missing?

Thanks.

[Finian Lennon]

Re: Database Upsize - Where is the SQL data?

Hi Tom:

Don't the active link tables show up in your Alpha database?

Go the Control Panel/Tools/External Databases and start playing around. Your connection string is the key to access. Apart from browsing the Active/Passive link tables, the only way you can see the data is by using SQL commands. Alpha provides some pretty good tools that can get you started on that. You can create queries and updates and Alpha will create/show the SQL needed. Most people working with back ends will use a separate tool (for example, I've used Navicat for MySQL) to manage the database structuring and maintenance and then modify code from there to include with Alpha. Each product has its own management console, so you don't necessarily have to buy.

To create the sort of custom stuff you are used to doing on the desktop you'll need to go to xdialog as your interface.

[Tom Cone Jr]

Re: Database Upsize - Where is the SQL data?

Hi, Finian.

thanks. will do.

the active link tables are present and seem to be working. I just haven't been able to figure out where on the hard disk the data is actually being stored. Had expected it to be present in a new SQL database somewhere on my machine. but where?

If I were going to move the actual data to another machine or folder I have to be able to find it first, right? this is where I'm stuck. Will see if the alpha tools open a door for me.

Thanks again.

-- tom

[Steve Wood]

Re: Database Upsize - Where is the SQL data?

Since you are using SQL Server, then your data is accessible using tools like SQL Server Studio or Navicat. There is no "MDF" file. Get a trail download of Navicat for SQL Server, install it and create a connection to your sql database to see the tables. Or you can go to Alpha > Tools > Database Explorer >Click the Plus sign, create a connection (etc) and then be able to view your tables using that method.

I know you are just starting with this but do not mistake upsizeGenie/Active Tables with actually using SQL in your application. It creates a hybrid situation where sql tables are used, but all of the database syntax in Alpha is still DBF-style.

[Tom Cone Jr]

Re: Database Upsize - Where is the SQL data?

Finian,

Thanks again. I will follow your advice.

Is there some way to create the "MDF" file using Alpha, or one of the other tools you mention?

Thanks again, -- tom

ps. - If you're a Falcons fan, you have my sympathy. We're struggling down here in Tampa, too.

[Finian Lennon]

Re: Database Upsize - Where is the SQL data?

I'm not familiar with SQL Server Express, ran it once a long time ago and then decided to concentrate on MySQL/MariaDB. Is the MDF file the SQL Server data file?

If I have mdf file correct (!) drill down into the folders where SQL Server is installed and you may find a data folder and/or a configuration (MySQL uses a MySql.ini file) file that holds all the information about the installation. One of the key points about back-end databases is that access to those files is and should be very difficult/impossible for any user other than a super-administrator. There is nothing you can see or do directly with those files. They are nothing like dbf's you can click on or open with other tools.

Working with back ends requires multiple tools and, initially, it's pretty daunting because it's easy to mess things up. Just remember, you can always start over and run the upsize genie again. And remember your passwords and any other keys you use to set up the database.

Once you create the database in SQL Server with the upsize genie, and knowing its name and access password, you should be able to create a connection to it using Alpha or Navicat where you can see the tables and structures as they are defined by SQL Server Express. Welcome to the world of immeasurably more field/data types.

[Tom Cone Jr]

Re: Database Upsize - Where is the SQL data?

Thanks again.

I'm going to start over with MySQL. Not because of the stumbles I've had but because I'll be climbing the learning curve with a partner, who shares my interest in running Alpha as a front end directly connecting to the SQL backend, via xdialogs.

-- tom

[DaveM]

Re: Database Upsize - Where is the SQL data?

I think phpadmin is free
mysql comes with a dataviewer and ability to edit/create tables - "workbench"
I use sqlyog, but it's not free

[Al Buchholz]

Re: Database Upsize - Where is the SQL data?

Thanks again.

I'm going to start over with MySQL. Not because of the stumbles I've had but because I'll be climbing the learning curve with a partner, who shares my interest in running Alpha as a front end directly connecting to the SQL backend, via xdialogs.

-- tom

You may want to look at MariaDB. It is the product that the original MySQL developers made after selling MySQL.

https://mariadb.org/

[GGandhi]

Re: Database Upsize - Where is the SQL data?

Thanks again.

I'm going to start over with MySQL. Not because of the stumbles I've had but because I'll be climbing the learning curve with a partner, who shares my interest in running Alpha as a front end directly connecting to the SQL backend, via xdialogs.

-- tom

why should it matter what backend database server is? sql server express and mysql or mariadb, all should work with xdialog as far as i know. you should know in this board they defend each server feverishly.
as far as location, did it save in the folder upsize?

[GGandhi]

Re: Database Upsize - Where is the SQL data?

Was expecting to see a new primary database file ( MDF filename extenson).

tom
if you have not figured out how to upsize your alphasports i can show you step by step in either mssql or mysql.
then you can try. if you are comfortable then you can actually upsize your database to either one of them.
incidentally your data will not be lost, if you are worried about that.
you will see the sql database.

[DoronF]

Re: Database Upsize - Where is the SQL data?

Hi Tom,

One thing you can do by simply open the SQL studio which is installed in your computer and see the data. Look for SQL Sever Management Studio. If you use Windows 8.1 or 10 just type it in. Windows 7 check under programs.

Regards,

Doron
The Farber Consulting Group, Inc.

Main Web Site: http://www.dFarber.com

Avis Car Rental Application
Convert Ms Access to Web
Alpha Five Development

[pboomwork]

Re: Database Upsize - Where is the SQL data?

Tom,

In SQL Server each database is stored in a series of files with the LDF, NDF and MDF extensions. For each individual database the files will share a common root name. There may be more then one file with the NDF extension per database. As with Alpha5 each SQL database can contain a large number of tables (even thousands). A connection string in Alphas is to single SQL database.

A SQL Server installation can manage numerous SQL databases, what the upper limit is I'm not sure suffice to say it's lots. But once again a single connection string in Alpha5 connects to a single SQL database or collection of LDF, NDF, and MDF files.

The actual Physical location of these files is only know by the SQL server. To find out the physical location of the files you need to use SQL Server Management Studio as mentioned by DoronF in the last post. It's part of the SQL server installation and I would suspect part of the SQL Server Express package.

The Alpha5 connection string is only to the SQL Server and a particular database. It is up to SQL server to know where the physical files are located. Therefore the physical files must be know SQL server. In SQL speak the files must be mounted to the SQL server. If the database in question was created by SQL server as would be in the case of the upsizing genie it is already mounted to the SQL server as part of the creation process.

A SQL Server database can be unmounted from one SQL server and then mounted to an other SQL server. But normally a database is backed up from one SQL installation and restores to another. The backup and restore can be done while never knowing the actual physical location of the files. There are layers upon layers is this stuff. The backup and restore operations are of coarse you guessed done by the SQL Server Management Studio.

Mounting and remounting databases to SQL servers is only an advantage if you are dealing with large data sets and down times. IF done properly with the use of a shadow copy utility it can save significant amount of time (hours) over the backup and restore process. That's the only reason I know about it and actual physical files involved.

It's a steep learning curve, there is more to SQL Server then there every will be in Alpha5. Many Database Administrators make their livelihood in SQL Server. I know several great guys and it is amazing what they can do with an SQL query. SQL queries literally can be programs. Alpha5 will shield you from a lot of the complexities and make learning curve less like a cliff, you feel like you are falling off or trying to scale, but at times you have to face the Microsoft tools and all their complexity.

The Navicat product as mentioned by Steve Wood is an other way of dealing with hiding the complexity of SQL Server. I've looked at it's promotional material and if I needed to create SQL databases I would certainly use it. To date I've only been working with existing databases used by vertical market medical application.

The following link is great introduction to using SQL within Alpha5. I would have saved myself a lot of time and headache if I had of read it first.

http://www.downloads.alphasoftware.c...SQLTables.html

[Tom Cone Jr]

Re: Database Upsize - Where is the SQL data?

Thanks to all responders. Your interest and support is very much appreciated. -- tom

[David Boomer]

Re: Database Upsize - Where is the SQL data?

for what it's worth...
(not sure that this will work with your specific install of MySQL)
To find MySQL database files...
C:\Program Files\MySQL\MySQL Server 5.5\my.ini ---> look for "#Path to the database root"
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"

MySQL stores and accesses files in various places on your hard drive. This page documents the locations of commonly used files, for some often used MySQL installations.
If you want to backup your data or copy data from one server to another, please don't copy the data files directly. It is recommended to use Sequel Pro's Export and Import features (use a MySQL dump) to accomplish this.
If you can't find the files in the locations given here, it could be because you changed the configuration -- these are only the default locations. Some of the files only exist while the MySQL server is running (eg. the socket file).
If your installation has no option file (my.cnf), you can create it in the base directory of your installation (see 'Using Option Files' section of the MySQL manual for more information).
If you can connect to your MySQL server, you can find many of these paths by selecting Show Server Variables... from the Database Menu in Sequel Pro.
MySQL installed with the official .pkg installer
Socket File
/tmp/mysql.sock
Data Files
/usr/local/mysql/data/
Error Log
/usr/local/mysql/data/HOSTNAME.err (insert your hostname)
Base Directory
/usr/local/mysql/ (this is a symbolic link)