Posts by tesuji

Hello again

He, what about the 66 whales, could any of them be rescued?

Hell yeah, '07:20:00' AND '15:19:59' evaluates to 1! I have never seen other database systems than mysql doing seriously boolean algebra on time strings! Though this be kind of compensation for the below rough violation of ANSI SQL standard aliases? (now() of 1st line can differs from 2nd and 3rd lines ones!)

No, your case is not correct. I would make use of such a construct:

select time(now()) as "too sad, mysql cannot use this alias elsewhere",
case 
  when (time(now()) between '07:20:00' and '15:19:59') then 'The early bird catches the worm'
  when (time(now()) between '15:20:00' and '23:19:59') then 'We are from swinging' 
  else 'Nighty night everyone!'
end as "what a pitty";
/*
too sad, mysql cannot use this alias elsewhere  what a pitty
--------------------------------------------------------------------
15:50:42                                        We are from swinging

*/

-- tesu

(btw, a practial example like your earlier one would help much)

Hello

If your problem is still existed, you may check whether TNSNAMES.ORA is in your directory path. There could also be discrepance between SQLNET.ORA and TNSNAMES.ORA. You could post both files here (make copy of them and replace your private data). Well, I am not an ora admin, just working with it, nevertheless I am trying to help you.

Where is our friend Deabsis Das? He is true Oracle expert and can help you instantly.

-- tes

Hello Garrett85

Congratulations that you started to learn SQL from this great book Simply SQL written by my friend Rudy Limeback! This is the finest book for beginners I ever read. Clearly and precisely written, covering all important aspects of (DML-) SQL, contains a wealth of useful examples and tips, thoroughly founded on Rudys comprehensive experiences on database field.

Yes, it is "The fun and easy way to learn best-practice SQL".

I am sure that Rudy will add a small chapter on DDL-SQL to the next edition, the only very little SQL-part I am really missing from his wonderful book.

As for MySQL workbench you have already downloaded, assuming you have installed Ubuntu 10.04 (lucid) then workbench version must be 5.1.xx which does not have integrated Query SQL broswer to execute simple SQL statement like SELECT * FROM ... nor it has mysql administration functionality. However, it is possible to build version 5.2, which contains query and admin, from the sources. I have tested 5.2 new functionalities and in my opinion it is still in a very beta state. Of course, it is a good tool for designing kind of entity relationship models (ERM, not EER), much more better than its old error-prone predecessor (V4.2) from source-forge.

However, the most important thing is, you need to install a standard SQL database first, if you don't have already done this job. So what database for beginners?

Well, if you complete Rudy's book, you will have …

Hi

>>> Also I though I would ask is it possible to replace the callback function with just the integer 0 as I have been doing or is there something special with the callback function?

The link given by gerard4143 shows an example where callback function isn't further necessary (modern version of SQLite in 5 Minutes)

-- tesu

There is a great book about C++ and GUI programming by Jasmin Christian Blanchette of trolltech dot com. His book, well, about 60 USD, also older versions are available, is great for learning QT (cute). Following his tutorial you will have learnt the very basics of GUI programming within ten minutes, and probably you won't make use of any other C++ GUI system than that portable QT for windows, unix, mac.

-- tesu

Only starting hint, google this: generalization specialization relational modeling

Now it's your turn.

You may post in what you will then have decided, and we will discussing and possibly also improving your results.

-- tesu

Well, and the body of function factorial(...) should be better completely replaced by

{
  if(input<=1) return 1;
    else return input*factorial(input-1);
}

Meanwhile I have got an idea which deals with difference operation on sets (A \ B = {x | x element(A) and not element(B)}, sorry this math is not that important) which is MINUS (Oracle specific) or EXCEPT, the latter is a standard operation in ANSI SQL, however mySQL does not support set operations except UNION.

One can simulate set difference by means of subselect. You may try this code:

select distinct tc, serial from history 
where tc not in (select a.tc from history a join meter b on a.serial = b.serial where b.status=1);

Pls try it out, I haven't test this code so far. Tell me the result asap. Then I'll dive into your examples.

-- tesu

Hello

Yes, company is not an entity. Your explanation is correct. If your application is to manage many such parcel-service companies, it were a multi-client software (like SAP R/3).

There are further entities I have found at a first glance:

