Database

17-Oct-12 9:31

Probably the quotes. Remove those and try your query again.

Bastard Programmer from Hell Suspicious | :suss:

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

How to access window ?

nghia09t317-Oct-12 5:27

nghia09t3

17-Oct-12 5:27

I am coding the project, which access to the Window, exactly is HDD(NTFS, FAT32), help me solve it!!!, thanksssss Big Grin | :-D

Eddy Vluggen17-Oct-12 9:32

17-Oct-12 9:32

Your question does not make any sense to me. Can you describe the problem a bit more?

Bastard Programmer from Hell Suspicious | :suss:

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

nghia09t317-Oct-12 12:47

nghia09t3

17-Oct-12 12:47

Hi Eddy Vluggen, i think my English is not good to express my purpose, hjhj,
and my project makes a "Program" which can show properties of
hardware, example type(NTFS/FAT32), used space,..

i am coding by C++, can you give to me the documents about this, thanks so much!!!!

Richard Andrew x6417-Oct-12 14:06

Richard Andrew x64

17-Oct-12 14:06

You can use WMI[^] to query system properties.

The difficult we do right away...
...the impossible takes slightly longer.

nghia09t317-Oct-12 14:29

nghia09t3

17-Oct-12 14:29

thanksssss Richard, Have a nice day!!!!!

Database to C++

shiftwik16-Oct-12 16:21

shiftwik

16-Oct-12 16:21

hey, noob here

I'm reading some database queries into a structure in C++
The database class I'm using MFC CDatabase/CRecordset reads all the data as type strings.

So my questions are:

What is the best practice for storing the fields? should I convert them to their appropriate data type as I read them from the data base,(using CODBCFieldInfo) to determine the datatype?

or is their a class that exists that already does converts the query to a structure automatically in C++?

what is the preferred method for handling datatypes for ma database?

Re: Database to C++

Jochen Arndt16-Oct-12 21:21

Jochen Arndt

16-Oct-12 21:21

You may use the CRecordset member function GetFieldValue()[^]. There are different overloaded versions of this function which retrieve the values as strings or variants. Use a function which retrieves a variant to get values as C types that correspond to the type stored in the database.

subquery using 1 table

john john mackey16-Oct-12 7:26

john john mackey

16-Oct-12 7:26

i'm not sure if this is a Circular Reference problem or not.

i have the following SQL query

SQL

SELECT tb1.SomeField,
(SELECT tb2.AccountName
FROM tblAccountInfo tb2
WHERE tb2.CustomerID = tb1.CustomerID) AS strAccountName
FROM tblAccountInfo tb1
WHERE tb1.AccountID = 123456

Since i'm using the same table in the subquery, is this a problem?

If it is a circular problem, how could i correct this?

Thanks,
JJ

fjdiewornncalwe16-Oct-12 8:01

fjdiewornncalwe

16-Oct-12 8:01

Because you are using the same column and table in the subquery, then you simply make the subquery redundant.

SQL

SELECT SomeField, AccountName FROM AccountInfo WHERE AccountID=123456

will get you the accurate result.
The issue in your query is that you may have more than 1 record in the accountInfo table with a given CustomerID so you will throw an error in that the subquery can have multiple results when it can only have one. If you actually want more than one result then you could do something like this, but I'm not entirely sure the inner join will be valid like this. Something you can try, though.

SQL

SELECT t1.SomeField,
       t2.AccountName
  FROM tblAccountInfo t1
       INNER JOIN tblAcocuntInfo t2 ON t2.CustomerID = t1.CustomerID
 WHERE t1.AccountID = 123456

I wasn't, now I am, then I won't be anymore.

john john mackey16-Oct-12 9:50

john john mackey

16-Oct-12 9:50

I think i need the construct i initially posed, but there may be the case where the result may return more than one result - in which case i could handle with MAX() or a simple aggregate.

When i first call tblAccountInfo with AccountID, it gives me 1 record in tblAccountInfo with a CustomerID.

When i call tblAccountInfo using CustomerID, it will return AccountName, different than if I used AccountID, but I need both calls to retrieve different sets of data from same table.

I need first call to tblAccountInfo to get the CustomerID to use in the second call. Sigh | :sigh:

Michael Potter16-Oct-12 10:38

Michael Potter

16-Oct-12 10:38

I have to agree with Marcus. The correlated subquery is redundant. I can't imagine any structure and/or data that would make this subquery useful.

tb2.CustomerID = tb1.CustomerID will always return both columns from the same row. If CustomerID is duplicated within the table, then it will throw an error.

It also seems strange that AccountName is not dependant upon AccountId as thier names imply. Are you sure your structure is designed correctly?

Thou mewling ill-breeding pignut!

AspDotNetDev16-Oct-12 11:25

AspDotNetDev

16-Oct-12 11:25

To answer your question, there will be no "circular problem". If you run this query, you should see that it works fine. What allows you to have the same table multiple times in a single query is aliasing, which you are doing (you are aliasing tblAccountInfo as tb1 and tb2).

Like others have mentioned, your query doesn't make sense as is. If, however, there can be multiple records with the same CustomerID, this would make sense (notice the TOP 1):

SQL

SELECT
    tb1.SomeField,
    (
        SELECT TOP 1
            tb2.AccountName
        FROM tblAccountInfo AS tb2
        WHERE
            tb2.CustomerID = tb1.CustomerID
    ) AS strAccountName
