Database Discussion Boards

Member 13536030 wrote:
Am I totally wrong?

I don't have a blog, didn't write any book.

Member 13536030 wrote:
You mentioned MS SQL is easy to do; but how about other databases like Oracle, Db2, MySqL, etc?

For MSSQL, there is a profiler-application that is part of the enterprise-version; it'll show you any SQL command that is executed against the server. In Oracle they don't call it a profiler, but a trace; each large db-vendor has their specific tools. These tools have the advantage that they show everything that is communicating with your database.

If you're going to use it for debugging, then you're only interested in the SQL that your application generates. In that case there are other tools that help, like MiniProfiler: A simple but effective mini-profiler for .NET and Ruby[^]. This requires a change in code, but does not depend on a specific type of database.

Member 13536030 wrote:
Do you mind sharing your personal/work email?

Yes. Any question can be asked and answered on the forum, for all to benefit. For you it has the added benefit that if I am wrong, someone will correct me Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

I am creating an application that will read log/profile files and parse it to find, anyalyse SQL queries (to start with only "select" statements) that are executed for a few particular fields and those tables/views that use these files. So for this application to work, the application should know the format of the file it reads. Let me see if I can be more specific:

I have a field called 'field1' used by a table called 'table1'. I want to turn on / configure something in my MySQL database (it could the profiling) tool you mentioned with the goal that every night I can get a file that shows any "Select" statements against this field and table. Is that possible?

Thanks.

The mini-profiler mentioned would do that. Tracing in MySQL is discussed here[^].

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Based on other responses.

So database is MySQL and you want to look at what statements were used. From that you want "General query log"

MySQL :: MySQL 5.7 Reference Manual :: 5.4 MySQL Server Logs[^]

However be very careful if you are using this on a production machine. You want to insure that the log files do not use up the entire file system.

Member 13536030 wrote:
if someone does a select * from any of the tables

Just noting that is a bad idea. Shouldn't be allowed. Hinders maintenance work and things like exactly what you are doing.

However you can't tell whether the client is using a specific column or not even if it was specified. But if "*" is used then you will know for a fact it is being returned.

I've always done Linq to SQL which I like. It's easy to setup and work with.

I follow a typical n-Tier architecture: UI <~~> BL <~~> DAL <--> DB

I've only set up Entity Framework once. When it generated the Data Context all the entities were in the same DAL project. This means that my UI, BL, and other projects in the solution all needed a reference to the DAL. This seems like a tight coupling. I was able, with a lot of work, to generate the entities into a separate project. Then, the other projects in the solution only needed to know about the new entities project, but again it was a lot of work to set up.

So, how is EF typically used? What's the right way to set up the DC and entities?

Thanks

If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.

Use the "Code First" approach, even if you're connecting to an existing database. That way, you can put your entities wherever you like, so long as the DAL project has a reference to them.

If you're using an existing database, there's even a wizard to create the code first classes for you, which you can then move to another project as required:
Entity Framework Code First to an Existing Database[^]

The BL layer might be able to reuse the EF entity classes. But you'll probably find that you need to make changes to the layout, particularly when dealing with navigation properties. In that case, you'll want a parallel set of DTO classes, and something like AutoMapper[^] to map data between them.

The UI layer will probably need its own parallel set of view-model classes, with UI-specific validation, property change handlers for WPF, etc.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Not if you lean on interfaces and follow the repository pattern to wrap your EF DataContexts. I generally throw a "core" or "common" library into my solutions to serve this purpose, and MEF my repository into the mix. This is also nice because it does not marry you to the EF implementation.

Yes, you will be coupled to an abstraction, but that's more or less the point in my eyes.

"There are three kinds of lies: lies, damned lies and statistics."
- Benjamin Disraeli

I want to change the old store to new store by using the query in MSSQL. For example,

Staff_Id Name Transaction_Date New_Store Old_Store 
--------------------------------------------------
1        AA   1.1.2017         1

1        AA   1.2.2017         2

1        AA   1.3.2017         3

1        AA   1.4.2017         1

1        AA   1.5.2017         4