- Delivery_options and conditions
- Serice_stations, especially where packages are being tracked
- Package_Movement (locations, timestamp, conditions)
- Delivery_details (including recipient's confirmation)

You could draw and post in an ERD (nice tool is freeware visual paradigm) so one can examine its completeness and correct relationships.

-- tesu

Hi

Supposing your both queries select staffids which are not on holidays OR in
sessions you should select staffids which are in both result sets:

select * from staff 
  where staffid in (your select not on holidays )
    AND staffid in (your select not in sessions);

-- tesu

Hi red_ruewei,

the result corresponds completely to your select statement. However, it seems that it does not meet your concept. So what's wrong, what should be the exact result? Can you give an example? (Unfortunately, I have deleted the data once you sent to me. So I am unable to look inside the problem).

-- tesu

Aha age columns. That makes the query tremendously simpler:

SELECT all_my_dating FROM mytables WHERE age BETWEEN 30 AND 35;

... BUT: where keeps the DOB file? And will those ages be manually updated in database just before or just after pupil's birthday partys every year?

-- tesu

I am back again. You are completely right, I have mistaken your problem. However, the most important thing is that:

Given your table:

DB_Salgspris db_interval_start db_interval_end db_customer
59          |         2       |   3           | 59
99          |         7       |   8           | 149
149         |         11      |   12          | 199
699         |         65      |   68          | 1099
1099        |         102     |  110          | 1699
 
If I do this:

SELECT DB_SALGSPRIS from pris_interval WHERE DB_INTERVAL_START < '11.78' AND DB_INTERVAL_END >= '11.78'

I get:

DB_SALGSPRIS
------------
149

If I do this:

SELECT DB_CUSTOMER  from pris_interval WHERE DB_INTERVAL_START < '7.19'AND DB_INTERVAL_END >= '7.19'

I get:

DB_CUSTOMER
-----------
149

These results are correct. There aren't any further rows but the listed two. So something must be wrong with your database. I did both selects on Sybase database (MS SQL Server's mother).

-- tesu

Hello and good morning

I was playing about with your sample data and I found a simple solution based on self-join. I'll explain it in two steps:

/* 1. Step: All different mess_id and their corresponding maximum date_sent-timestamps are selected:*/

select mess_id, max(date_sent) as maxdate from yourtable group by mess_id;

/*
The result should be:

---------------------------------------
|   mess_id    |       maxdate        |
---------------------------------------
|  12          |  2010-08-04 21:00:22 |
|  448         |  2010-07-13 17:48:24 |
---------------------------------------

To get the missing columns id and message this intermediate result must be self-joined with original table. So the 1st-step select is put in from clause of a second select and joined with original table. Thanks God, such kind of subselect in from-clause has finally been supported by mysql since near version 4.

2. Step: Self-join
*/
select y.id as "ID", y.mess_id as "Message id", y.message as "Message", y.date_sent as "Date sent"
from (select mess_id, max(date_sent) as maxdate from yourtable group by mess_id) as x 
  join yourtable y on x.mess_id = y.mess_id and x.maxdate = y.date_sent order by y.id;

/*
Now the result should be:

---------------------------------------------------------
 ID |  Message id  |   Message    |      Date sent      |
---------------------------------------------------------
 0  |  12          |  mess 12_0   | 2010-08-04 21:00:22 |
 4  |  448         |  mess 448_2  | 2010-07-13 17:48:24 |
---------------------------------------------------------
*/

Simple solution, isn't it :-O

You may test the select statement of 2nd step. There might be typos yet it should fit mysql syntactically largely. I didn't test it so far.

-- tesu

Hi

don't worry, rows in database tables never have a particular order. This is a general rule for relational databases are based on set theory (at least as regard that). So if you want to get a certain order, you must make explicitly use of order function by ORDER BY clause, for example:

SELECT * FROM dbo.names ORDER BY id_record ;
/* result (assumed that there exist a column id_record) 
1. dog
2. cat
4. llama
5. snake **now this is wrong, right?**
*/

.
If you don't have column id_record to order your rows, you can also use the lastname und name columns to get alphabetical order, for example:

SELECT * FROM dbo.names ORDER BY lastname asc, name desc;

Where ASC stands for ascending order and DESC for descending order.

Or you add a further column id_record to your table and you nominate id_record to be primary key of this table:

create table names(id_record integer not null, prename varchar(50), surname varchar(50),
primary key (id_record));

-- tesu

Hello

Usually such project is based on OOA/OOD, therefore realising such a design with c++ well structured classes incorporated in convenient design pattern (mvc, facade etc) are a must.

How to realize persistently stored data is another question.

Stock management requires complex data model and consists of many entities which are related to each other so inserting, updating, or deleting entity data aren't simple tasks. Using binary files for that require enormous individual programming efforts and the result would be a rather imperfect propritary data management system for stock management.

