|
Isn't it possible to do it as web service?
Mazy
"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down
|
|
|
|
|
Yes, I'm sorry, but I found a good article on this subject in the VS.Net Walktroughs ("Creating a distributed application"). It uses SQL Server, but that's not really a big prob .
THX, Nicolas
|
|
|
|
|
Is it possible to include spaces when you create a table
sSql="CREATE TABLE Graph (Date TEXT, Hour NUMBER,Temp_ºC NUMBER)";
For instance: Temp ºC instead of Temp_ºC
Thanks
|
|
|
|
|
José Luis Sogorb wrote:
sSql="CREATE TABLE Graph (Date TEXT, Hour NUMBER,Temp_ºC NUMBER)";
For instance: Temp ºC instead of Temp_ºC
Yes, enclose the "invalid" table name with [ and ] e.g.
sSql="CREATE TABLE Graph (Date TEXT, Hour NUMBER,[Temp ºC] NUMBER)";
Just also remember when you reference that table to put the [ and ] in. (You can use this technique for reserved table names as well, e.g. Order must be used as [Order].
Paul Watson Bluegrass Cape Town, South Africa
Ray Cassick wrote:
Well I am not female, not gay and I am not Paul Watson
|
|
|
|
|
Hello,
i have a problem that I am sure anyone who writes database Apps has come across. i am looking for the most elegant solution usnig ADO and an access MDB file, via C++
I have been working with ADO for quite a while now, but I am still unsure of the best way to do this.
I have a table which stores values from an email: TO, FROM and SUBJECT.
The TO field could contain multiple email addresses.
Currently I am storing them in a string, seperating each address with a comma.
This method works, but has these (and other) drawbacks:
1) When storing a list of emails, I must first format a string, sperating the emails with a comma
2) When Retrieving the list, I need to read the entire field and parse out the results into an internal list
3) SQL queries, for example, to check if an email exists get a bit complicated.
So, the question is, what alternative methods do you DB experts recommend?
As usual, thanks in advance,
Jeremy.
Jeremy Pullicino
Professional C++ Developer
Done any hacking lately?
|
|
|
|
|
I think it is better to make new table for emails and make relation with other tables.
Mazy
"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down
|
|
|
|
|
Create a separate table for the TO entries. It would need just a primary key and the TO value. Each row would contain one addressee. Change the TO field in your original table to be a foreign key that points to the new TO table.
|
|
|
|
|
Has anybody used the .NET data provider for Oracle ?
How did you find it ?
Johan Lombaard
Only two things are infinite, the universe and human stupidity, and I'm not sure about the former - Albert Einstein
|
|
|
|
|
Hi Johan,
Microsoft has released Oracle .NET Data Provider just recently. I have not tried them yet, but with some disgusting experience with OleDB Provider from IBMDADB2 driver and System.Data.OleDB, I have tried moving it to Microsoft.Data.ODBC. The transition was smooth and only one file needed to change.
Perhaps you can try Microsoft.Data.Odbc since it has much stable implementation perhaps.
deepak
|
|
|
|
|
Thanks deepak.
Since I posted my message, I've implemented the Oracle Data Provider in a data import app.
It works like a charm, and is 4X faster than the oledb provider in some cases.
Johan Lombaard
Only two things are infinite, the universe and human stupidity, and I'm not sure about the former - Albert Einstein
|
|
|
|
|
Is there any way to do a multiple inserts in a stored procedure in SQL2000? Please help. Thanks.
|
|
|
|
|
RJS wrote:
Is there any way to do a multiple inserts in a stored procedure in SQL2000?
You could write a while loop inside the stored procedure, there are many ways of doing something like this.
Nick Parker
May your glass be ever full.
May the roof over your head be always strong.
And may you be in heaven half an hour before the devil knows you’re dead. - Irish Blessing
|
|
|
|
|
Can you give more details?
Are these just INSERTS into multiple tables?
Are the INSERTS dependent on each other?
Cheers,
Simon
"Sign up for a chance to be among the first to experience the wrath of the gods.", Microsoft's home page (24/06/2002)
|
|
|
|
|
Simon,
thanks for the reply.
The scenario is like this:
I have (let's say) 3 records or more in my .net page. Example:
"John", "123 Anywhere", "City1"
"Rudy", "345 Anywhere", "City2"
"Tom", "335 Anywhere", "City2"
...
...
Is it possible to do a multiple inserts in a stored procedure to save those 3 records or more in a table? If so, how do I pass the parameters since I will not know what is the maximum records that will be sent at once? Thanks.
|
|
|
|
|
What you would like to do is the basically pass a recordset to a stored procedure in SQL Server 2000. (?)
The only way I know of to do this is to create an Xml file, pass it as a text (ntext in .Net ) parameter to the stored procedure. And then use the OPENXML SQL statement, which enables you to treat an XML file as a table (check OPENXML in Books Online).
You can then easily use something like
INSERT INTO MyTable (val1, val2, val3)
SELECT val1, val2, val3
FROM OPENXML(...)
It very performant and works like a charm!
Hope this helps,
Morty
|
|
|
|
|
That's one way, Morten.
Another, which is very dependent on the type of data you're passing through, could pass a delimited list (a big string) to the stored proc and use some of the following code:
<br />
DECLARE @Array varchar(50)<br />
DECLARE @iStart int<br />
DECLARE @iEleFound int<br />
DECLARE @iMaxEleLen int<br />
DECLARE @iNextDelimIndex int<br />
DECLARE @iEleLen int<br />
<br />
SET @Array = '1,2,3,4,5,6,7,8,9,65,66,67,100,101,102,654321'<br />
SET @iStart = 0<br />
SET @iEleFound = 0<br />
SET @iMaxEleLen = 5<br />
SET @iNextDelimIndex=0<br />
SET @iEleLen =0<br />
<br />
print '---------'<br />
print 'LENGTH of string: ' + CAST(LEN(@Array) AS VARCHAR)<br />
while CHARINDEX ( ',' , @Array,@iStart ) > 0 -- for each element in the array<br />
begin<br />
SET @iEleLen =1 --default <br />
--if not ',' or ''<br />
if (CAST(SUBSTRING(@Array, @iStart + 1, 1) AS char(1)) <> ',') and not(len (SUBSTRING (@Array, @iStart + 1, 1)) = 0)<br />
begin--element found at this index<br />
<br />
SET @iNextDelimIndex = CHARINDEX ( ',' , @Array,@iStart+1) <br />
if(@iNextDelimIndex=0)<br />
begin<br />
SET @iNextDelimIndex = CAST (LEN(@Array) AS VARCHAR)+1<br />
print 'latest element is next:'<br />
end <br />
SET @iEleLen = @iNextDelimIndex - @iStart<br />
--DB function would occur here<br />
print 'NEXT DELIM LOCATION-->' + CAST(@iNextDelimIndex AS VARCHAR(10))<br />
print 'VALUE FOUND-->' + RTRIM( CAST(SUBSTRING(@Array, @iStart + 1, @iNextDelimIndex - @iStart-1) AS char(10)) ) + '<--'<br />
<br />
<br />
SET @iEleFound = @iEleFound + 1 <br />
<br />
<br />
end<br />
<br />
else --no element found at this index<br />
begin<br />
print 'do nothing'<br />
end<br />
<br />
SET @iStart = @iStart + @iEleLen<br />
<br />
end --if (CAST(SUBSTRING(@Array, @iStart + 1, 1) AS char(1)) <> ',') and not(len (SUBSTRING (@Array, @iStart + 1, 1)) = 0)<br />
<br />
print '------ ELEMENTS FOUND: ' + CAST(@iEleFound AS CHAR(2)) + ' ---------'<br />
Note: not fully tested, but should give you a starting point.
Cheers,
Simon
"Sign up for a chance to be among the first to experience the wrath of the gods.", Microsoft's home page (24/06/2002)
|
|
|
|
|
This is a followup to my previous question in the VisualC++ forum[^]. As no one was able to help me there (so far) and I have traced my problem to a failed SQLGetCursorName() call, I decided that this may be a better forum for my question.
I traced my problem into the CRecordSet::BuildUpdateSQL() function (Line 3394 of dbcore.cpp)
if (m_strCursorName.IsEmpty())
{
RETCODE nRetCode;
TCHAR szCursorName[MAX_CURSOR_NAME+1];
SWORD nLength = _countof(szCursorName)-1;
AFX_SQL_SYNC(::SQLGetCursorName(m_hstmt,
reinterpret_cast<SQLTCHAR *>(szCursorName), _countof(szCursorName), &nLength));
if (!Check(nRetCode))
ThrowDBException(nRetCode);
m_strCursorName = szCursorName;
}
m_strUpdateSQL += m_strCursorName;
Now according to MSDN[^]:
<quote>
Cursor names are used only in positioned update and delete statements (for example, UPDATE table-name ...WHERE CURRENT OF cursor-name). For more information, see "Positioned Update and Delete Statements" in Chapter 12: Updating Data. If the application does not call SQLSetCursorName to define a cursor name, the driver generates a name. This name begins with the letters SQL_CUR.
Note In ODBC 2.x, when there was no open cursor and no name had been set by a call to SQLSetCursorName, a call to SQLGetCursorName returned SQLSTATE HY015 (No cursor name available). In ODBC 3.x, this is no longer true; regardless of when SQLGetCursorName is called, the driver returns the cursor name.
SQLGetCursorName returns the name of a cursor whether or not the name was created explicitly or implicitly. A cursor name is implicitly generated if SQLSetCursorName is not called. SQLSetCursorName can be called to rename a cursor on a statement as long as the cursor is in an allocated or prepared state.
A cursor name that is set either explicitly or implicitly remains set until the StatementHandle with which it is associated is dropped, using SQLFreeHandle with a HandleType of SQL_HANDLE_STMT.
</quote>
So now I can assume that MyODBC is an ODBC version 2.x because of the exception thrown. Now my question is, where would I place a call to SQLSetCursorName() and where do I get the 'statementhandle' needed in the call?
And here I thought this would be easy
CPUA 0x5041
Sonork 100.11743 Chicken Little
"So it can now be written in stone as a testament to humanities achievments "PJ did Pi at CP"." Colin Davies
Within you lies the power for good - Use it!
|
|
|
|
|
Hello,
I'm using an .mdb file as a database for a project i'm working on. I have a series of words that I have to loop through and for each word I have to:
1) Check if the word is already in the table (SELECT COUNT(*) ... )
2) If it is, then update count (UPDATE ... )
3) If does not exists, create entry (INSERT INTO ... )
I'm using OLE DB's CDynamicAccessor with SQL queries but the search part (SELECT COUNT(*) WHERE word = 'blabla') is very slow. Is there a way to make this any faster? I need something like "find first occurence of... ".
By the way, maybe it's because I set the tables poorly (I didn't use indexes and such...) maybe the problem's there!
Thanks!
---------------
Concentrating on Ideas
http://www.edovia.com
|
|
|
|
|
I never used CDynamicAccessor before, but you are hitting too much the database.
use:
UPDATE table SET count = count + 1 WHERE word = 'blabla'
Also, check if there is an index on the word field.
With OLEDB you should have something similar to ADO's RecordsAffected. Then, if RecordsAffected == 0, you execute INSERT INTO
Q261186 - Computer Randomly Plays Classical Music
|
|
|
|
|
Success! It's faster but not enough... sometimes it will take up to 20 seconds to go through about 1700 words in a table that contains only 764 words (it will likely contain 10X that amount eventually).
I did set an index for the word field... is there anything else I could do to improve speed and performance?
Thanks!
---------------
Concentrating on Ideas
http://www.edovia.com
|
|
|
|
|
LukeV wrote:
that contains only 764 words (it will likely contain 10X that amount eventually).
Only 7000 words!? I supposed you have much, much more data than this!
Well, load the entire table to memory with a SELECT * (a std::map would be just fine), drop it and rewrite the whole table to disk...
The whole process should take only 2 or 3 seconds this way.
Q261186 - Computer Randomly Plays Classical Music
|
|
|
|
|
Daniel Turini wrote:
Only 7000 words!? I supposed you have much, much more data than this!
It could be more, since the table will grow with time. I wouldn't be suprised to see it around 10-20k but I don't think it will grow any bigger than that...
Daniel Turini wrote:
drop it and rewrite the whole table to disk...
The whole process should take only 2 or 3 seconds this way.
So perhaps what I should do is the get the table at the beginning of the app, update the map and rewrite the table to disk at the end of the program... But if the app crashes, I'll lose everything and I don't know if it would consume too much memory when the table grows...
I'll let you know how it turns out. Thanks!
---------------
Concentrating on Ideas
http://www.edovia.com
|
|
|
|
|
LukeV wrote:
By the way, maybe it's because I set the tables poorly (I didn't use indexes and such...) maybe the problem's there!
Why should you expect database to be fast if you don't bother to set up indexes? Why database at all, if you don't index your data? Just store everything in a large text file.
You should realize that there's no magic in programming. Search algorithms are deterministic - and you are required to organize data into tables and indexes based your logical dependencies and search criteria.
BTW, are there any reasons you yse dynamic accessors and not static? This will also cost you some ticks.
Vagif Abilov
MCP (Visual C++)
Oslo, Norway
Hex is for sissies. Real men use binary. And the most hardcore types use only zeros - uppercase zeros and lowercase zeros.
Tomasz Sowinski
|
|
|
|
|
Vagif Abilov wrote:
Why should you expect database to be fast if you don't bother to set up indexes? Why database at all, if you don't index your data? Just store everything in a large text file.
I know... It's just that I never had a project that required a huge database so speed wasn't a concern.
Vagif Abilov wrote:
BTW, are there any reasons you yse dynamic accessors and not static? This will also cost you some ticks.
I'll give that a try as well... Thanks!
---------------
Concentrating on Ideas
http://www.edovia.com
|
|
|
|
|
Access isn't the fastest DB, but you should be able to achieve adequate performance for 20k rows of a relatively small table.
First, make sure you used indexes and use the right one. If the column 'word' contains a unique list of words, use the "Indexed - no duplicates" type of index.
Second, check the data type and size you used. It looks like you are using a "Text" data type which should be OK, but check the length of the column. The default is 50. Is this more than you need, if so, use a smaller value. Access always uses varchar data but you can force it to fixed-width by pre-filling the column with non-space data (such as the under-score _).
Third, the select count(*) statement will require a full table-scan unless the "indexed - no duplicates" option is used on the column. If you just need to know if the item is in the table, just do a "SELECT WORD from table where word = 'blablabla'". This would generate better performance if not using a unique index.
Fourth, consider removing the "select" statememt altogether. Just do an UPDATE and if it fails check the SQLCODE and perform an INSERT.
Sixth, if this is a single threaded GUI app you could just open a recordset against the table and disconnect it from the DB.
Seventh, if this is a GUI app (not ASP based) you can just load the whole word list into memory. If the maximum length of a word is 50 bytes and you can have 30K words, then you will need 1.5mb of ram.
Eighth, if you use an in-memory list and you don't want to risk losing updates, add a new table to your db called "Update_History" with an auto-number column and a 'Word' column. Each time the in-memory list is incremented, insert a record into this table. When your app finihes normally, walk through this table and perform the UPDATEs/INSERTs against your original table and then delete the table contents. When you app launches, check this table and if it is not empty just do the updates then.
|
|
|
|
|