|
My web hotel has upgraded from MySQL 4.1.21 to MySQL 5.0.37. The only problem I have had with this is that a query doesn't return anything in the result any more:
select sum(i.FileSize) as TotalSize<br />
from Photos p<br />
inner join Images i on i.PhotoId=p.PhotoId and i.IsPublic=1<br />
where p.IsPublicArt=1 or p.IsPublicCommon=1
If I change "sum" to "max" or "count" the query return one row, with "sum" or "avg" it returns zero rows.
FileSize is int and can not be null.
If I test the query directly in the database (using phpmyadmin) it returns one row with all the aggregates.
My connection string looks like this (with a different uid and pwd of course): "driver={MySQL};server=mysql.loopia.se;uid=asdfasdf;pwd=asdfasdf;database=voidstation_com"
I have tried to add ";option=3" to the connection string, but that didn't make any difference.
The list of changes that the web hotel linked to:
http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html[^]
I don't see anything there that should affect this.
Anybody got any ideas?
---
single minded; short sighted; long gone;
|
|
|
|
|
Guffa wrote: If I change "sum" to "max" or "count" the query return one row, with "sum" or "avg" it returns zero rows.
I do not know about MySql.
But see what you can do with this hint:
sum will return a large value than count or max
Regards,
Arun Kumar.A
|
|
|
|
|
Thanks for your reply.
When I use sum and run the query in phpmyadmin, the exact value that it returns is 609538140. It's larger than what count and max returns, but it's still way within the bounds of an int.
Anyway, it's not a problem with reading the value from the row, as the query doesn't even return a row. The result is empty. It doesn't contain any row to read from.
---
single minded; short sighted; long gone;
|
|
|
|
|
When I changed the driver used for the connection from version 2.50 to 3.51, it started working again.
---
single minded; short sighted; long gone;
|
|
|
|
|
I am using
Reporting Services 2005 Express
SQl Express
Visual Studio 2005
The project is in asp.net.
I have .jpg signature images that are stored in an directory image folder that I need to dynamically add to my reports based on the parameter of the person's username that is stored in a User's Table. What do I need to do to add these images dynamically? If possible.
|
|
|
|
|
hello to al,
i am using the SUM(),
but
for eg if i give SUM(PENCOST) means it gives then sum of cost of the pens
but the table name also displays as SUM of PENCOST instead of PENCOST,,
so what should and how should i give the code.......????
self confidence+hard work=SUCCESS
|
|
|
|
|
give Sum(PenCost) as PenCost
|
|
|
|
|
HEY UU ITS WORKING YAAR,
THANK U VERY MUCH REVATHI,,
THANKS A LOT
self confidence+hard work=SUCCESS
|
|
|
|
|
some times we hv to give Sum(COST) COST (we hv to give spae instead of 'as')
self confidence+hard work=SUCCESS
|
|
|
|
|
hi guys ,,,
actually i am very much new to oracle, i just want to
ask that cam we use sql joins in oracle
if not than how can we get data from different tables
based on a particular chriteria,,,
thanks in advance
hello
|
|
|
|
|
Yes.
for fetching data from more than one table, Joins can be used in Oracle.
Regards
KP
|
|
|
|
|
Hi All,
I need a URL from where I can download sample stored procedures, with which I can learn different techniques to write stored procedures. We have 101 Sample C# codes available to download in MSDN site, I am looking for similar kind of with respect to stored procedures.
Can any body help me….
Thanks in advance
Regards,
Krishnaraj
|
|
|
|
|
|
hello krishna, can u tell how to get that 101 sample c# codes from MSDN site, can u give me the link?
self confidence+hard work=SUCCESS
|
|
|
|
|
Please make me clear that
will INT(4) and VARCHAR(4) will consume same storage space in SQL Server 2000 ?
|
|
|
|
|
Navaneeth. wrote: will INT(4) and VARCHAR(4) will consume same storage space in SQL Server 2000 ?
No.
For More Details Here[^]
Regards,
Satips.
|
|
|
|
|
How to Customized SQL Report Viewer Control ? e.g The Export Combo Should only convert the Report to PDF and User Cannot Convert it to another Format
Plz Help me!
Ali
|
|
|
|
|
Hi there,
anyone using Pervasive SQL 8.0??
I have an issue with transactions using C# on Pervasive SQL 8.0. The transaction does not wait when there is a lock (another transaction operating) on the table.
It just throws an exception.
Thanks for helping
|
|
|
|
|
What Exception it throws?
Regards,
Satips.
|
|
|
|
|
A btrieve error 85 is thrown, which means the file is locked. Right now, my solution to this problem conbsists of looping through a try-catch block and keep trying until the file is unlocked. I was just wondering whether there is a more elegant solution to that..
bool bSuccess = false;
int nCount = 0;
while (!bSuccess && nCount < 30)
{
try
{
da.Update(dsControl, "Control"); // this throws the exception if
// there is a lock on Control
bSuccess = true;
}
catch (Exception ex)
{
nCount++;
}
}
I suspect something wrong with Pervasive SQL 8.0.. :$
|
|
|
|
|
Hi.
I have a SSIS package named TESTPACKAGE on the 2005 SQL Server.
How I can exeucte it from VB.NET?
I found some articels that refer to Microsoft.SQLServer assembly but I use older .NET Framework version (1.1) and this assembly is not availabe for me in Visual Studio (2003).
I read something about DTEXEC command. Can I use it in the Stored Procedure to run SSIS. I used this code in the Stored Procedure to run DTS package on the previsous/older SQL Server version.
<br />
CREATE PROCEDURE dbo.exportData AS<br />
Exec master..xp_cmdshell <br />
'DTSRUN /SMYSERVER /NTESTDTS /Umyaccess /Pmypassword'<br />
GO
Thank you,
|
|
|
|
|
Hope this will Help you.
DECLARE @cmd VARCHAR(255)
DECLARE @Wait INT
DECLARE @result INT, @OLEResult INT
DECLARE @ShellID INT
SET @Wait = 0
SET @cmd = 'DTEXEC /sq "TestSSPSPackageName" /ser /Set \Package.Variables[User::varTargetName].Properties[Value];"c:\dtsxTest\Test2.txt"'
-- create instance of OLE object
EXECUTE @OLEResult = sp_OACreate 'WScript.Shell', @ShellID OUT
IF @OLEResult <> 0 SELECT @result = @OLEResult
IF @OLEResult <> 0 RAISERROR ('CreateObject %0X', 14, 1, @OLEResult)
-- run package
EXECUTE @OLEResult = sp_OAMethod @ShellID, 'Run', NULL, @cmd, 0, @Wait
IF @OLEResult <> 0 SELECT @result = @OLEResult
IF @OLEResult <> 0 RAISERROR ('Run %0X', 14, 1, @OLEResult)
If @OLEResult <> 0 EXEC sp_OAGetErrorInfo @ShellID, @OLEResult
-- drop object
EXECUTE @OLEResult = sp_OADestroy @ShellID
-- I think this will run an an asynchronous process.
Regards,
Satips.
|
|
|
|
|
I am probabely be called an idiot rom now on but I can't figure this out, tried everything. Here I have to trasfer in SQL a very disfunctionnal FMP database. This is the function that verifies the dates. The problem I have is this:
It works perfectly when the original date (formatted 01/01/1800) is correct but the last part should overwrite any malformed date (anyways that was the intention....) but none get overwritten. I always get the malformed date back. It is as if @DateTest never gets put to False. Help!!
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetBirthDate]
(
@BirthDate nvarchar(10)
)
RETURNS nvarchar(10)
AS
BEGIN
DECLARE @ReturnValue nvarchar(10)
DECLARE @DateTest bit
SET @DateTest = 'True'
IF (@BirthDate = '00/00/00' OR @BirthDate = '' OR @BirthDate = '?' OR @BirthDate IS NULL)
SET @ReturnValue = '1800-01-01'
ELSE
BEGIN
IF LEN(@BirthDate) = 10
SET @ReturnValue = SUBSTRING(@BirthDate, 7, 4) + '-' + SUBSTRING(@BirthDate, 4, 2) + '-' + LEFT(@BirthDate, 2)
ELSE
SET @ReturnValue = '19' + SUBSTRING(@BirthDate, 7, 2) + '-' + SUBSTRING(@BirthDate, 4, 2) + '-' + LEFT(@BirthDate, 2)
END
IF (SUBSTRING(@ReturnValue, 1, 2) <> '19' AND SUBSTRING(@ReturnValue, 1, 2) <> '20') SET @DateTest = 'False'
IF (SUBSTRING(@ReturnValue, 3, 1) < '0' OR SUBSTRING(@ReturnValue, 3, 2) > '9') SET @DateTest = 'False'
IF (SUBSTRING(@ReturnValue, 4, 1) < '0' OR SUBSTRING(@ReturnValue, 4, 2) > '9') SET @DateTest = 'False'
IF (SUBSTRING(@ReturnValue, 6, 1) < '0' OR SUBSTRING(@ReturnValue, 6, 2) > '1') SET @DateTest = 'False'
IF (SUBSTRING(@ReturnValue, 7, 1) < '0' OR SUBSTRING(@ReturnValue, 7, 2) > '9') SET @DateTest = 'False'
IF (SUBSTRING(@ReturnValue, 9, 1) < '0' OR SUBSTRING(@ReturnValue, 9, 2) > '3') SET @DateTest = 'False'
IF (SUBSTRING(@ReturnValue, 10, 1) < '0' OR SUBSTRING(@ReturnValue, 10, 2) > '9') SET @DateTest = 'False'
IF (SUBSTRING(@ReturnValue, 5, 1) <> '-') SET @DateTest = 'False'
IF (SUBSTRING(@ReturnValue, 8, 1) <> '-') SET @DateTest = 'False'
IF @DateTest = 'False' SET @ReturnValue = '1800-01-01'
RETURN @ReturnValue
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
|
|
|
|
|
I had to answer to this one myself, this is too funny. My stored procedure was calling another function I wrote that had a similar name
It was a pleasure wasting everybody's time ! ;P
|
|
|
|
|
Hi,
I am using Enterprise Library April 2007, and wanted to know what DbType is a uniqueidentifier data type?? Like when I have for example:
db.AddInParameter(command, "@FirstName", DbType.String, info.FirstName); // Required
How would I do it for a uniqueidentifier data type?
Please can someone help me?
Regards
ma se
|
|
|
|
|