|
ivo75 wrote: I want to add 1 column in first table and column in query will be 4.
As jschell indicated, you will need to use an alter statement to replace the existing view with a new one.
Here is a site that has more detail about how you alter a veiw in Access (I didn't think it was posible, but then I havent worked with MS Access since 1997)
http://allenbrowne.com/func-DDL.html[^]
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
ivo75 wrote:
for example I have 1 query in who participate 3
columns in 2 table and I want to add 1 column in first table and column in query
will be 4. I want do this with code.
Not sure I understand what you are saying.
However you appear to be referring to that you now have the following:
select t.a, t.b, t.c from...
And you want the following:
select t.a, t.b, t.c, t.d from...
The problem with that is that SOMETHING is using the first query. And it is not going to magically use the second one. At best (depending on a number of things) it will simply do nothing at all. At worse something will fail in a way that makes it difficult to determine what happened.
So the best case has no impact - so no point in trying to change it.
In the worst case many things fail - so not point in trying to change it.
Thus it follows, what you think that you want to do will NOT work.
|
|
|
|
|
alter exists column datatype.
alter table tblname
alter column columnname datatype(variablesize)
or
add new column................
alter table tblname
add columnname datatype(variablesize)
|
|
|
|
|
For an existing Access database holding around 100 queries, I'm looking for a comfortable way to export the actual SQL statements that represent those queries; ideally I'd like some (C#) code that generates a DataTable, with columns queryName and SqlStatement. Alternatively, a command or script that tells Access to create a text file holding such information. Or some magical SQL snippet that creates a new table in the database itself.
To be perfectly clear, I need the SQL statements, not the results of the queries themselves. And I'll need to run it a couple of times in the next few months, so I don't plan on doing it manually, one by one!
Luc Pattyn [My Articles] Nil Volentibus Arduum
modified on Wednesday, August 24, 2011 10:12 AM
|
|
|
|
|
This[^] is not exactly what you requested, but might prove a useful starting point.
|
|
|
|
|
Thanks a lot, I got it working right away.
|
|
|
|
|
|
Thanks for the quick response. I managed to solve my little problem with the first reply I got.
|
|
|
|
|
Problem solved, using "good" old DAO from C# isn't that hard after all, and all that was required is this:
public override void GetQueries() {
DAO._DBEngine dbEng=new DAO.DBEngineClass();
DAO.Workspace ws = dbEng.CreateWorkspace("", "admin", "", DAO.WorkspaceTypeEnum.dbUseJet);
DAO.Database db = ws.OpenDatabase(File, false, false, "");
foreach(DAO.QueryDef q in db.QueryDefs) {
env.log(0, q.Name+" = "+q.SQL);
}
}
Thanks for all the input!
|
|
|
|
|
Luc Pattyn wrote: DAO
WOW! That brings back memorys...Not entirly good ones but still
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
Luc Pattyn wrote: DAO
Ahhhhh!!!! Good old DAO
|
|
|
|
|
I'm not a big fan of Oracle and am quite happy to delegate the coding of the more complex stored procedures to others. Because of this lack of knowledge I need some opinions.
When building a SP is it reasonable to create a series of strings and then EXECUTE IMMEDIATE to load the results into a temp table.
The proc is complex and there are a number of steps so the temp table is valid but I have problems with the use of concatenated strings. I would think this would negate any benefit from compiling the proc as this would on validate the concatenation of the strings and not their content.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
EXECUTE IMMEDIATE is a generally accepted method of coding dynamic sql in PLSql.
I use this methodology and have not had any issues - yes it can take a bit to get the concatenation of quotes etc correct - however in the end this works fine.
If you are concerned about the content of the queries you could put the queries into a meta table and pull the queries in and build your dynamic sql at runtime.
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
|
|
|
|
|
My point is that I don't think EVERY query should be dynamic sql. It has it's place but not for every single query.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I completely agree 
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
|
|
|
|
|
Not that this helps but...
I didn't know Oracle supported stored procedures, when I had to use it (1996-2001, Oracle 6?), we never used stored procedures, only PRO*C. And it was good. 
|
|
|
|
|
You are correct, in terms of the naming convention they are called procedures - they are basically the same as Microsoft's stored procedures.
Also procedures can be stored in packages(basically classes).
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
|
|
|
|
|
Even way back when I was using it there were procedures (about the same time). I pointed out to the developer that I could more easily do the string concatenation in c#, as per your prefferred method, than have him do it in PL/SQL.
I pulled in another dev who has very good Oracle skills, the coding style will be changing to proper PL/SQL rather than dynamic sql.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: When building a SP is it reasonable to create a series of strings and then
EXECUTE IMMEDIATE to load the results into a temp table.
Only if it can't be done using normal PL/SQL.
Which would mean almost never.
|
|
|
|
|
That was my opinion, but without the authority of experience I am glad to have it confirmed. That dev now has a refactoring (rewrite) job to do. Luckilly this was the first couple of jobs I have given him.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have code and return value == 0. Why? It must be 99!
ALTER PROCEDURE [dbo].[myproc]
(
@mykod INT
)
RETURNS INT
AS
BEGIN
select * from table1 where kod=@mykod
RETURN 99
END
ICommandText* pICommandText;
hr = pIDBCreateCommand->CreateCommand(NULL, IID_ICommandText,(IUnknown**) &pICommandText);
if (FAILED(hr)) AfxMessageBox("Command Create Command Failed");
WCHAR* wSQLString = L"{?=CALL myproc(?)}";
pICommandText->SetCommandText(DBGUID_DBSQL, wSQLString);
SPROCPARAMS sprocparams = {0, 2};
DBPARAMS Params;
const ULONG nParams = 2;
typedef struct tagSPROCPARAMS
{
long lReturnValue;
long lkodValue;
} SPROCPARAMS;
DBBINDING acDBBinding[nParams];
DBBINDSTATUS acDBBindStatus[nParams];
for (ULONG i = 0; i < nParams; i++)
{
acDBBinding[i].obLength = 0;
acDBBinding[i].obStatus = 0;
acDBBinding[i].pTypeInfo = NULL;
acDBBinding[i].pObject = NULL;
acDBBinding[i].pBindExt = NULL;
acDBBinding[i].dwPart = DBPART_VALUE;
acDBBinding[i].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
acDBBinding[i].dwFlags = 0;
acDBBinding[i].bScale = 0;
}
acDBBinding[0].iOrdinal = 1;
acDBBinding[0].obValue = offsetof(SPROCPARAMS, lReturnValue);
acDBBinding[0].eParamIO = DBPARAMIO_OUTPUT;
acDBBinding[0].cbMaxLen = sizeof(long);
acDBBinding[0].wType = DBTYPE_I4;
acDBBinding[0].bPrecision = 11;
acDBBinding[1].iOrdinal = 2;
acDBBinding[1].obValue = offsetof(SPROCPARAMS, lkodValue);
acDBBinding[1].eParamIO = DBPARAMIO_INPUT;
acDBBinding[1].cbMaxLen = sizeof(long);
acDBBinding[1].wType = DBTYPE_I4;
acDBBinding[1].bPrecision = 11;
IAccessor* pIAccessor;
HACCESSOR hAccessor;
pICommandText->QueryInterface(IID_IAccessor,
(void**) &pIAccessor);
if(FAILED(hr)) AfxMessageBox("Failed Query Interface IId_IAccessor");
hr = pIAccessor->CreateAccessor(DBACCESSOR_PARAMETERDATA,
nParams, acDBBinding, sizeof(SPROCPARAMS), &hAccessor,
acDBBindStatus);
if(FAILED(hr)) AfxMessageBox("Failed Create Accessor");
Params.pData = &sprocparams;
Params.cParamSets = 1;
Params.hAccessor = hAccessor;
LONG cRowsAffected;
IRowset* pIRowset;
hr = pICommandText->Execute(NULL, IID_IRowset, &Params,&cRowsAffected, (IUnknown**) &pIRowset);
if (FAILED(hr)) AfxMessageBox("Execute Failed");
CString str;
str.Format("%d",sprocparams.lReturnValue);
AfxMessageBox(str);
pIRowset->Release();
str.Format("%d",sprocparams.lReturnValue);
AfxMessageBox(str);
Íà ôîðóìå ÿ èùó ïðîãðàìû ïî Ñ++.
|
|
|
|
|
Hi All,
Can somebody who has SQL 2000 assist with this?
Create a simple DTS package using any table from Northwind DB and then prompt the number of rows in that table through a message box. This package should then be posted to me.
Aim: I want to experiment conversion of DTS to SSIS 2008 package but I don't have SQL 2000
I look forward to your response.
Regards,
Current
|
|
|
|
|
This is a great site that I use for such a requirment
SQL DTS[^]
They also run a site for SSIS to the link to that is at the top of the page.
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
Thanks your response.
I just wonder that I must have SQL 2000 before I can experiment the conversion.Am I correct?
Thanks
|
|
|
|
|
current1999 wrote:
I just wonder that I must have SQL 2000 before I can experiment the conversion
The answer is no, if you are just converting an existing package then all you need is the target version.
<hr><small> Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
</small>
</hr>
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