So to keep your additional c++ costs low and to get instant access to all these great data management functions you would have else implemented in a sweat-inducing way you should make use of a relational database. Using a RDBMS within C++ is standardized and rather simple. There are lots of tutorials on the web, also on daniweb this topic has been covered more often. I myself prefer ODBC, plain C or class-oriented interfaces as well as. As for the database, I am usually fond in MS SQL Server and Sybase SQL Anywhere (if I have the option, I wouldn't harm myself with mys..).

If you decide for relational database, I immediately suggest you to design a complete entity relationship model (ERM) of your stock management first before starting to implement data models on your chosen database. I myself believe the ERM is the most important task to map real-world requirements successfully and effectively into …

Hi Peter

I couldn´t stop thinking about the longest-common-substring problem for SQL language (user defined function). So I took and old version of LCS which I once implemented in plain C. Its time complexity is O(m*n), storage O(m+n). I translated this C version line by line into a user defined function (PSM of SQL 1999) for Microsoft SQL Server and Sybase database. Finally I also compiled a Java version from my old C LCS function.

All three functions implement the very same LCS algorithm and I myself can also affirm that every function is based on the same programming methods and approaches what guaranties that they have same O(m*n).

Then I did some benchmarks using your sample strings containing Hungarian Debrecen. I executed all three functions/ methods 100000 times on these strings on an "medium upper class" win XP computer.

[I]Here are the results for 100000 function calls:

Programming Language          Execution time
-------------------------------------------- 
plain C (no classes)             2.2 seconds       
Java, static member              2.8 seconds
SQL user defined function     2658.0 seconds [/I]

I am very surprised at the poor SQL-UDF result. I had never believed that SQL-UDF would consume a thousand times execution time of C/Java function. So it is rather out of the question to implement LCS algorithm in SQL user defined function for practical usage.

This benchmark pleasantly surprised one by the computing power of Java. It put up a great fight by contrast with fast C for this non-numerical algorithm.

-- tesu

write a program that will identify the longest substring in two given strings that is common to both.

Hi

LCS is a famous problem in programming. You my have a look at my posting there and follow the links you possibly will find solutions.

Be aware of that LCS isn't that easy to program. Poor solutions have time complexity of O(n^4), optimal solutions based on dynamic programming have O(n^2) or (O(mn)) in both time and storage.

-- tesu

Well, I think what he is looking for is to find the longest common substring (LCS) of both strings

/system/images/777/medium/Debrecen_-_University.jpg?1279547675

Debrecen_-_Protestant_Great_Church.jpg

where the green marked substrings seems to be such LCS. There is also a longest common subsequence problem where the sequence of chars need not to be contiguously stored.

LCS is a famous problem usually solved by dynamic programming, its time complexity is O(mn). Some theory can be found here. There are also implementations on wikibooks in various languages. Unfortunately, I haven't seen any implementation in PSM (pl/sql) for SQL user defined function (UDF). I am sure one cannot do that in SQL queries directly. So an UDF written in PSM or even a C/C++ implementation what can be called from SQL, what is possible as for example in postgresql, seems to be a solution. Yet what I have seen are some PHP implementations having poor O(n^4).

-- tesu

Hi Tommy,

greetings from the hood.

If I understand you correctly, you want to list all customers which have purchased goods for more than 5000EUR in 2010. This query should solve it (you need to substitute in your appropriate column names):

WITH totalAmount (Kunder, ID, Amount) as
(
  SELECT k.kunderName as "Kunder", k.kunderID as "ID", 
   SUM(f.Quantity * f.Itemprice) as Amount from kunder k join faklin f 
    ON k.kunderID = f.kunderID
     WHERE f.purchasedDate between '2010-01-01' AND '2010-12-31'
      GROUP BY k.kunderName, k.kunderID
)
SELECT * from totalAmount where Amount >= 5000 ORDER BY Amount desc;

Possibly you need to replace SUM(f.Quantity * f.Itemprice) by SUM(f.invoiceAmount), if you don't deal with invoice items.

Possibly you may also have to change date format. I took standard ISO date format yet your DB
may have other settings (Denmark's date format same as Bavaria's?)

-- tesu

let me explain u clearly..
I have a table name product Details I have Pid as a field.
then another table name is product order so if i update my Pid in product details simultanoeusly in product order also have pid,it should be also updated...how to do that..hope its clear for u now?

This objective can easily be achieved by the table's definitions. You should make use of properly defined foreign keys, as for example:

create table productdetails(pid integer not null, description varchar(100), ...further data..., 
primary key(pid));

create table productorder (shouldhaveprimarykeytoo integer not null, pid integer, 
...further data...,
primary key(shouldhaveprimarykeytoo),
[B]foreign key (pid) references productdetails(pid) [U]ON UPDATE CASCADE[/U][/B]);

Now if pid changes in master table productdetails its reference in table productorder will automatically be changed due to update policy CASCADE.

Well, as already stated, changing values of primary keys is a severe issue and should not be daily business for it requires such dangerous policies as cascade which can easily destoy data consistence of a whole database.

-- tesu

Hi,

obviously your asm is real mode (because of int 10H (= 0x10) then the code for inputting a string terminated by enter key (0DH) could be:

maxleng equ 100 ; maxleng of inbuffer is 100 chars ;;; not knowing EQU? then replace maxleng of inbuffer by 100.

inbuffer db maxleng, ?, maxleng dup(?)     ;;; defines special buffer for int 21h/0AH function

;;; 0AH require offset of special buffer in dx
mov dx, offset inbuffer 
mov ah, 0ah
int 21h      ;;; read string, if input length exceeds inbuffer[0] string is cut

;;; Now inbuffer[1] contains number of chars read in, inbuffer[2].. inbuffer[1+inbuffer[1]] contains these chars

;;; For output place '$' in inbuffer[2+inbuffer[1]], be careful: must not exceed maxleng

mov bx, 0;
mov bl, inbuffer[1]
mov inbuffer[bx+2], '$'   ;;; terminate string by $ for output int 21h/09h

;;; now output inbuffer to screen, starting from inbuffer[2] 
mov dx, offset inbuffer+2
mov ah,09H                ;;; outputs $ terminated string to screen
int 21h

Well, that's it all. But this code not tested. You should check whether I set the '$' in the right place.

Tell me, if above code is working, also your changes made.

-- tesu

I am afraid this can't be done in a plain select statement. You need some procedural code for parsing the string which contain the subject numbers and translating these numbers into subject names. A userdefined function can easily solve this problem, in principle:

create function parseandreplace (in vName varchar(50)) returns varchar(255) 
begin

... some declarations and initialisations here ...

-- get subject string
select subject into vSubject from user_subject where name = vName;

-- put a ',' on right end of subject string
set vSubject = INSERTSTR(length(vSubject)+1, vSubject, ','); 
 
-- for each subject number in vSubject do:
WHILE i < length(vSubject) LOOP

  -- position of next comma
  set comma = locate (vSubject, ',', i);

  -- get next subject number
  set vID = cast(substr(vSubject, i, comma-i) as integer);  

  -- get name of subject  
  select subjectName into vsubjectName from subject where id = vID;

  -- concat names
  set vsubjectNames = vsubjectNames || ', ' || vsubjectName;

  -- scip to next subject number
  set i = comma+1; 
END LOOP;

-- return concat names
RETURN vsubjectNames;
END;

-- usage
select name, parseandreplace(name) from  user_subject;

Well, above code should work in principle. Similar things could also be done in php. As for a UDF effort for programming and testing should not exceed one hour.

-- tesu

as there are no return statements, in the solution you gave, so does it means that stack unwinding is independent of the return statement.

the functions returns back due to stack unwinding.

one more thing, what all things are stored in the stack while pushing in the stack (function call)

Hi mithunp

To clarify what happen if a C function is called, let's analyse what will happen if this function int fun (int a, double b) is to execute.

1. Just before the call
First, the calling program saves the values of EAX, ECX and EDX on the stack

Then the value of last actual parameter b is pushed on the stack. Next follows parameter a.

Finally contains of EBP register is pushed which is now on top of stack.

2. Now we enter function fun().
All local variables locally defined in fun will also be created on the stack. Then some calculation is done, especially the return value is calculated.

3. Just before leaving function fun
All locally defined variables are poped from stack (partly unwinding)

Important: the return value is passed back in EAX register and not on the stack. This is true as long as the length of the return value is less or equal 4 bytes (if greater, capacity of EAX would be exceeded).

Longer return values will be passed back by an extra actual parameter which is automatically generated if its length exceeds 4 …

Hello

You could try this:

select * from t3 where c1 || to_char(c3) in (select c1 || to_char(max(c3)) from t3 group by c1) order by c1, c2, c3;

c1, c2, c3 are your three columns. The subselect generates a temporary key from grouped c1 column and the corresponding to_char(maximums of c3). The outer select also generates a temporary key from c1 und c3 without grouping and aggregating. Then all rows with identical keys are seleted. Certainly, there exist more efficient solutions, however above select also finds duplicate rows. If you don't care duplicates, try: select distinct * ...

Hope it will work, nevertheless, this query not tested.

-- tesu

Standard dev is Sum((datapt - mean)^2)/(counter -1) so you can't add the squared data and then subtract the mean squared unfortunately since there is a cross term of -2*datapt*mean when you square it out. You need to hold all the data then calculate the mean, then do the calculation.

Sorry, even a phrenologist is allowed to make a mistake. Unfortunately, your implication is incorrect.
Proof: ;)

Let standard deviation be defined as:

s^2 = sum (a - xi)^2 /( n - 1)

By multiplying out the sum we get:

(n-1)*s^2 = sum(a^2) - 2*sum (a * xi) + sum(xi^2)
(n-1)*s^2 = sum(a^2) - 2*a*sum(xi) + sum(xi^2)
(n-1)*s^2 = sum(a^2) - 2*a*n*a + sum(xi^2)
(n-1)*s^2 = sum(xi^2) - n*a^2

and finally:

s = sqrt((sum(xi^2) - n*a^2)/(n-1)); 

(where n is number of measured data, xi is a single measured data, a is average sum(xi)/n, s is standard deviation of the measurement series, ^ stands for power of, sqrt() is square root)

You may take a try:

n = 5                              
i      1    2    3    4    5    sum
x      1    2    3    4    5    15
xi^2   1    4    9   16   25    55

a = 15 / 5 = 3
s = sqrt((55 - 5 * 3^2)/(5-1)) = sqrt(( 55 - 45) / 4 ) = sqrt(10/4) = 1.58

Prove it by taking the inconvenient way:

s = sqrt(((3-1)^2 + (3-2)^2 +(3-3)^2 +(3-4)^2 +(3-5)^2)/(5-1))  ?

You can also examine this facts on wikipedia. You may study there the last formula of paragraph "Identities …

Hello

Could that be that you have mistaken counter and avg in:

stdDev = sqrt((totalDevSqrd / (avg - 1))); /* standard deviation equals the square root of the total square deviations devided by the average minus 1 */

// shouldn't it be?
	stdDev = sqrt((totalDevSqrd / (counter - 1))); //... divided by counter-1

-- tesu

Hi

I didn't found any error function when I was searching the compilers (gcc, visual c, watcom). Therefore, I took an approximation from Abramowitz/Stegun and wrote my own erf(x). It's approximation error is about 10^(-7). which was almost always sufficient for my apps. erf(x) = 1-erfc(x).

double erf(double x)
{  
/* erf(z) = 2/sqrt(pi) * Integral(0..x) exp( -t^2) dt
erf(0.01) = 0.0112834772 erf(3.7) = 0.9999998325
Abramowitz/Stegun: p299, |erf(z)-erf| <= 1.5*10^(-7) 
*/
 double y = 1.0 / ( 1.0 + 0.3275911 * x);   
 return 1 - (((((
        + 1.061405429  * y
        - 1.453152027) * y
        + 1.421413741) * y
        - 0.284496736) * y 
        + 0.254829592) * y) 
        * exp (-x * x);      
}

Maybe this small function is also useful for you.

--tesu

Hello,

I am glad to meeting here a postgresql fan too! So let's proceed unter mysql's hood.

What do you actually mean by

>> relataions.relation_type_id foreign from relation_type.id
>> relations.from_id foreign from items.id
>> relations.to_id foreign from items.id
?

Sure, because of the one-to-many relationships relataions.relation_type_id is a foreign key which points to master table relation_type. Both columns are used in the where clause to define the inner join.

But this usage DOES NOT mean that the table also has a foreign-key constraint what is usually table-defined in:

create table relataions (... id integer, ... FOREIGN KEY (id) REFERENCES relation_type, ...);

Also usage of relation_type.id does not mean that id is primary key of table relation_type, as usually defined in:

create table relation_type (... id integer, ... PRIMARY KEY (id), ...);

Well, if all these primary and foreign key constraints are well defined the way I just sketched out, there would be automatically put an index on all those primary and foreign key columns.

These indexes are very important to carry out all inner joins with high speed. If these indexes DO NOT exist because of omitting proper definition of primary AND foreign keys the inner joins would degenerate into cartesian products (cross products).

For example if all of your three tables have 1000 rows each, the number of comparisons necessary to compute the degenerated inner …

Well, if you want to do suchlike string separation in query statement, you can make use of ora's string functions, such as:

select 'tag: key1, key2, key3' as st, length(st) as le, instr(st, ':') as lo, substr(st, 1, lo-1) as tag, substr(st, lo+1, le-lo) as whateverfor;
/*
st                     le  lo tag  whateverfor
---------------------------------------------------
tag: key1, key2, key3  21  4  tag  key1, key2, key3 
*/

-- tesu

Hi,

Am a self teaching Newbie Assembly programmer :) Still in the theoretical Stages.