I want to change the table to following like that 
Staff_Id Name Transaction_Date New_Store Old_Store
--------------------------------------------------
1        AA   1.1.2017         1         NULL

1        AA   1.2.2017         2         1

1        AA   1.3.2017         3         2

1        AA   1.4.2017         1         3

I wrote the query like that, but it's not true, it's output randomly

SQL

Update EC1 SET EC1.Old_Store=EC.New_Store
 From Employee_Change EC , Employee_Change EC1 
WHERE EC.Staff_Id=EC1.Staff_Id 
AND EC.Transaction_Date>=EC1.Transaction_Date

Something like this should work:

SQL

UPDATE
    EC
SET
    Old_Store = 
    (
        SELECT TOP 1 New_Store
        FROM Employee_Change As EC1
        WHERE EC1.Staff_Id = EC.Staff_Id
        And EC1.Transaction_Date < EC.Transaction_Date
        ORDER BY EC1.Transaction_Date DESC
    )
FROM
    Employee_Change As EC
;

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

This has been answered at Change old store to new store[^]
In future, do not cross-post across forums. Pick one and be patient - we do this in our spare time for free - sometimes it can take a little while Smile | :)

Hi,

I am running an SSIS Package which takes FileName as Variable and runs the Package, strangely when I run the Package from Sql Agent Job Package I am getting this error: Did not read byte array properly

But when I run the Package, if the file is open, its reading the file and running the Package properly, if I close the File it gives me error, the Package is using the Jet 4.0 drivers and File has multiple sheets in it, I am giving the name of the second sheet to read,

is it because of the multiple sheets or
is it because of the Jet drivers,
The file path is too long (I saw its 150 characters as it shows, the variable datatype that holds the File name is String)
or if the File full Path combined with connection string value, does it become too long to be hold by the Connection string of the Source Connection?

I am not sure what is the reason for its throwing this error.

Any suggestions welcome please need some help. Thanks in advance.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

Abdul asked:
1.is it because of the multiple sheets

Try replacing "the file" (I'm guessing this is a workbook) with one with only a single sheet and see if the problem still happens

Abdul asked:
3.The file path is too long (I saw its 150 characters as it shows, the variable datatype that holds the File name is String)
4.or if the File full Path combined with connection string value, does it become too long to be hold by the Connection string of the Source Connection?

Try shortening the path - e.g. put it into a temp folder straight off the root, or map a drive to the location.

Given that it "works" when you have the file open though, it is unlikely to be any of those. The suggestions are just to indicate that you could have easily discounted them for yourself by experimentation.

You are using very old Jet Drivers and you have not mentioned which version of Office or SQL. Try installing at least ACE 12.0 and see if that fixes it.

Beyond that, without knowing what the package is trying to do we're fumbling about in the dark

Yeah we are using only Jet Drivers, but its limitation on Server for now. The office file is .xls file which is 97 version I think.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

indian143 wrote:
if the file is open, its reading the file and running the Package properly

You might want to examine that assumption.

The fact that it doesn't produce an error that you see, is not sufficient to prove that it worked. It must process the file and produce output that it could have only gotten from the file.

I'm getting ready to start a new C#/WPF project for a client. The database will be SQL Server. Myself and another developer will both work remotly.

So there's two development PC's, each with SQL on them. Then my server will be used to test, so there's a copy of the DB there. Then there's the production DB on the client's server.

I expect that I'll be creating, editing, and deleting DB objects continuously. I will probably use scripts for these changes so I can then run the scripts on either the test or production servers. Also, other developers will need to run them.

The question is about how to manage the changes/scripts. What process do you follow for this? Is there a naming convention? How do you keep everything in sync?

Thanks

If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.

In a previous position, we used the following DB script pattern:

1) For each major release, have a set of "Create" scripts.
a) Create Table_1.0.0, Create_Index_1.0.0, Create_Sysdata_1.0.0 (or something like that)
2) Each time there is an upgrade, create a set of scripts
a) Upgrade_Table_1.0.0_1.0.01, Upgrade_index_1.0.0_1.0.1 (and so on)

