Posts by tesuji

Hi,

here is some answer to your first question: "toatal due query: I got some great help on the forum to create my total due query. However it produces it with just total due, how could i have it by month?"

Answer (code not tested):
You can apply aggregate functions such as sum(), min(), avg() etc together with expressions or simple attributes of a table. For example, if you want to group various amounts by the month they accrued from together with the monthly subtotal you should code:

select monthname(datePaid) as Mois, sum(amount) as monthly_Mortgage from mortgage
  where DatePaid IS NULL  group by Mois;

Output could look like:
Mois_________monthly_Mortgage
January______3,541.58
February_______999.87
etc.

Important rule: All attributes appearing in the select statement which are not part of an aggregate function must be specified in the group-by clause, e.g.

select a, b, c, avg(d), min(e) from x where... group by a, b, c; -- correct clause
select a, b, avg(d), min(e) from x where... group by a; -- wrong clause, missing attribute b

(consider, there are about 30 useful aggregate functions)

Maybe you will get inspired by this group-by clause how to solve "I would like to show the users name along with the clients name in the query."? I'll noddle over this soon, and will be answering asap. To solve this task several tables must be joined together: referrals inner join clients inner join users, where "referrals" surrogates further tables.

well, it might be very helpful to know all related tables and all properly set-up primary and foreign keys. Without these pieces of information it's rather impossible to help you seriously (but pretty kind of stroking the crystal ball).

-- tesu

I just want to access database using SQL Server Management Studio .

But due to that trigger I can't access the interphase provided by the Studio .

So that, I want to drop that trigger.

Your initial posting has already told that.

Well, maybe you answer to my questions?

-- tesu

Hi

What actually happened?

1. Did you drop table ServerLogonHistory prior trigger?

2. Did you really have write access to table ServerLogonHistory?
(if not, you possibly need an "execute as" clause.)

3. Did you already try to drop the trigger via command prompt running sqlcmd?

(btw, if 17892 appears, you won't be able to access database using SQL Server Management Studio.
A possible work arround seems to be connecting via c++ program using odbc or java using jdbc.)

-- tesu

Well, actually the trigger solution works fine on my other databases (I will also test my first advice using commit etc). Btw, if you insert or update one or more tables you have to use begin transaction and commit/rollback too. Never rely on auto-commit configuration!

I should also note that i had had several serious problems with older mysql databases because msyql has its very own (kind of inconsiderate) notion on relational databases, sql, and relational design in the sense of Edgar Codd. In the past several times we discontinued very poorly designed applications based on MySQL (where poor design significantly correlates with the underlying DBMS in question), did redesigns and ported them to other, more reliable DBMS e.g. Oracle, Sybase ASE, DB2.

As of mid-week I will have mysql access at my company. There I will check both solution on a running mysql v5.1.

-- tesu

Hi

Usually I don't meddle in an advanced discussion, however I've got the feeling that this update problem (may be also other) coheres with the overall design. Could you post all tables which are related to pipeline_commission? Have all primary keys and foreign-key constraints been properly set up?

Btw, in "SELECT clients_ClientID, SUM(columnName1) ...." group-by clause is a must.

-- tesu

Hi,

Say Say Say would Paul McCartney say :)

Both variants do not meet c syntax. You may look up http://www.cplusplus.com/reference/clibrary/cstdio/fopen/.

-- tesu

hi,

ERM seems to be almost completed and sound.

May I do some notes to the primary, foreign keys? For example entity friendlist has an artificial key idAddedFriendnum although the attributes (idPerson, FriendID) unequivocally identifies each entry. Therefore (idPerson, FriendID) should be primary key of friendlist. Both attributes must be taken from table person, what means that idPerson, FriendID are foreign keys.

Same for entity LoginTime: (idPerson and Time) also identifies each entry uniquely here. Then why introducing an artificial key for LoginTime? Btw, you are using char string "Time" which is already used for data type.

I think it's a good idea to forgo artificial keys if entities already have enough strong attributes for forming the primary key. Just consider, artificial keys significantly increase redundancy and programming expenses.

-- tesu

hi,

please do these changes:

1. in create table atractions:
`referenceID` INT(11) NOT NULL, --> `referenceID` INT(11),
(possibly you already has done)

2. in create trigger
UPDATE referenceID SET new.referenceID = new.attID WHERE referenceID = NULL -->
UPDATE attractions SET referenceID = last_insert_id();

This works fine on MS SQL Server 2008 and Sybase SQL Anywhere V11. I am just sitting in front of them and tested it. (Remark: On both databases the meaning of last_insert_id() is @@identity.) Unfortunately no mysql right now.

-- tesu

