Complex Math functions

Hi there friends,

Can any of you please advice me as to how I am going to accomplish certain mathematical functions on numbers such as working out the n-th root of a number since the mathematical functions built in don't go past the square root (2nd root)?

Regards
<!--[if !msEquation]--> <!--[if !vml]--> <![endif]--><!--[if !vml]--><!--[endif]--><!--[endif]-->hi,
you can use the POWER function since the equivalent exponential function of an [n'th root of x] is [x power of 1/n]..
e.g.
if you want to get the 5th root of 100, use POWER(100, 1.0/5.0)

- clintz|||Thank you very much clintz.
|||you can also create user defined function (UDF) for other mathematical functions that you need.. like factorial, permutations, etc.|||It seems i have rejoiced to early:

if i do a simple query like select power(27,1/3),
the resulting value is 1 not 3. why is that?

Regards

|||

bcs 1/3 = 0 (INTEGER DIVISION)

So anything power 0 = 1

To overcome this use the following expression

Select Power(27.0,1.0/3.0)

|||lol of course, thanks so much once again
Regards

compile [really link] question

Hi,
I'm having trouble linking a simple example using the bcp functions...
The error:
************************************************
test.obj : error LNK2001: unresolved external symbol _bcp_init@.20
************************************************
Environment: Windows 2000, SQL Server 2000, VS C++ 6.0 Enterprise
I know my environment and connection handles are working...but my bcp_init()
call is driving me crazy...
Headers I'm including:
////////////////////////////////
//C HEADERS
#include <stdio.h>
#include <stdlib.h>
//WINDOWS
#include <windows.h>
//ODBC HEADERS
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
//BCP HEADER
#include <Odbcss.h>
////////////////////////////////
Library I'm including
Odbcbcp.lib
I use both
a) /libpath:"C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Lib"
and
b) LIB environmental variable points there too
I've checked the folder and the file is def. there!
Thinking I might not be using the ODBC 3.0 headers and lib...
I've check my LIB path and "Odbcbcp.lib" only exists once!
If I comment out the bcp_init() call, my program compiles and links fine
The call to bcp_exec() is FINE...
Anyone have a clue as to what I'm doing wrong?
Included below is my c file (test.c) pasted just in case I'm doing something
else wrong.
// START test.c
#include <stdio.h>
#include <stdlib.h>
#include <windows.h>
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
#include <Odbcss.h>
#define MAXBUFLEN 255
#define SQLSERVER1
#defineDSN2
int createHandles();
void destroyHandles();
SQLHENV henv = SQL_NULL_HENV;//Environment Handle
SQLHDBC hcn = SQL_NULL_HDBC;//Connection Handle
main()
{
intret;//internal return codes
RETCODEretcode;//ODBC return codes
UCHARszDSN[SQL_MAX_DSN_LENGTH+1] = "cnABL",//DSN info
szDSN_UID[MAXNAME] = "sa",
szDSN_PSWD[MAXNAME] = "";
SQLCHARconnectionStringIn[MAXBUFLEN] = "";//SQL SERVER driver
connection string
SQLCHAR connectionStringOut[MAXBUFLEN];//SQL SERVER driver
connection string out
SQLSMALLINT lengthOfConnectionStringOut = 0;
// Bulk copy variables.
SDWORD cRows;
intmyType = DSN;
//Create Handles
if ( ( ret = createHandles() ) )
{
destroyHandles();
returnEXIT_FAILURE;
}
switch (myType)
{
case SQLSERVER:
//Build up the ConnStrIn...
sprintf(connectionStringIn, "DRIVER={SQL
Server};SERVER=%s;UID=%s;PWD=%s;DATABASE=%s;", "", "sa", "", "abldb");
printf("\nCONNECTION STRING->%s<-", connectionStringIn);
//connect
retcode = SQLDriverConnect(hcn,// Connection handle
NULL,// Window handle
connectionStringIn,// Input connect string
SQL_NTS,// Null-terminated string
connectionStringOut,// Address of output buffer
MAXBUFLEN,// Size of output buffer
&lengthOfConnectionStringOut,// Address of output length
SQL_DRIVER_NOPROMPT);
//check it
if (retcode == SQL_ERROR)
//if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
// Connect failed, call SQLGetDiagRec for errors.
printf("\nSQL SERVER CONNECTION FAILED!");
destroyHandles();
return EXIT_FAILURE;
}
else
{
// Connects to SQL Server always return
// informational messages. These messages can be
// retrieved by calling SQLGetDiagRec.
printf("\nSQL SERVER CONNECTION SUCCESS!");
}
break;
case DSN:
//connect
retcode = SQLConnect(hcn, szDSN, (SWORD)strlen(szDSN), szDSN_UID,
(SWORD)strlen(szDSN_UID), szDSN_PSWD, (SWORD)strlen(szDSN_PSWD));
//check it
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
// Connect failed, call SQLGetDiagRec for errors.
printf("\nDSN CONNECTION FAILED!");
destroyHandles();
return EXIT_FAILURE;
}
else
{
// Connects to SQL Server always return
// informational messages. These messages can be
// retrieved by calling SQLGetDiagRec.
printf("\nDSN CONNECTION SUCCESS!");
}
break;
}
// Initialize the bulk copy.
//retcode = bcp_init(hcn, "abldb..trendtable", "c:\\ben\\BCPODBC.bcp",
"c:\\ben\\BCPERROR.out", DB_OUT);
// Note that the test is for the bulk copy return of SUCCEED,
// not the ODBC return of SQL_SUCCESS.
if ( (retcode != SUCCEED) )
{
printf("bcp_init(hcn) Failed\n\n");
destroyHandles();
return EXIT_FAILURE;
}
// Execute the bulk copy.
retcode = bcp_exec(hcn, &cRows);
if ( (retcode != SUCCEED) )
{
printf("bcp_exec(hcn) Failed\n\n");
destroyHandles();
return EXIT_FAILURE;
}
printf("Number of rows bulk copied out = %d.\n", cRows);
//clean up
destroyHandles();
return EXIT_SUCCESS;
}
int createHandles()
{
RETCODE retcode;
// Allocate the ODBC Environment and save handle.
retcode = SQLAllocHandle (SQL_HANDLE_ENV,
SQL_NULL_HANDLE,
&henv);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
// Notify ODBC that this is an ODBC 3.0 application.
retcode = SQLSetEnvAttr(henv,
SQL_ATTR_ODBC_VERSION,
(SQLPOINTER)SQL_OV_ODBC3,
SQL_IS_INTEGER);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
// Allocate an ODBC connection handle
retcode = SQLAllocHandle(SQL_HANDLE_DBC,
henv,
&hcn);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
return 1;
}
void destroyHandles()
{
SQLDisconnect(hcn);
SQLFreeHandle(SQL_HANDLE_DBC, hcn);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
// END test.c
thanks in advance,
georgejetson
Nevermind...
Although I had my environmental variables pointing to the more recent dirs
first, it was not working...I'm still learning ms vc dev environment...
FYI: To compile ODBC using BCP in VC6 Enterprise...
My Solution:
a) Downloaded latest and greatest mdac sdk
b) Added mdac lib & include dirs to LIB & INCLUDE environmental variable
list at front of list
c) For all configurations (Release/Active...) in VC6
1) Made sure all required libs existed
Odbc32.lib Odbccp32.lib Odbcbcp.lib
(probably don't need Odbccp32.lib)
2) Set Additional Library Path to FIRST use new mdac/lib folder, then
sqlserver/lib folder
ie:C:\mdacSdk28\Libs\x86,C:\Program Files\Microsoft SQL
Server\80\Tools\DevTools\Lib
d)Did the same thing for additional include directories
ie:C:\mdacSdk28\Inc,C:\Program Files\Microsoft SQL
Server\80\Tools\DevTools\Include
In my source code, I included the following...
//ALL ODBC CALLS NEED
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
//ODBC UNICODE
#include <Sqlucode.h>
//ODBC BCP
#include <Odbcss.h>
e) Finally, I took OUT the /nologo option so I could see where the compiler
and linker were really looking
the result below
Compiling...
Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 12.00.8168 for 80x86
Copyright (C) Microsoft Corp 1984-1998. All rights reserved.
cl /MLd /W3 /Gm /GX /ZI /Od /I "C:\mdacSdk28\Inc" /I "C:\Program
Files\Microsoft SQL Server\80\Tools\DevTools\Include" /D "WIN32" /D "_DEBUG"
/D "_CONSOLE" /D "_MBCS" /FR"Debug/" /Fp"Debug/prjOdbcTest.pch" /YX
/Fo"Debug/" /Fd"Debug/" /FD /GZ /c
"C:\sqlutil\odbcTest\test.c"
test.c
Note: Using precompiled header
Linking...
Creating browse info file...
prjOdbcTest.exe - 0 error(s), 0 warning(s)
thanks in advance,
georgejetson