My question is....

According to the book am reading

When you convert 254(decimal) to hexadecimal you get 0FEh.

And when you convert -2(decimal) to hexadecimal using two's complement you get OFEh.

When i do it manually i get FEh.

Is the zero before FEh always necessary or is it just another way of representing your code..?
Am fully aware that 0 in hexaecimal stands for 0000(base 2)

Thanks in advance......

Well, the 0 of 0FEH is wrong if it should represent -2D. The most significant bit (msb) of an integer numbers always indicates whether the number is positive or negative. msb = 0 is for positive numbers, msb=1 stands for negative numbers, where negative whole numbers usually are in two's complement.

Let's code -2D in binary 2k and then in hex 16k:

2D = 10 --> extend it by some 0 to indicate true positive number: 0000 0010
now 1K: 1111 1101, 2k = 1k + 1 = 1111 1101 + 1 = 1111 1110B
-2D = 1111 1110B = FEH

If you add a 0, you get 0FEH, binary: 00001111 1110B what is a positive number, therefore wrong! If you want to extend a negative whole number, you must extend with the sign bit, that is for example FEH = FFFEH = FFFFFFFEH.

-- tesu

Hi,

Yes, I know, MySQL indeed did this insane expansion of the mathematically well defined select-order-by clause. Now everybody will be able to use MySQL for comparing apples to oranges without cease. Now you should, no, you must carefully examine every row result whether it contains idiotic combinations of invalid column values or not.