Make sure you are using a source code control system where you would be checking in not only your source code, but your SQL scripts.

When you have a "build" you will also pickup the correct version of database scripts.

Also, you should have a Build / Deploy box where you can Create or Upgrade databases anytime for QA testing. It is always a good test to be able to take a backup of your client's database and run the upgrade scripts to prove them out.

The only version control system I've used is Subversion. If you are not familiar with them, check them out and setup a proper team development environment, you will save yourself lots of headaches.

Just my 2 cents.
David

General concept
- Database itself has a version
- Wrap the changes into an application that applies those to the database
- Use a table in the database that tracks the current version.

An existing tool for this is liquibase. Seemed decent when I used it.

You can roll your own as well.

And additional feature that I consider essential but it adds complexity is that the application also tracks the version of the database it expects. If the database is the wrong version (table above) then the application will exit on start up.

We use Red-Gate SQL tools against the UAT server. We also make a strong use of schemas, common objects use dbo, specific object use a different schema, a developer generally works on one schema at a time and dbo changes are discussed before implementing.

Scripts are run against the UAT server using SQL Compare. Master table data is synched via SQL Data Compare.

PS I do not work for Red-Gate but have been using their paid for tool set well over a decade.

Never underestimate the power of human stupidity
RAH

This is how I've done it for years....right after login, the application compares db version to app version, applies changes (logic in the app itself) if needed. If the version changed, the db version is updated to reflect that. In addition, I also use a special database table that records schema changes as they are made.

If it happens that the database version is higher than the app, the app prompts for an update. If the user declines, the app complains and exits.

btw, comparing version numbers is much easier if you convert them to long ints using something like this:

intAppVersion = (major * 100000) + (minor * 1000) + revision

This also makes it easier to run a loop through db version checks until you hit the current app version.

"Go forth into the source" - Neal Morse

That sounds very similar to Entity Framework Code First Migrations[^]. Smile | :)

However, I don't think that's a good approach. Apart from your application needing to check the database version every time it connects, it also has to connect as a user which has permission to modify the structure of the database. That always seems like a violation of the principle of least privilege[^] to me. 99% of the time, your application doesn't need to modify the database structure, so it shouldn't have permission to do so.

You might be able to work around that by using a second login with the elevated permissions. But in most cases, I think it's easier to move the database upgrade code outside of the application, and manage it as part of the upgrade process.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Richard Deeming wrote:
That sounds very similar to Entity Framework Code First Migrations

Maybe they got it from me? Laugh | :laugh:

I've been using this approach since about Y2K. Smile | :)

You do have some good points regarding principle of least privilege, and I wholeheartedly agree with that philosophy/architecture for some applications...most of the ones I deal with demand simplicity and the ability for an end user to install a module without help from IT, hence dbo. Smile | :)

"Go forth into the source" - Neal Morse

I have the following tables and values:

SQL

t_cars
--------------------------------------------------------------------
nCars_ID	sName		sModel		        sIdentifier
--------------------------------------------------------------------
1		BMW		3 series	        D-78-JHG
2		Volvo		C30			B-56-KHT
3		Fiat		Doblo		        H-72-ABN
4		Volvo		C40			J-78-YTR


t_feature
-------------------------------
nFeature_ID		sName
-------------------------------
1			CMC
2			Doors
3			Color
4			Type
5			Weight
6			Engine
7			Power


t_cars_feature
-----------------------------------------------------------------------------------------------
nCarsFeature_ID	                nCars_ID	        nFeature_ID	               sValue
-----------------------------------------------------------------------------------------------
1				2			1				2500
2				2			2				5
3				2			4				Diesel
4				2			3				Green
5				3			1				1900
6				3			2				3
7				3			4				Otto
8				3			5				2300 KG
9				1			1				1900
10				1			3				Blue
11				1			4				Diesel
12				1			5				2100 KG

I need to retrieve from DB the cars that has CMC feature, has Color feature, AND CMC = 1900 AND Color = 'Blue' ONLY

