|
Hello,
I have been trying to develop an automatic way of programmatically accessing datasources and performing some predefined(-supported) processing on them.
The question I would like to ask you people has to do with numeric fields. What exactly is precision? Is it the maximum length in digits of a field, or is there more to it? What about a "field's scale", what is it and how does it affect a field's value handling?
Thank you very much,
Dave
|
|
|
|
|
According to SQL BOL:
Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2.
http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_8rc5.asp[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
|
|
|
|
|
Anyone here using this?
I have what is essentially a frozen database, (the live version is in SQLS2K) and I want a single program to be able to access it. I make heavy use of lookup tables (e.g. a text 'word' to a numeric ID, then look the ID up in a list to yield a list of matches), but my target machine is low on memory, so I don't really want to run MSDE on it, and I want the machine to work standalone.
From what I've read, once I've created and populated the database, it should do what I want, but I'd welcome comments.
(It's not quite readonly, since I save lists of results occasionally, and need to update one other table as well, but I can save those externally *as well* and then update the master later.
Does this sound feasible? I have all the SQL concerned (except that the word lookup is generated on the fly depending on input, since the number of words varies...)
Steve S
Developer for hire
|
|
|
|
|
Hi All,
I dont know whether this is actually possible but thought I post the questions anyway. Is it possible to have arguments for stored procedures derived from SQL Server Management API and not pre-configures in a separate table. Thanking you in advance.
N
|
|
|
|
|
Hi everone,
I am having trouble in updating the source database. I am using a dialog based application with a datagrid. I managed to add a number (01065017) to the first coloumn succesfully but could not update the source after the addition of this number. Any Idea why.
The following is the code:
m_pConn.CreateInstance(__uuidof(Connection));
m_pConn->Open("Provider=Microsoft.Jet.OLEDB.4.0;"
"Data Source=ADOTestDB.MDB",
_bstr_t (""),
_bstr_t (""),
adModeUnknown );
m_pComm.CreateInstance(__uuidof(Command));
m_pComm->ActiveConnection = m_pConn;
m_pComm->CommandText = "Select * From Student";
m_pReco.CreateInstance(__uuidof(Recordset));
m_pReco->CursorLocation = adUseClient;
m_pReco->Open ( (IDispatch *) m_pComm, vtMissing, adOpenStatic,
adLockBatchOptimistic, adCmdUnknown);
m_DataCtrl.SetCaption("Students");
m_DataCtrl.SetRefDataSource(NULL);
m_DataCtrl.SetRefDataSource((LPUNKNOWN) m_pReco);
m_DataCtrl.Refresh();
m_pReco->AddNew();
FieldsPtr pFields = m_pReco->Fields;
FieldPtr pStudentNo = pFields->GetItem("StudentNo");
ASSERT(NULL != pStudentNo);
CString ali = (char*)(_bstr_t) pStudentNo->Value;
ali.Format("01065017");
_variant_t vColumn, vValue;
vColumn.SetString("StudentNo");
vValue.SetString(ali);
m_pReco->Update(vColumn, vValue);
m_pReco->Update();
Thanx in advance
|
|
|
|
|
I have what I think is a fairly simple procedure, but its currently not working. I'm hoping someone here is an ADO.NET guru who has tried the same thing, and can help me out.  Here is the situation:
Process:
===============
I am developing a small app to post new, incomming data to an existing database. The database is a live, production database, and there is the possability that duplicate information may come through in the new incomming data. I've set up a processing database, where I insert the new data in temporary tables, and select and insert the data from the live database into other temporary tables. I post the new data to the same tables as the temp live data, perform some business logic, and then perform an update on the live database.
Setup:
===============
I am using ADO.NET with a single DataSet to get the data from the live database into the temp processing tables. I use the same dataset to insert the new data into other temp tables.
All the DataTables have primary keys configured.
There are DataAdapters configured for importing the live data, with proper DataTableMappings, each of which have proper DataColumnMappings.
There are DataAdapters configured for importing the new data, with proper DataTableMappings and DataColumnMappings.
The databases are both Microsoft Access databases, although I have tried with Microsoft SQL Server, with the same results.
Issues:
===============
When I execute the application, it imports the live data, imports the new data, and performs some basic business rules. After the rules are done, I need to update the live database. The primary keys on the DataTables filter out any duplicate data, so when I import the new data, only new information is added. The business logic changes some of the existing live data, as well as some of the new data. When this is all said and done, and I try to update the live database, it doesn't update properly. I've never tried to use a single DataSet with two different data sources before. This particular situation requires that both data sources be updated, inserted into, and selected from a couple times.
Question:
===============
How does one update #1 data source with data from #2 data source using a single DataSet, and be able to do the opposite, update the #2 data soource with data from #1 data source?
Thanks for any help, and I hope I have provided enough information.
|
|
|
|
|
I have this case:
I am working on a screwed up application in which arraylist are using for implementing layer isolation.
i.e., It is using some function to convert each and every values it return from the SQLDataReader into an arraylist and then pass it to the UI Layer.
I want to know that whether this will help in anyway or will cause an overhead to the application(remember the appn. is screwed up!!)
|
|
|
|
|
obymathew wrote:
I want to know that whether this will help in anyway or will cause an overhead to the application
This is the wrong way of decoupling layers:
1. This will add both complexity and overhead - you'll need a lot of casting on the UI layer, as ArrayLists store only objects.
2. Probably your data is being put on specific positions (e.g, al[1] is the customer name, al[2] your customer address, etc. This makes the application hard to debug, and change impact analysis very hard to do.
What I suggest:
If you still want or need to keep a strong isolation between these layers (e.g., the data or the UI layer can be used with other implementations) you can:
Create a 3rd assembly, which will define the interfaces that'll be used by both the data layer and the UI layer. From the data layer, return those interfaces, or arrays of those interfaces. To allow incremental changes, and to not disrupt everything when changing a method, provide a method on each interface that converts that data to an arraylist.
Before doing any changes, plan ahead a bit: make a backup (actually, you should use source control), create some basic unit tests and only then start working.
Don't forget that you may need to maintain two versions while you're converting things if your users need changes/bug corrections.
Yes, even I am blogging now!
|
|
|
|
|
Hello again buddies,
I wonder how can I work with just time section of a datetime or smalldatetime object ignoring its date section. wouldn't it be better to provide a "time" data type?
What I am looking for is to query records that a datetime column of them is in a specified time with no importance on the date
---
"Art happens when you least expect it."
|
|
|
|
|
Use DATEPART(). It is a bit of a mess if you want to do something like get me the records between 11:30 and 14:30 but it is doable.
Do you want to know more?
|
|
|
|
|
Yes it works, thank you so much Colin 
I am sorry if my questions seems so amature, I guess I am a good programmer but a newbie in SQL which I feel is a great different programming environment. I promiss that will change!!!! 
---
"Art happens when you least expect it."
|
|
|
|
|
Den2Fly wrote:
a newbie in SQL .... I promiss that will change!!!!
We've all got to start somewhere.
Do you want to know more?
|
|
|
|
|
Set them all to the same date.
CAST('1900-01-01T' + SUBSTRING(CONVERT(varchar, theTime, 126), 12, 12) AS datetime)
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
Sorry, this is off topic, but:
"SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)" <--- 
|
|
|
|
|
Seems a great trick, what about the performance? DATEPART or CAST??
---
"Art happens when you least expect it."
|
|
|
|
|
As Colin indicated in his original response, usability and efficiency both depend greatly on exactly what you're doing with it.
Also, I've found that when you take two solutions to the same problem and compare them, it's possible that one solution is faster on one server, and the other is faster on a different one.
For instance, stripping the time from a datetime. There are many ways to do this, the two most common being CONVERT(varchar(8), theDate, 112) or DATEADD(day, 0, DATEDIFF(day, 0, theDate)). I find that DATEADD/DATEDIFF runs faster in our development environment by a factor of 5, but the CONVERT method is faster in our production environment by a factor of 2.
So, the short answer is that you should write the query both ways, and benchmark them on your own system to see which one is faster on your own server. It also depends greatly on where/how you're using it. If you're using it in the WHERE clause, either one of them is going to be a pig. If you're just using it in the SELECT clause, then it will probably depend on your system hardware and configuration.
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
You are right. Thank you.
---
"Art happens when you least expect it."
|
|
|
|
|
Hello is it possible in SQL (and specially SQL Server 2000) to perform a SELECT on result of another SELECT statement? something like this just as an example of what I am talking about:
SELECT * FROM (SELECT a.Name, a.EMail FROM Users a WHERE a.Gendre = 0) WHERE Name = 'Danielle'
Thanks for any note,
-Den
---
"Art happens when you least expect it."
|
|
|
|
|
SELECT myTable.* FROM (SELECT a.Name, a.EMail FROM Users a WHERE a.Gendre = 0) myTable WHERE Name = 'Danielle'
You need to provide an alias. Why do you want to do this ? It means the data needs to be processed twice.
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
HEllo Christian,
Thank you so much for your reply
The example I used was not my real requirement and as Colin said it could be easily relpaiced by an "AND". I have a rather complicated query in which I really need such an approach
Thank you again
---
"Art happens when you least expect it."
|
|
|
|
|
Yeah, I guessed it was an example
I've sometimes felt I needed to do something similar, but I've always found a better performance alternative. In fact, we're ot allowed to write queries like this at work, and we write some complex queries Perhaps you could provide an example closer to what you're doing ?
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
Christian Graus wrote:
In fact, we're ot allowed to write queries like this at work
Do you mean as per the example? Or using a subquery in an inner join?
Either way, isn't it a bit restrictive. Surely a better approach would be to write the query which ever way makes most sense and then if it is too slow profile it to see where the bottle necks are and rewrite those sections.
Do you want to know more?
|
|
|
|
|
Colin Angus Mackay wrote:
using a subquery in an inner join?
Bingo
Colin Angus Mackay wrote:
Either way, isn't it a bit restrictive.
The actual rule is, if you think you need to do it, ask and they'll show you why you don't
It works, I always find for myself why I don't.
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
hi
nice to see ur provided info anbout Select Subquries...
but u alos mentioned that u are not allowed to use such queries at work..then what u do ...where subquries are needed...and u don't have an alternate????
is there any more accurae and efficient way as an alternate to these sub-quries.
................................
munawar
|
|
|
|
|
munawarhussain wrote:
then what u do ...where subquries are needed
They generally are not needed, that's the point.
munawarhussain wrote:
is there any more accurae and efficient way as an alternate to these sub-quries.
I usually find a way to use joins rather than subqueries. Obviously, if there physically is no other way, we'd use a subquery, but as a rule we're able to eliminate them.
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