Let me allow to cite from chapter 11.16.3 of MySQL reference manual:

"MySQL extends the use of GROUP BY so that you can use nonaggregated columns or calculations in the select list that do not appear in the GROUP BY clause."

And further on this warning:

"When using this feature, all rows in each group should have the same values for the columns that are ommitted from the GROUP BY part. The server is free to return any value from the group, so the results are indeterminate unless all values are the same."

Do you got it? Indeed, MySQL pulled off a feat to turn the complete relational algebra and set theorie topsy-turvy.

NEVER EVER use this foolish MySQL feature!

-- tesu

Hi,

I think your RK steps are not correct, you may check this.

Also a RK function of my own shows other steps for y direction:

tSCALAR ruku5( tODEF f, tSCALAR x, tSCALAR y, tSCALAR h)
// Common 4th order Runge–Kutta method
{  tSCALAR k1, k2, k3, k4;
   k1 = h * f ( x, y );
   k2 = h * f ( x + h / 2, y + k1 / 2 );
   k3 = h * f ( x + h / 2, y + k2 / 2 );
   k4 = h * f ( x + h    , y + k3 );
   return (y + ( k1 + 2 * ( k2 + k3 ) + k4 ) / 6);
}

So I think some of your loops should be changed to as commented:

for (i=0;i<N;i++) t1[i]=y[i]+0.5*(k1[i]=H*fp(x, y, i));
    for (i=0;i<N;i++) t2[i]=y[i]+0.5*(k2[i]=H*fp(x+h, t1, i));      // <-- H*fp(x+h,k1[i]/2, i)
    for (i=0;i<N;i++) t3[i]=y[i]+    (k3[i]=H*fp(x+h, t2, i));      // <-- H*fp(x+h, k2[i]/2,i)
    for (i=0;i<N;i++) k4[i]=                H*fp(x+H, t3, i);       // <-- H*fp(x+H, k3[i], i)
    for (i=0;i<N;i++) y[i]+=(k1[i]+2*k2[i]+2*k3[i]+k4[i])/6.0;