FROM tblAccountInfo AS tb1
WHERE
    tb1.AccountID = 123456

But then, you also mentioned an aggregate function (such as MAX) in a reply above, which would accomplish much the same. If CustomerID is a unique field, then an aggregate or TOP 1 would be unnecessary, but then so would the subquery.

Aadhar Joshi16-Oct-12 20:28

Aadhar Joshi

16-Oct-12 20:28

First thing is u should avoid sub query as a column and make a inner join and take a column from sub table as it improves your performance when u fire your query with large data.

Second is it works fine till sub query produces single record for parameter, if there is more than one row returned by sub query, it throws errors.

Third for same u can use distinct clause in sub query.

High Number of Lock Requests

john john mackey17-Oct-12 4:38

john john mackey

17-Oct-12 4:38

Thanks for all the replies and suggestions. as it stands the table structure does reveal different AccountNames when queried on different columns (AccountID, CustomerID). And yes, that seems redundant because for case of one record having unique set of AccountID, CustomerID, and AccountName it is obvious.
For case of many records with mixed combinations of those 3 columns, I am not guaranteed the AccountName is what I want.
I think I'm only concerned if AccountName is not NULL.
Thanks!

cjb11016-Oct-12 3:23

cjb110

16-Oct-12 3:23

We're seeing a large number of lock requests / sec on our SQL 2008 db, the current average is ~240,000.

They seem to be either key locks or page locks. There's no time-outs, deadlocks and the average wait time is 0.
The only other stat that seems high is the context switches, which is around 1k-3k on our 2cpu system.

Is this something to be worried about?

Re: High Number of Lock Requests

Eddy Vluggen17-Oct-12 0:17

17-Oct-12 0:17

cjb110 wrote:
Is this something to be worried about?

Only if you have time to spare. It might hint that additional optimization is possible, but it's not guaranteed that it can be optimized further - it'd be a fun exercise though. You might find this[^] an interesting read to get you started.

Bastard Programmer from Hell Suspicious | :suss:

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

SQL Select Where query

Joe Stansfield15-Oct-12 0:37

15-Oct-12 0:37

Hi all,

Firstly, I must confess to being a very amateur programmer with a lot to learn! However, I'm battling away and am slowly getting my head around things.

I have a query I have written which as follows:

SELECT File_Name
WHERE (File_Name = @File_Name1)

File_Name1 is a user input parameter. My problem is that, even if the record does not exist, it returns the record, resulting in an error on the client side.
Is it possible to edit that query to return a file_name field value of say, 1, if the searched record doesn't exist?

Any help appreciated! (Also for note most of my queries are generated by VStudio, so my understanding of them leaves a lot to be desired...)

Eddy Vluggen15-Oct-12 0:57

15-Oct-12 0:57

Using which database-dialect? If you're using Sql Server, then the IsNull[^]-function might be handy.

SQL

SELECT ISNULL(File_Name, '1')
  FROM Files
 WHERE File_Name = @FileName1

If we're talking about more than a single record, you'd need a join and a dummy-table.

Bastard Programmer from Hell Suspicious | :suss:

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

Joe Stansfield15-Oct-12 1:15

15-Oct-12 1:15

Yea I am using SQL Server.

At the moment the query is designed to open file which the user requests (file_name). This works really well if the input matches a record in the database.

The problem is when it doesn't match a stored record. Will using the query as above with ISNULL work? And if so, am I right in assuming that the query first executes the where part of the query, then the IFNULL portion and, if still valid after that, retrieves the correct record?

Eddy Vluggen15-Oct-12 1:22

15-Oct-12 1:22

Joe Stansfield wrote:
The problem is when it doesn't match a stored record. Will using the query as above with ISNULL work?

Dunno - it will not return an empty record, but it'll return '1' when the value is empty. Would that work with the rest of your code?

Joe Stansfield wrote:
And if so, am I right in assuming that the query first executes the where part of the query, then the IFNULL portion and, if still valid after that, retrieves the correct record?

Yes.

Bastard Programmer from Hell Suspicious | :suss:

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

Joe Stansfield15-Oct-12 1:28

15-Oct-12 1:28

Yea that will fix up the error from no value being entered.

But I'm struggling to understand and make it work if, say, an input of 100 is entered. If 100 isn't a valid ID of a record, it won't return anything. I'd like it to return 1 for ease of avoiding an error and possible loop error (which could also be put down to poor programming by me on the C# side of things).

With that said, if 99 is entered and that is a valid record, then 99 would need to be returned by the query.

Sorry for not understanding your explanation.

Eddy Vluggen15-Oct-12 2:15

15-Oct-12 2:15

Joe Stansfield wrote:
With that said, if 99 is entered and that is a valid record, then 99 would need to be returned by the query.

That's not how things are done usually; all records in the database "should" be valid records with a link. If not, you got a modeling-error that could bite you later on.

We're using "empty" values (nulls) if we can not link to another record. The database-server is built around that concept; you can get all records from two (or more tables) "joined" into a single table, explicitly asking for a collection with/without the linked records.

Joe Stansfield wrote:
I'd like it to return 1 for ease of avoiding an error and possible loop error

What loop? Do you have an example?

FWIW, you don't need to loop to load all records; you fetch 'em in a single haul.

Bastard Programmer from Hell Suspicious | :suss:

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

Joe Stansfield15-Oct-12 10:48