compile [really link] question

Hi,
I'm having trouble linking a simple example using the bcp functions...
The error:
****************************************
********
test.obj : error LNK2001: unresolved external symbol _bcp_init@.20
****************************************
********
Environment: Windows 2000, SQL Server 2000, VS C++ 6.0 Enterprise
I know my environment and connection handles are working...but my bcp_init()
call is driving me crazy...
Headers I'm including:
////////////////////////////////
//C HEADERS
#include <stdio.h>
#include <stdlib.h>
//WINDOWS
#include <windows.h>
//ODBC HEADERS
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
//BCP HEADER
#include <Odbcss.h>
////////////////////////////////
Library I'm including
Odbcbcp.lib
I use both
a) /libpath:"C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Lib"
and
b) LIB environmental variable points there too
I've checked the folder and the file is def. there!
Thinking I might not be using the ODBC 3.0 headers and lib...
I've check my LIB path and "Odbcbcp.lib" only exists once!
If I comment out the bcp_init() call, my program compiles and links fine
The call to bcp_exec() is FINE...
Anyone have a clue as to what I'm doing wrong?
Included below is my c file (test.c) pasted just in case I'm doing something
else wrong.
// START test.c
#include <stdio.h>
#include <stdlib.h>
#include <windows.h>
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
#include <Odbcss.h>
#define MAXBUFLEN 255
#define SQLSERVER 1
#define DSN 2
int createHandles();
void destroyHandles();
SQLHENV henv = SQL_NULL_HENV; //Environment Handle
SQLHDBC hcn = SQL_NULL_HDBC; //Connection Handle
main()
{
int ret; // internal return codes
RETCODE retcode; // ODBC return codes
UCHAR szDSN[SQL_MAX_DSN_LENGTH+1] = "cnABL", //DSN info
szDSN_UID[MAXNAME] = "sa",
szDSN_PSWD[MAXNAME] = "";
SQLCHAR connectionStringIn[MAXBUFLEN] = ""; //SQL SERVER driver
connection string
SQLCHAR connectionStringOut[MAXBUFLEN]; //SQL SERVER driver
connection string out
SQLSMALLINT lengthOfConnectionStringOut = 0;
// Bulk copy variables.
SDWORD cRows;
int myType = DSN;
//Create Handles
if ( ( ret = createHandles() ) )
{
destroyHandles();
return EXIT_FAILURE;
}
switch (myType)
{
case SQLSERVER:
//Build up the ConnStrIn...
sprintf(connectionStringIn, "DRIVER={SQL
Server};SERVER=%s;UID=%s;PWD=%s;DATABASE
=%s;", "", "sa", "", "abldb");
printf("\nCONNECTION STRING->%s<-", connectionStringIn);
//connect
retcode = SQLDriverConnect( hcn, // Connection handle
NULL, // Window handle
connectionStringIn, // Input connect string
SQL_NTS, // Null-terminated string
connectionStringOut, // Address of output buffer
MAXBUFLEN, // Size of output buffer
&lengthOfConnectionStringOut, // Address of output length
SQL_DRIVER_NOPROMPT);
//check it
if (retcode == SQL_ERROR)
//if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
// Connect failed, call SQLGetDiagRec for errors.
printf("\nSQL SERVER CONNECTION FAILED!");
destroyHandles();
return EXIT_FAILURE;
}
else
{
// Connects to SQL Server always return
// informational messages. These messages can be
// retrieved by calling SQLGetDiagRec.
printf("\nSQL SERVER CONNECTION SUCCESS!");
}
break;
case DSN:
//connect
retcode = SQLConnect(hcn, szDSN, (SWORD)strlen(szDSN), szDSN_UID,
(SWORD)strlen(szDSN_UID), szDSN_PSWD, (SWORD)strlen(szDSN_PSWD));
//check it
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
// Connect failed, call SQLGetDiagRec for errors.
printf("\nDSN CONNECTION FAILED!");
destroyHandles();
return EXIT_FAILURE;
}
else
{
// Connects to SQL Server always return
// informational messages. These messages can be
// retrieved by calling SQLGetDiagRec.
printf("\nDSN CONNECTION SUCCESS!");
}
break;
}
// Initialize the bulk copy.
//retcode = bcp_init(hcn, "abldb..trendtable", "c:\\ben\\BCPODBC.bcp",
"c:\\ben\\BCPERROR.out", DB_OUT);
// Note that the test is for the bulk copy return of SUCCEED,
// not the ODBC return of SQL_SUCCESS.
if ( (retcode != SUCCEED) )
{
printf("bcp_init(hcn) Failed\n\n");
destroyHandles();
return EXIT_FAILURE;
}
// Execute the bulk copy.
retcode = bcp_exec(hcn, &cRows);
if ( (retcode != SUCCEED) )
{
printf("bcp_exec(hcn) Failed\n\n");
destroyHandles();
return EXIT_FAILURE;
}
printf("Number of rows bulk copied out = %d.\n", cRows);
//clean up
destroyHandles();
return EXIT_SUCCESS;
}
int createHandles()
{
RETCODE retcode;
// Allocate the ODBC Environment and save handle.
retcode = SQLAllocHandle ( SQL_HANDLE_ENV,
SQL_NULL_HANDLE,
&henv);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
// Notify ODBC that this is an ODBC 3.0 application.
retcode = SQLSetEnvAttr( henv,
SQL_ATTR_ODBC_VERSION,
(SQLPOINTER)SQL_OV_ODBC3,
SQL_IS_INTEGER);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
// Allocate an ODBC connection handle
retcode = SQLAllocHandle( SQL_HANDLE_DBC,
henv,
&hcn);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
return 1;
}
void destroyHandles()
{
SQLDisconnect(hcn);
SQLFreeHandle(SQL_HANDLE_DBC, hcn);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
// END test.c
thanks in advance,
georgejetsonNevermind...
Although I had my environmental variables pointing to the more recent dirs
first, it was not working...I'm still learning ms vc dev environment...
FYI: To compile ODBC using BCP in VC6 Enterprise...
My Solution:
a) Downloaded latest and greatest mdac sdk
b) Added mdac lib & include dirs to LIB & INCLUDE environmental variable
list at front of list
c) For all configurations (Release/Active...) in VC6
1) Made sure all required libs existed
Odbc32.lib Odbccp32.lib Odbcbcp.lib
(probably don't need Odbccp32.lib)
2) Set Additional Library Path to FIRST use new mdac/lib folder, then
sqlserver/lib folder
ie:C:\mdacSdk28\Libs\x86,C:\Program Files\Microsoft SQL
Server\80\Tools\DevTools\Lib
d)Did the same thing for additional include directories
ie:C:\mdacSdk28\Inc,C:\Program Files\Microsoft SQL
Server\80\Tools\DevTools\Include
In my source code, I included the following...
//ALL ODBC CALLS NEED
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
//ODBC UNICODE
#include <Sqlucode.h>
//ODBC BCP
#include <Odbcss.h>
e) Finally, I took OUT the /nologo option so I could see where the compiler
and linker were really looking
the result below
--
Compiling...
Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 12.00.8168 for 80x86
Copyright (C) Microsoft Corp 1984-1998. All rights reserved.
cl /MLd /W3 /Gm /GX /ZI /Od /I "C:\mdacSdk28\Inc" /I "C:\Program
Files\Microsoft SQL Server\80\Tools\DevTools\Include" /D "WIN32" /D "_DEBUG"
/D "_CONSOLE" /D "_MBCS" /FR"Debug/" /Fp"Debug/prjOdbcTest.pch" /YX
/Fo"Debug/" /Fd"Debug/" /FD /GZ /c
"C:\sqlutil\odbcTest\test.c"
test.c
Note: Using precompiled header
Linking...
Creating browse info file...
prjOdbcTest.exe - 0 error(s), 0 warning(s)
--
thanks in advance,
georgejetson