Obviously the arrays t1, t2, t3 aren't necessary for computing y in last for-loop. Therefore scalar k1, k2, k3, k4, and array y can be computed within one loop.

-- tesu

Ignore all these scrub posters and use Horner's algorithm.

Hi Rashakil,

as for Horner you are completely right!

I am already using Horner's first algorithm in this code:

void pore (int d, float a[], float x, float *r){
  if (d>0)pore(d-1,a,x,r); *r=x**r+a[d];}

(I have just somewhat minimized this by dropping two curly brackets what is possible if d>=0 is also replaced by d>0.)

Just consider:

*r=x**r+a[d]

this is very straight Horner's first algorithm. Obviously, you might have ignore this facts accidently.

btw, this very useful scheme should be named after Ruffini because he invited it several years earlier than Horner, therefore all Spanish people call it "Regla de Ruffini".

I am not native English (certainly you have already noticed this), so I would really appreciate you if you could explain the meaning of:

"scrub posters"

to me.

Thank you very much!

-- tesu

Hi,

though your polynomial function calls itself recursively (if we believe that "polinomyal" equals to "polynomial"), yet the value of the polynomial isn't computed recursively instead it is done in for loop if n becomes 0.

You may study this code:

void pore(int d, float a[], float x, float *r){
 if (d>=0){pore(d-1, a, x, r); *r = x**r+a[d];}}