This is a very common task. Seems to be appearing here every other week. If i recall correctly, about two years ago or so i posted a compact solution to this problem (Besides mine there are lots of solutions given by other people too). So seaching the forum won't be a bad idea ;)

-- tesu

Trigger will be automatically fired (=run) depending on the action performed on the table the trigger is assigned to.

I wrote "after insert" which means that the trigger will be fired automatically every time a new record has been inserted in mytable. Besides after insert there are before insert, before/after update/update of, before/after delete. Also you can combine insert, update, delete actions.

-- tesu

I've got another idea solving this problem by using a trigger:

create trigger insert_fk after insert on mytable
  for each row set new.referenceID = new.attID;

(Code neither tested)

-- tesu

Maybe you want to get the last inserted auto incremental key? This can be done by function LAST_INSERT_ID(). For example:

begin transaction;
...
insert into mytable (referenceID, ... ) values (null, ...);
update mytable set referenceID = attID where attID = last_insert_id();
...
commit;

-- tesu

Ah, i just see referenceID refers to attID within same table. This is kind of recursive reference one usually need to building hierarchical structures like trees, e.g. for bill of materials.

Referencing the same table is a common task (therefore my statement "if attributs refer to other tables" should be extended to " and same table".

-- tesu

It's a great thing: INNODB supports FOREIGN KEYS. You may simply add foreign-key clause,
http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html. One should carfully consider insert/update/delete policies.

Recomendation: never ever omit foreign key clauses if attributs refer to other tables. Foreign keys are the only way to guarantee referential integrity. Without that you have to spend enormous additional programming efforts (if you be a serious programmer).

-- tesu

hi kishanvkumar

Could you tell us something more about your task, particularly what database, language?
How do you fill the grid via select statement? Did you understand the meaning of host variable :age? Do you use such variables?

As for java or c++ one can fill the grid within a programming loop what seems to be easier doing the transposition (which should also be able using PHP).

-- tesu

Here is some code for testing palindromes:

bool isPalindrome(string s)
 {int i,l=s.length(); for (i=0; i<l/2; i++) if (s[i] != s[l-i-1]) return false; return true;}
int main(int argc, char *argv[]){
   string pal[] = {"able was I ere I saw elba", "Able was I ere I saw Elba", "Napoleon I noelopaN"};
   for (int i=0; i<3; i++){
     cout << "\"" << pal[i] <<  "\"  " << (isPalindrome(pal[i]) ? "is a " : "is not a ") << "palindrome" <<  "\n";
   }return 1;}

"able was I ere I saw elba" is a palindrome
"Able was I ere I saw Elba" is not a palindrome
"Napoleon I noelopaN" is a palindrome

Enjoy this snippet,
-- tesu

hi,

at first sight your table may look like:

medals (gamename, medal, loage, hiage)
Primary key: gamename, loage.

The select statement to get the medals for given game and age is:

select medal from medals where gamename="Cricket" and :age between loage and hiage;

Consider age is not an attribute of a table but a variable depending on your programming language (java, c++, php etc) or interactive system (e.g. sql management studio) where the select statement will be applied to. I myself often use c++ together with odbc. Here the host variable age must be indicated by a preceding colon like :age.

-- tesu

You may want to look up there: http://msdn.microsoft.com/en-us/library/ms682499%28VS.85%29.aspx

-- tesu

Yes, you can use SQL Server Management Studio this way:
security --> logins --> new login
--> general: create new login
--> server roles: mark at least sysadmin (there is no admin, sa is obsolete)

etc.

-- tesu

Let me make a further recommendation:

don't start database design on the create-table level. Start with designing the ERM. For this task you may use the really powerful Mysql Work Bench (v5.1 or so). If you publish an ERM on daniweb it is quite easier to help you.

-- tesu

WOW, Edgar Frank Codd is about turning in his grave speedily ...

This monster of table does not even fit first normal form because of its horizontally repeating groups (#1... #15). (for such tables you will never be able to construct effective sql select statements. There may also be a sea of anomalies...)

You need to decompose it into several tables where the most important table consist of ONLY ONE attribute set (code, product, price )

If you want to distinguish code #1 from code #15 or even a new code #33 from #99 then simply add sort_of attribute to your table, and you will get (sort_of, code, product, price).

Consider your poor but mysql-typical first solution: How to implement a new code above code15? You need to do changes of table structures, also additional programming tasks is necessary.

-- tesu

Sorry, i didn't notice that this thread is older than dirt ...

Hi
I am using standard ODBC interface to connect to SQL databases from within c or c++ programs. Almost every sql database, e.g. oracle, ms sqlserver, sql anywhere etc, fully supports ODBC, and it runs on windows, mac and unix as well. Here http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0902/en/html/dbpgen9/00000261.htm you will get some information about the database system and odbc interface I am working with.

The only drawback is that standard ODBC is not object-oriented but rather a simple call level interface. As already mentioned there are some o-o extentions like sqlapi++ shareware or QT-sql (my recommendation if you are looking for o-o odbc interface)

Here are some statements showing you how to access a sql table customer from sybase sql anywhere database using odbc v3:

//...
  #include "ntodbc.h"
  SQLHENV hEnv; SQLHDBC hDbc;
  SQLCHAR constring[] ="connection string depends on specific database";
  SQLHSTMT hStmt; SQLRETURN rc; SQLINTEGER idcust, clen; SQLCHAR custname[50];

  // Allocation of environment handle
  SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv );

  // Allocation of database handle
  SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc );

  // connect to database
  SQLConnect(hDbc, constring);

  // Allocation of statement handle
  rc = SQLAllocHandle( SQL_HANDLE_STMT, hDbc, &hStmt);

  // SQL statement_________________= 1 =___= 2 = 
  SQLCHAR sqlStatement[] = "SELECT idcust, custname FROM customer ORDER BY 1 DESC";

  // direct execution without preparation
  rc = SQLExecDirect( hStmt, sqlStatement, SQL_NTS );

  // fetching all records from result set
  while((rc = SQLFetch(hStmt)) != SQL_NO_DATA)
   {
     // get values = 1 = and = 2 = of current record 
     SQLGetData(hStmt, 1, SQL_C_ULONG, &idcust, 0, 0); 
     SQLGetData(hStmt, 2, …

hi hb25

I think it s a good idea to draw an ERM of your hotel reservation. A very useful tool doing so seems to be mysql workbench (though i don't like to work with that database)

-- tes

Hi, I am back again.

Because of "A French vocable got one or more English vocable equivalents and vice versa." there exists a many-to-many relationship between both entities. Therefore this is the correct solution:

Possibility 1.
Table 1: French vocabulary
Table 2: English vocabulary
Table 3: Translation table containing primary keys of table 1 and 2
DBGuy007

Possibility 2 is highly erroneous because it violates first normal form, creates anomalies, does not allow to creating effective sql- queries. It topsy-turvifys Codd's relational theory completely.

-- tesu

Hello,

>>> I installed and configured MySQL on my server and also installed the MySQL ODBC driver (version 5.1). What the problem is now that I couldn't establish a connection, despite that I supplied all the parameters corectly. All I kept getting is "unable to establish a connection: access denied to user".

You should post the original code completely where you try to connect to database via odbc datasource.

>>> I couldn't locate the table I created in the oracle database, when I wanted to link tables in the MS Access.

Did you ever connect to ODBC-database from within MS Access? What version of Access are you using?

General: Maybe you haven't enough rights to connect to these databases, possibly owner of database tables is not the user who wants to use them and that user hasn't been granted to allow accessing those tables.

krs,
tesu

Hello again,

The problem are duplicate date (fecha) values, why sum will be wrongly computed. To solve this, date and uniqueID must be combined in that order because date has higher priority than uniqueID (I think so). In the expression string(b.fecha, b.uniqueID) 1st, date and uniqueID will be converted to chars and 2nd, both chars will be concatenated, for example string(2008-03-05, '.', 10006) results in '2008-03-05.10006'. Now duplicates are impossible ! The following select statement

select uniqueID, fecha as "Date", Amount,  amount + coalesce((select sum(amount) from ozr b where string(b.fecha, '.', b.uniqueID) < string(a.fecha, '.', a.uniqueID)   ), 0) as sAmount, string("Date", '.', uniqueID) as "test only" from ozr a order by string("Date", '.', uniqueID);

gives the result you are asking for:

uniqueID    Date        Amount  sAmount    test only
----------------------------------------------------------
10006       2008-03-05  233.00  233.00      2008-03-05.10006
10007       2008-03-05  -33.00  200.00      2008-03-05.10007
10005       2008-04-05   40.00  240.00      2008-04-05.10005
10004       2008-05-05  -20.00  220.00      2008-05-05.10004

What you have to do is, you should find an appropriate function, like string(), what concatenates strings on your database, if MySql, CONCAT() might solve it. Possibly, you have to convert date type (b.fecha) and integer type (b.uniqueID) into varchars first. On my database system this task is done automatically.

-----
tesu

hello,

I am glad that you have got inspired by my solutions.

Questions:

1. How is your uniqueID computed?
2. Does uniqueID correspond to the date?
3. Isn't there a contradiction in

WHERE b.uniqueID < a.uniqueID

and your example

DB10006 | 05-3-08 | 233.00 | 233.00
DB10007 | 05-3-08 | -33.00 | 200.00
DB10005 | 05-4-08 | 40.00 | 240.00
DB10004 | 05-5-08 | -20.00 | 220.00

because smallest uniqueID DB10004 corresponds to largest date 05-5-08 ? Also, you should use ISO date: year-month-day, for example 2008-05-30.

-----
tesu

hi,

you may read this

http://gcc.gnu.org/

or that

http://gcc.gnu.org/onlinedocs/gcc-3.3.6/gcc/G_002b_002b-and-GCC.html

-----
tesu

return answer == 'y' ;

Hello

if you want to get an oracle developer you probably become fond of this site:
http://st-curriculum.oracle.com/tutorial/SQLDeveloper/index.htm

Also full oracle documentation is available at oracle.com

krs,
tesu

Hello rich_m,

how are you?

I am afraid you will have to wait at the Greek calends.

Once MySql company had a wonderful oracle clone. It was MaxDB, formerly SAPDB. And lots of people thought they would drop ugly MySql database and replace it by MaxDB. But they returned it back to SAP in 2007.

krs,
tesu

Hello

What is the primary key of responses, (response_id, range_id)?

To ensure referential integrity all foreign keys (fk) must be set up correctly.
Diaries ---< questions: diary_id is fk in questions.
Questions ---< responses: question_id is fk in responses.
Response ---- response_values: because there is one-to-one relationship, response_id (and range_id, if part of pk) must be pk and fk of response_values and response_value_id must be dropped. If you introduce an own pk response_value_id, you will have a one-to-many relationship, what’s wrong. Furthermore, database is unable to ensure referential integrity, that is you will only get rid of this lack by additional programming efforts despite the problem is most effectively solvable at sql level.
Same is true for questions ---- answers. Question_id of answers must be pk and fk, and answer_id has to be dropped.

krs,
tesu

possibly #include <iostream> or using namespace std forgotten.

That also works: return answer == 'y' ;

instead of: if ( answer == 'y' ) return true; else return false;

krs,
tesu

hi velodrom

SELECT current_date() - INTERVAL 1 DAY AS "Help yesterday !",   
    current_date() + INTERVAL 1 DAY AS "for tomorrow never come !";

may also give some results.

krs, tesu

ah sorry, Easter() is my own udf, please replace it by for example yourBirthday.
krs, tesu

hello

SELECT DATEDIFF(DAY, yourBirthday, getdate()) AS silly_days

may work on mssqlserver.

krs,
tesu

Hello,

you may have a look at DATE_FORMAT(date, format), especially specifier %p to convert date and time values.

krs,
tesu

Hi,

you may try:

select * from yourtable where TO_CHAR(your_date, 'MM') in ('01', '03',... ,'12')

krs,
tesu

Hello

that depends on your specific database system. There are Database systems where you can
type in: select myBirthday - now() as "My age is:" ;

You need to know the current-date function of your database, for example: today(), now(), currentdate() etc.

krs,
tesu

Hi, I'm developing issues database. How to get the ageing or how long the issues has been outstanding. TQ

Hello,

php default's is auto commit ON. Therefore, you first step should always be switching it OFF, if you want to do serious database programming. With auto commit ON you will never be able to update two tables correctly, if they correlate, apart from being fond of always playing Russian roulette.

krs,
tesu

Hello

Because both products differ in an important property they must have different part numbers. So you are able to manage different quantities, deliverers, prices, taxes etc.
You may put them into same category, for example "hard disks" to get a list of all hard disks if necessary.

krs,
tesu

By tricky triggers, as you may have already figured out.

Hello

Your mistake! You cannot do that !

-----
tesu

Hi,
How can i pick next or prevoius day in mysql? Any function like curdate() ?
Thanks

next: today + 1
previous: today - 1

hello,

>>> "select * from table1 where RAM between ... "

Never use *, the sign of slackers! You must always enumerate only those columns you really need for your datagrid!

There are lots of reasons for data type mismatch, so it s better you bring in the complete and exact error message.

----
tesu

hi,

how do you access the database? If by ODBC, you should set autocommit OFF. Unfortunately, ODBC's default is ON!

krs,
tesu

And also that "unaltered" code went through some modification meanwhile, LOL

hi,

there are some discrepancies:

1. What are PD.REFNO, PH1.BGROUP, PS1.REFNO ?

2. Is there a self-join: LEFT JOIN TableC LEFT JOIN TableC C ???

3. TableC of first left join doesn't have alias.

4. Select has 6 columns but only 3 results are showing.

Btw, threefold results may likely occur by adding same values three times. Such stuff happens if joining conditions are wrongly chosen what then results in undesirable cross products (cartesian products).

-----
tesu

hello,

in union clause the column which should be ordered cannot be denoted by column name. Column to be ordered must be specified by its position number like in:

select a, b, c  from t1
union
select x, y, z from t2
order by 2

That orders second column (b,y).

krs,
tesu