Joined: 7/8/2017
DEPARTMENT IN (SELECT SUBSTRING(name, 1, CHARINDEX('-', name, 1) - 1) AS Department FROM dwsystem.dbo.dwgroup
WHERE gid IN (SELECT gid FROM dwsystem.dbo.DWUserToGroup WHERE uid IN (SELECT uid FROM dwsystem.dbo.dwuser WHERE name = 'bsikora'))
AND CHARINDEX('-', name, 1) > 0)
SELECT name FROM dwsystem.dbo.dwgroup
SELECT Name AS GroupName FROM dwsystem.dbo.dwgroup
WHERE gid IN (SELECT gid FROM dwsystem.dbo.DWUserToGroup WHERE uid IN (SELECT uid FROM dwsystem.dbo.dwuser WHERE name = 'bsikora'))
Daryl,
The problem here is that if any group name doesn't have a colon in it, the SUBSTRING() command is trying to use a negative number when CHARINDEX() comes up with zero. CHARINDEX() will return zero if it doesn't find the character you are looking for.
So, what are the names of your groups? Find this out by performing the query:
SELECT name FROM dwsystem.dbo.dwgroup
I don't have any group names with colons, but all my group names have spaces in them. If I change the colon in your query to a space, the query succeeds.
In the same way you keep "Public" out (ostensibly because it doesn't have a colon), you need to keep out any group name lacking a colon via another condition in the WHERE clause:
AND CHARINDEX(':', name, 1) > 0
In fact, since that will inherently keep out "Public" as well, you can just use that for a final query of:
DEPARTMENT IN (SELECT SUBSTRING(name, 1, CHARINDEX(' ', name, 1) - 1) AS Department FROM dwsystem.dbo.dwgroup
WHERE gid IN (SELECT gid FROM dwsystem.dbo.DWUserToGroup WHERE uid IN (SELECT uid FROM dwsystem.dbo.dwuser WHERE name = CURRENTUSERLONGNAME()))
AND CHARINDEX(':', name, 1) > 0)
As I said, I get no records back (because I have no groups with colons in the name), but at least the query does not error out on SQL Server.
Good luck!
Joe Kaufman
Edward,
Some other async examples make the operation become synchronous by asking for the Result of the operation. Consider the example of downloading a document, found here:
https://developer.docuware.com/dotNet_CodeExamples/4354370d-a0ce-42d4-ba94-6dc848ed62c0.html
The main call is an async method, but the Result is summoned, making the program wait:
var downloadResponse = document.PostToFileDownloadRelationForStreamAsync(
new FileDownload()
{
TargetFileType = FileDownloadType.Auto
}).Result;
This gives you back the stream immediately for download.
I am not sure other async routines are written in such a way that this will always work, and async stuff makes my head hurt.
A really quick and dirty way to know if the file is done downloading would be to wait for the file to exist, and then try to lock it for exclusive write. That shouldn't be allowed while the file is still downloading. I have a WaitForFile routine I use for that:
/// <summary>
/// Like the global VFP function of the same name, this routine waits for a file to appear and not be in use elsewhere (via exclusive write check)
/// so that the file is known to be available for use and no longer being filled or operated on by any other process.
/// </summary>
/// <param name="fileName">The file name to wait for.</param>
/// <param name="timeout">Milliseconds to wait for the file (forever, by default).</param>
/// <param name="silent">If true, do not display any information to use, not even if timeout is hit.</param>
/// <param name="prePauseSeconds">A time to pause (in seconds) before checking for the file, to give the file time to appear regardless of timeout.</param>
/// <returns>Returns TRUE if file is found and free of other use within timeout period, otherwise FALSE.</returns>
public static bool WaitForFile(string fileName, int timeout = Int32.MaxValue, bool silent = false, int prePauseSeconds = 0)
{
bool fileFound = false;
int numLoops = 0;
if (fileName.Length == 0) { return false; }
if (timeout <= 0)
{
// A timeout has been passed in but is zero or less. Assume this is meant to be an infinite wait.
timeout = Int32.MaxValue;
}
if (prePauseSeconds > 0)
{
System.Threading.Thread.Sleep(prePauseSeconds * 1000);
}
// We can now wait for the file to appear.
while ((!fileFound) && (numLoops <= timeout))
{
if (File.Exists(fileName))
{
fileFound = true;
}
else
{
// Wait one second before trying again...
System.Threading.Thread.Sleep(1000);
Application.DoEvents();
if ((numLoops >= 5) && (!silent))
{
UIFuncs.StartWait("Waiting for file " + fileName + " " + numLoops.ToString() + " times...");
}
numLoops++;
}
}
// If file not found, then we have timed out.
if (!fileFound)
{
if (!silent) { UIFuncs.StopWait(); }
return false;
}
// If we make it here, we have file, but it might still be growing or in use.
// Wait until nothing else has it in use.
numLoops = 0;
while (FileInUse(fileName) && (numLoops <= timeout))
{
// Wait one second before trying again...
System.Threading.Thread.Sleep(1000);
Application.DoEvents();
if ((numLoops >= 5) && (!silent))
{
UIFuncs.StartWait("Waiting for file " + fileName + " " + numLoops.ToString() + " times...");
}
numLoops++;
}
if (!silent) { UIFuncs.StopWait(); }
return (numLoops <= timeout);
}
Never mind the "StartWait" stuff, that is just a popup display I developed to mimic FoxPro's WAIT WINDOW functionality. The above routine calls the method FileInUse, which looks like this:
/// <summary>
/// Check to se if a file is in use by trying to open it with exlusive write access (that lock is removed if access is granted).
/// </summary>
/// <param name="fileName">The file name to check for being in use.</param>
/// <returns>Returns TRUE if the file is in use elsewhere, FALSE otherwise.</returns>
public static bool FileInUse(string fileName)
{
bool inUse = true;
FileStream stream = null;
try
{
FileInfo fi = new FileInfo(fileName);
stream = fi.Open(FileMode.Open, FileAccess.ReadWrite, FileShare.None);
inUse = false;
}
catch (IOException)
{
// File is in use by another process.
inUse = true;
}
finally
{
if (stream != null) { stream.Close(); }
}
return inUse;
}
Maybe that would work for you, assuming you are doing this in C#/.NET, of course.
Thanks,
Joe Kaufman
public static Document UploadFileToFileCabinet(FileCabinet fileCabinet, string uploadFile, Document indexInfo = null)
{
LastError = "";
try
{
// We will use a standard upload method for smaller files, but the "Easy" version for larger files, otherwise
// large file uploads might fail (the "Easy" version is meant for huge file uploads, according to the documentation).
int largeFileThresholdInBytes = 10 * 1024 * 1024; // 10 MB
Document uploadedDoc = null;
FileInfo fileInfo = new FileInfo(uploadFile);
if (fileInfo.Length < largeFileThresholdInBytes)
{
// Smaller file.
uploadedDoc = fileCabinet.UploadDocument(indexInfo, fileInfo);
}
else
{
// Larger file.
uploadedDoc = fileCabinet.EasyUploadSingleDocument(fileInfo, indexInfo);
}
return uploadedDoc;
}
catch (Exception ex)
{
LastError = ex.Message;
return null;
}
}
<?xml version="1.0" encoding="utf-8"?>
<DialogExpression xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Operation="And" xmlns="http://dev.docuware.com/schema/public/services/platform">
<Condition DBName="SHIP_DATE">
<Value>2019-01-01</Value>
<Value>2019-12-31</Value>
</Condition>
</DialogExpression>
This is a query on a field called "SHIP_DATE", and would get everything where ship date was in 2019.<?xml version="1.0" encoding="utf-8"?>
<DialogExpression xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Operation="And" xmlns="http://dev.docuware.com/schema/public/services/platform">
<Condition DBName="DWSTOREDATETIME">
<Value>2020-01-03T00:00:00</Value>
<Value>2020-01-03T23:59:59</Value>
</Condition>
</DialogExpression>