If pore() is called from this context:

// recursively calculated polynomial
//  p(x) = 1*x^3 + 2*x^2 + 3*x^1 + 4*x^0  
int d=3; float a[]={1,2,3,4}, x=2.5, r=0; 
pore(d, a, x, &r);
cout << "polynomial: " << r << endl;

The result is polynomial: 39.625

-- tesu

Odbc requires that a data source (DSN) be created by means of your operating system. There you have to specify the driver for your specific database. Therfore this driver, delivered by mysql, must be installed priorly on your computer.

On my side we do developing c/c++ aps on and for linux and windows. That is the reason why we use pure odbc.

On windows only I would also prefer Mysql connector/c++ as Ancient Dragon already suggested. Indeed, mysql put the odbc interface and some more stuff into nice c++ class whereby it's really easier to do programming as against pure odbc.

Here you will find detail information on connector/c++. There are also complete examples you can just cut and past them into your c++ ide.

Because connector/c++ also works on odbc the Microsoft odbc manager is required. And that odbc manager always requires that the specific driver of your mysql database be installed. So I am afraid installing an odbc driver on every client side is also necessary, correct me if i am wrong.

-- tesu

Hi andydeans,

there seems to be a lot of problems, kind of going round in circles. I personally would suggest that we should go these problems step by step. In each step we shall handle a small and manageable problem. To each step I'll make a suggestion of sql code. Then it will be your task to get that code running on your database. If errors occur, you must post firstly the ORIGINAL sql statement which caused the error and secondly the complete error message. If and only if a current problem is solved we proceed to next problem.

Possibly we shall go back to the UNION problem, where we should start from with a very simple example. I suggest that we start from the below reduced statements, which are the original sqls posted by you some days ago:

SELECT c.ClientID, c.App1FirstName, c.App1LastName, u.FirstName, u.LastName, MONTHNAME(DatePaid) as MonthPaid, YEAR(DatePaid) as YearPaid, sum(p.amount) as sub_amt, DatePaid    
FROM (clients c JOIN users u on c.ClientUserID = u.UserID) JOIN buytolet p on c.ClientID = p.clients_ClientID     WHERE DatePaid IS not NULL     

UNION  SELECT c.ClientID, c.App1FirstName, c.App1LastName, u.FirstName, u.LastName, MONTHNAME(DatePaid) as MonthPaid, YEAR(DatePaid) as YearPaid, sum(p.amount) as sub_amt, DatePaid    
FROM (clients c JOIN users u on c.ClientUserID = u.UserID) JOIN mortgage p on c.ClientID = p.clients_ClientID     WHERE DatePaid IS not NULL

If this problem is already solved or become obsolete, you could suggest another small problem we can start from. In this case you should post the complete sql code.

Ok, …

...For doing the mathematical operation I need to extract d data from d table and STORE it in another variable in d C program....
my doubt was how to initialise a variable in a C program wid a data from a table...thts all...

Sorry, I didn't notice those questions earlier. Here is some C code showing you how to fetch specific rows from a table. I used prepare statement, binding methods and SQLExecute this time. You might do wee changes to fit this code to your current database.