compatibility level

I recently upgraded my database from 6.5 to 2000. To take
advantge of user defined functions I changed the
compatibility level from 65 to 80. the following query in
my code now returns null if the middle name is null. Con
someone explain wht this is happening and how I can fix?
ThanksSorry here is the query
select Client_Name = rtrim(p.Last_Name) +
Case p.Mid_Name
WHEN null then ' '
WHEN ' ' then ' '
Else ' ' + p.Mid_Name + ' '
End
+ ltrim(p.First_Name)
FROM Client_Info c, Persons p
WHERE c.Client_no = '0004530184'
and c.person_id = p.Person_id
>--Original Message--
>I recently upgraded my database from 6.5 to 2000. To
take
>advantge of user defined functions I changed the
>compatibility level from 65 to 80. the following query
in
>my code now returns null if the middle name is null. Con
>someone explain wht this is happening and how I can fix?
>Thanks
>.
>|||In what query? If I were to guess, most likely you are using string
concatenation to build a name. Concatenate null yields null is off by
default as this is ANSI standard. You can find more info in books
online if you look up CONCAT_NULL_YIELDS_NULL
You can change your query to handle the null values in building the
string - if that's what the issue is.
-Sue
On Mon, 13 Sep 2004 11:32:23 -0700, "steve"
<anonymous@.discussions.microsoft.com> wrote:
>I recently upgraded my database from 6.5 to 2000. To take
>advantge of user defined functions I changed the
>compatibility level from 65 to 80. the following query in
>my code now returns null if the middle name is null. Con
>someone explain wht this is happening and how I can fix?
>Thanks|||As Sue stated in her post, your query will be affected by the
CONCAT_NULL_YIELDS_NULL setting. To avoid that dependency, or write the
query better overall, you would use
select Client_Name = rtrim(p.Last_Name) +
isnull(p.Mid_Name,' ') + ltrim(p.First_Name)
FROM Client_Info c, Persons p
WHERE c.Client_no = '0004530184'
and c.person_id = p.Person_id
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:173f01c499c1$0b721420$a401280a@.phx.gbl...
> Sorry here is the query
> select Client_Name = rtrim(p.Last_Name) +
> Case p.Mid_Name
> WHEN null then ' '
> WHEN ' ' then ' '
> Else ' ' + p.Mid_Name + ' '
> End
> + ltrim(p.First_Name)
> FROM Client_Info c, Persons p
> WHERE c.Client_no = '0004530184'
> and c.person_id = p.Person_id
> >--Original Message--
> >I recently upgraded my database from 6.5 to 2000. To
> take
> >advantge of user defined functions I changed the
> >compatibility level from 65 to 80. the following query
> in
> >my code now returns null if the middle name is null. Con
> >someone explain wht this is happening and how I can fix?
> >
> >Thanks
> >.
> >