I have tried:
SELECT t_cars.sName, t_cars.sModel, t_cars.sIdentifier
FROM t_cars, t_feature, t_cars_feature
WHERE t_feature.nFeature_ID = t_cars_feature.nFeature_ID
AND t_cars.nCars_ID = t_cars_feature.nCars_ID
AND [/*condition that get me cars that has CMC feature, has Color feature, AND CMC = 1900 AND Color = 'Blue' ONLY*/]

I have tried the condition like that:
Trial 1:

AND t_feature.sName = 'CMC'
AND t_feature.sName = 'Color'
AND t_cars_feature.sValue = '1900'
AND t_cars_feature.sValue = 'Blue'

and get me nothing

I have also tried:
Trial 2:

AND t_feature.sName IN ('CMC','Color')
AND t_cars_feature.sValue IN ('1900','Blue')

and get me all records that has CMC 1900 OR color 'Blue' (probably I got here cartesian product)

In real situation I could have several t_feature.sName values, and several t_cars_feature.sValue values, that is why trial 1 are not suitable for me ...

Can you help me ? Thank you.

You need to select on the cars_features table more than once, so:

SQL

SELECT C.sName, C.sModel, C.sIdentifier 
FROM t_cars AS C INNER JOIN t_cars_feature AS CF1 ON CF1.nCars_ID = C.nCars_ID 
  INNER JOIN t_cars_feature AS CF2 ON CF2.nCars_ID = C.nCars_ID  
WHERE (CF1.nFeature_ID = 1 AND CF1.sValue = 1900) 
  AND (CF2.nFeature_ID = 3 AND CF2.sValue = 'Blue')

sName | sModel | sIdentifier
----------------------------
BMW | 3 series | D-78-JHG

First crate a query that selects the link records (t_cars_feature) you expect to see.

SQL

Select * from t_cars_feature where (FeatureID = 1 and value = 'CMC') and (FeatureID = 2 and value in (3,5)) ...

Then change the query to only get the CarID

SQL

Select CarID from t_cars_feature where (FeatureID = 1 and value = 'CMC') and (FeatureID = 2 and value in (3,5)) ...

Once you are happy with the results wrap the first query to select the cars

SQL

Select * from Cars where  CarID in (insert query 1)

What you are missing is the heavy use of brackets. It is still an ugly database design.

Never underestimate the power of human stupidity
RAH

Hi,

It shows both the Jet and Ace drivers on a Server machine, and user the Sql Agent Job is running with, doesn't have admin privileges. The Packages that are running with Jet drivers are working fine on that Server where as the Packages that are using Ace drivers are not running Properly. There are various questions and eye brows that are raising here

There are Ace 14.0 drivers there on that machine, were the Ace 12.0 drivers overwritten by Ace 14.0 when we installed newer version of Ace or office pack
Don't the Ace 14.0 drivers which are advanced than the Ace 12.0 have backward compatibility, why is it? When we try Ace 12.0 drivers with the Packages that are using csv or flat files are working fine where as only the Excel files both (xls and xlsx) are not working properly why?

a. Are the Ace 14.0 drivers backward compatible with the csv and flat files or do the Ace 12.0 drivers can import from the csv and flat files without Admin
privileges Many questions are raising with this odd behavior. Because the csv and flat files are getting imported without admin privileges too

b. Are the Ace 12.0 drivers not available only 14.0 are running all this but because of the registry entry or some uncleaned stuff from the installation is
showing the Ace 12.0 drivers in the drivers list? Many many questions are raising.
As the Packages with the Jet drivers are working fine I am not understanding what is missing or messing up here, is it the unavailability of Ace drivers or do the Ace drivers need Admin privileges and Jet drivers don't? If unavailability is the case then why Ace 12.0 drivers are fine with csv and flat files?

The problem for us in continuing with the Jet drivers is, we have some packages with xls which is fine with Jet drivers but we have some Packages with the xlsx files which are not going to work with the Jet drivers.

Is there any buddy who can answer me these questions? Any help would be greatly appreciated my friends. I am also researching doing lot of studies about behavior of these drivers.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."