void SlcProductTable(SQLHENV hEnv, SQLHDBC hDbc, SQLINTEGER quantity )
{
  SQLHSTMT hStmt;
  SQLRETURN	rc;
  SQLINTEGER pID, pQuantity; 
  SQLLEN c_len=SQL_NTS;
  SQLCHAR pName[80], pDate[20];
  SQLREAL SalesPrice; 

  printf("Rows of products having quantity < %d\n", quantity);

  // Statement handle 
  rc = SQLAllocHandle( SQL_HANDLE_STMT, hDbc, &hStmt);

  // Get pID, pName, date, quantity and salesPrice from product table
	SQLCHAR sqlStatement[] =
	  "SELECT pID, pName, convert(char(20), pDate, 104), quantity, salesprice FROM products "  
             "WHERE quantity  < ?;";

  // Prepare SQL statement 
  rc = SQLPrepare(hStmt, sqlStatement, SQL_NTS );

  // Bind host variables on columns coresponding to sql statement
  rc=SQLBindCol(hStmt, 1, SQL_C_SLONG, &pID, 0, NULL);
  rc=SQLBindCol(hStmt, 2, SQL_C_CHAR, pName, sizeof(pName), &c_len);
  rc=SQLBindCol(hStmt, 3, SQL_C_CHAR, pDate, sizeof(pDate), &c_len);
  rc=SQLBindCol(hStmt, 4, SQL_C_SLONG, &pQuantity, 0, NULL);
  rc=SQLBindCol(hStmt, 5, SQL_C_FLOAT, &SalesPrice, 0, NULL);

  // Bind host variable quantity on parameter (?) of where clause
  rc = SQLBindParameter( hStmt, 1, SQL_PARAM_INPUT, SQL_C_ULONG,
    SQL_INTEGER, 0, 0, &quantity, 0, NULL);

  // execute prepared statement
  rc = SQLExecute(hStmt);
 
  printf("PID   Product                            Date          Quantity  Salesprice\n"	     "---------------------------------------------------------------------------\n");

  // Fetch rows from …

seriously, this *.cpp file includes only the following line...

int main{ return 0; }

N.B. I am in Codeblocks and i have changed the settings of linker as tesuji suggested.

>>> int main{ return 0; } ???

So what about this one:

int main(){return 0;}

tesu

Sorry, this has been my mistake, I thought we were talking about mysql.
MS sql server has much more comprehensive system tables. You can get a good impression by downloading one of those great posters (such an originally colored poster of sql server 2008 is pinned on a wall in my office)

For example table sys.foreign_key_columns contains all foreign keys of a database..

-- tesu

Well, no doubt :-/

One should consider some drawbacks of datatype text over varchar, for example attributes of datatype text as well as blobs will be stored separately from all other attributes (they are not stored within contiguous pages!), also if text attributes appear together with other attributes in a select statement, the resultset won't never be hold in main memory completely but be stored on disk. There are further limitations if attributes of text datatype be compared, sorted, manipulated with string functions etc.

So, if one doesn't need to handle more then 64k characters, why should he be burden with such unfavorable stuff? Therefore, varchar datatype is the far more appropriate datatype for dealing with such short notes, there is really no doubt whatsoever ;)

-- tesu

Hi sanagopi

I've just answered to your posting dealing with hexadecimal number 0xff where at least left shift operator has been annotated.

right-shift operator: that is kind of division by 2, and it functions analogously to left shift. You could understand both operators if you read my posting there.

-- tesu

Sorry Adak, I have to say that your answer is completely wrong.

The hexadecimal number 0xff stands for its decimal equivalent f*16 + f = 15*16 + 15 = 255. Therefore, the decimal value of 0xff is 255!

The numerical operator << does left shift. Each left shift by one place (<< 1) is identical with multiplication by 2. Therefore, << 2 makes a left shift by two places which is identical with multiplication by 4 (2 times 2).

If we consider that the length of int usually is 32 bits or 8 bytes, the variable i contains 000000ff or in binary (msb) 0000 0000 0000 0000 0000 0000 1111 1111 (lsb). By left shifting this binary number by two places we get (msb) 0000 ... 0011 1111 1100 (lsb). What additionally happened during left shift is that on the msb side two bits were dropped off and on the lsb side 2 zero-bits were inserted.

Now the result is 0x3fc. Its decimal equivalent is (3*16+f)*16 + c = (3*16+15)*16+12 =1020, which could also be reproduced by stepwise decimal multiplication of 2 times 2: 255 times 2 is 510, 510 times 2 is 1020.

I hope that this small "tutorial" could clarify some misconceptions.

-- tesu

:( !!! ??? Are really all Egyptian people mad after speaking with a stammer by repeating those punctuation marks without any ceasing ??? !!!

-- tesu

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, 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,

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
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, 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 dmmckelv,

When Edgar Frank Codd invited relational databases his primary idea was to found it on
logic algebra and set theory. The rows of relational tables (aka relations) are sets. Therefore, there aren't duplicate rows nor any particular order of rows. From this point of view, "I am looking for something that would give me the speed field value for the row before" is rather irrelevant.

However, to put any meaningful signification on it, your table must already have a column
which allows such an ordering, for example date, timestamp or auto-increment numbers.
If you don't have such special column (or set of columns) within a table, it is impossible to determine the predecessor of a specific row because the today predecessor could be the successor row tomorrow (e.g. due to inserting and deleting rows in the meantime).

To cut a long story short, what's that special column of your table to get particularly ordered rows? You may also post the creating statement of your table.

-----
tesu