compatibility level

I recently upgraded my database from 6.5 to 2000. To take
advantge of user defined functions I changed the
compatibility level from 65 to 80. the following query in
my code now returns null if the middle name is null. Con
someone explain wht this is happening and how I can fix?
Thanks
In what query? If I were to guess, most likely you are using string
concatenation to build a name. Concatenate null yields null is off by
default as this is ANSI standard. You can find more info in books
online if you look up CONCAT_NULL_YIELDS_NULL
You can change your query to handle the null values in building the
string - if that's what the issue is.
-Sue
On Mon, 13 Sep 2004 11:32:23 -0700, "steve"
<anonymous@.discussions.microsoft.com> wrote:

>I recently upgraded my database from 6.5 to 2000. To take
>advantge of user defined functions I changed the
>compatibility level from 65 to 80. the following query in
>my code now returns null if the middle name is null. Con
>someone explain wht this is happening and how I can fix?
>Thanks

comparision of sql server & mysql

whats the difference betwwen mysql and sql server2000 in terms of using
storedprocedures,stored functions and views.> whats the difference betwwen mysql and sql server2000 in terms of using
> storedprocedures,stored functions and views.

What version of MySQL?

--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com|||MYSql 5.0|||> MYSql 5.0

Well, both MySQL 5 and SQL Server 2000 have views/procedures/functions.

SQL Server 2000 has Check Constraints, which MySQL doesn't.

From SQL Server 2000, it's quite easy to extend the server with
stuff that's accessible from TSQL (like COM objects and so on)
in a programming language that supports it.

MySQL uses the standard PSM (Persistent Stored Module) Syntax for
it's procedures and functions, while SQL Server uses T-SQL, a Microsoft
extension to standard SQL.

Is there anything specific that you want to know?

--
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com