Convert ORACLE functions - Database Discussion Boards

RichardS16-Feb-03 21:39

16-Feb-03 21:39

Hi,

In our office we have a network of 3 computers. We have a db issue where we nee to access a single db on one of the 3 computers. Which would be the best solution:

1) Implementing SQL Server on the PC with the db
2) Writing an app to cache the db data and hand it over to clients that requests it (reads and writes)
3) Connecting to the db directly through the network connection

For interest the db we are using in Access.

Any information would be greatly apprecated.

Regards,
Rich

"Programming today is a race between software engineers striving to build bigger and better idiot-proff programs, and the Universe trying to produce bigger and better idiots. So far the Universe is winning." -- Rich Cook

Re: DB's on Servers

Hesham Amin17-Feb-03 21:43

Hesham Amin

17-Feb-03 21:43

Connecting to the db directly through the network connection

i think this is the best one

3 clients don't need SQL server , and you can directly access the MS Access db from the clients.

Syntax error with CREATE TABLE

IrishSonic16-Feb-03 1:41

IrishSonic

16-Feb-03 1:41

Hiya am getting a syntax error on using CREATE TABLE in SQL and an Access database:

// open the database
database.Open( NULL,false,false,sDsn );

database.ExecuteSQL("CREATE TABLE OFFICES (OfficeID TEXT(4) OfficeName TEXT(10) ) ");

Does anyone know what is wrong with the ExecuteSQL line..

Thanks
grahamoj.

Re:Found my problem but need to be able to create table for 600 chars Syntax error with CREATE TABLE

IrishSonic16-Feb-03 2:12

IrishSonic

16-Feb-03 2:12

Hiya again, I found out my problem. Missing comma i.e

database.ExecuteSQL("CREATE TABLE OFFICES (OfficeID TEXT(4),OfficeName TEXT(10) ) ");

So now what I need to be able to do is make the text field 600 chars. So tried these:

database.ExecuteSQL("CREATE TABLE OFFICES (OfficeID TEXT(4) OfficeName TEXT(600) ) ");

and

database.ExecuteSQL("CREATE TABLE OFFICES (OfficeID TEXT(4) OfficeName MEMO(600) ) ");

but it won't create the table because text fields are too long..

any ideas on how to do this??

grahamoj.

Re:Found my problem but need to be able to create table for 600 chars Syntax error with CREATE TABLE

karl_w16-Feb-03 3:17

karl_w

16-Feb-03 3:17

For SQL-Server the maximum length for TEXT is 2,147,483,647 (and even VARCHAR can store up to 8000 characters), so I don't think 600 should be the problem. Maybe it's just the missing comma again?

--edit--
BTW: For 600 characters you shouldn't use TEXT but VARCHAR because of better performance. TEXT is designed for storing large textfiles, so in the table there is only stored a pointer to the actual data.
--edit--

--

karl

Re:Found my problem but need to be able to create table for 600 chars Syntax error with CREATE TABLE

David Salter16-Feb-03 10:44

David Salter

16-Feb-03 10:44

grahamoj wrote:
database.ExecuteSQL("CREATE TABLE OFFICES (OfficeID TEXT(4) OfficeName MEMO(600) ) ");

Don't specify a length for a memo, i.e. use:

database.ExecuteSQL("CREATE TABLE OFFICES (OfficeID TEXT(4), OfficeName MEMO ) ");

Dave.

Thanks.

IrishSonic17-Feb-03 9:58

IrishSonic

17-Feb-03 9:58

Thanks.

Convert ORACLE functions

Andy H15-Feb-03 23:30

Andy H

15-Feb-03 23:30

I have a SELECT statement written for Oracle which I need to convert to work on Access, SQL, and Oracle. Could some kind soul please let me know what the SQLCommand would be to convert from:

SELECT Description,<br />
Decode(KQues, 'Y', NULL, To_Char(Add_Months(Warning_Deadline, 12*(Period_ID - 7)),'dd/mm/yyyy')),<br />
Decode(KQues, 'Y', NULL, To_Char(Add_Months(Statutory_Deadline, 12* (Period_ID - 7)),'dd/mm/yyyy')),<br />
Claim_Made,<br />
To_Char(Date_Claim_Made,'dd/mm/yyyy'),<br />
Signed_Off_By,<br />
KQues,<br />
Parent_ID<br />
FROM<br />
Client_Claims,<br />
Standard_Claims<br />
WHERE<br />
Client_ID = n AND<br />
Standard_Claims.Claim_ID = Client_Claims.Claim_ID

You will be doing me a big favour in converting this.

Re: Convert ORACLE functions

jamesr337816-Feb-03 5:38

jamesr3378

16-Feb-03 5:38

this should work for MSSQL

SELECT Description,
Case KQues
When 'Y' Then Null
Else Convert(Varchar(12),AddDate(month,12*(Period_ID - 7),Warning_Deadline),113)
End,
Case KQues
When 'Y' Then Null
Else Convert(Varchar(12),AddDate(month,12*(Period_ID - 7),Statutory_Deadline),113)
End,
Claim_Made,
Convert(Varchar(12),Date_Claim_Made,113)
Signed_Off_By,
KQues,
Parent_ID
FROM Client_Claims,
Standard_Claims
WHERE Client_ID = n
AND Standard_Claims.Claim_ID = Client_Claims.Claim_ID

Re: Convert ORACLE functions

Andy H16-Feb-03 6:12

Andy H

16-Feb-03 6:12

I will give it a go. Cheers Big Grin | :-D

Setting the lenght of a text field in an SQL Access database...

IrishSonic15-Feb-03 4:40

IrishSonic

15-Feb-03 4:40

Hiya I am writing a program with SQL and an Access Database. I need to set the lenght of the text field (a column) to 600. Can I do this from my program??

I have tried setting it in the database in the design view but it will only allow a maximum of 255 chars. But it would be better to be able to set it from the program.

I am using CDatabase and CRecordset and its an ODBC database..

Thanks
grahamoj.

Re: Setting the lenght of a text field in an SQL Access database...

David Salter16-Feb-03 10:42

David Salter

16-Feb-03 10:42

In access, create the field as a memo with no length e.g.

create table blah<br />
(<br />
 field1 memo<br />
)

Dave.

Documenting DB,Sprocs,Trigers, etc...

Ray Cassick14-Feb-03 11:43

Ray Cassick

14-Feb-03 11:43

Does anyone know of a product that will create XML bassed documentation for a SQL database like you can get with C# or by using NDoc?

Paul Watson wrote:
"At the end of the day it is what you produce that counts, not how many doctorates you have on the wall."

George Carlin wrote:
"Don't sweat the petty things, and don't pet the sweaty things."

Anybody plz, what is the following problem and how to solve that...

Exceter13-Feb-03 23:56

Exceter

13-Feb-03 23:56

I have problem with MS SQL Server during installation
firstly there was no problem with Standard Edition, then I uninstalled that and tried to install Enterprise Edition
and faced the following problem:
at the end of the installation the following message appears "... refer to sqlstd.log file"

now I have the same problem with Standard Edition

(I tried to install SQL Server on another computer, the same problem)

thanx.

Re: Anybody plz, what is the following problem and how to solve that...

Hesham Amin14-Feb-03 11:33

Hesham Amin

14-Feb-03 11:33

ok .. can you tell us what is written in the log file..
it may explain the problem..

also what OS do you use ?

A simple ADO.NET and table design question

DionChen13-Feb-03 5:40

DionChen

13-Feb-03 5:40

Hi, All:

I am writing my FIRST Windows Form Application in .NET to edit my MS Access database. In this Access DB, I have a table with two columns: EquipmentID and EquipmentDescription. Some other tables use the ID to refer the equipment. This application supports multiple users at the same time. That mean that it could be more than one user add new equipmment. I use in-memory DataSet to get user's input and then call Insert/Delete/Update methods. I am debating that whether I should use Access AutoNumber for the ID field, or I should just use DataReader to get the Max ID used, then Add 1 to create my new ID. The AutoNumber makes things simpler, but if I try to merge to DB later, I will have a lot of headaches. If I assign my own ID, I might have conflict with other users and fail the INSERT.

So what is the typical way to achieve this, I know a lot of people have done this, please give me some ideas. Thanks in advance.

Dion

Re: A simple ADO.NET and table design question

Peet Schultz17-Feb-03 8:13

Peet Schultz

17-Feb-03 8:13

If you use a GUID (Globally Unique Identifier) as the ID, you should have something that can be ported to any platform as well as solve your Identity problems. I have previously used a VB6 function that produced GUID's, and am sure you should find one for .Net.

Regards
Peet Schultz
Centurion SA

YASP

Re: A simple ADO.NET and table design question

Alexander Kojevnikov17-Feb-03 22:22

Alexander Kojevnikov

17-Feb-03 22:22

There're was an article on MSDN covering this issue, but I cannot find it.

What it suggests is to configure your EquipmentID to use negative autoincrement values:

<br />
ds.Tables["Equipment"].Columns["EquipmentID"].AutoIncrementSeed = -1;<br />
ds.Tables["Equipment"].Columns["EquipmentID"].AutoIncrementStep = -1;<br />

...and to modify your insert SP (or insert code if you use Access) to return the newly created identity value. SP would look like this:

<br />
CREATE PROCEDURE dbo.daInsertEquipment<br />
(<br />
	@EquipmentID int OUT,<br />
	@EquipmentDescription nvarchar(200)<br />
)<br />
AS<br />
    INSERT INTO Equipment<br />
    (EquipmentID, EquipmentDescription)<br />
    VALUES (@EquipmentID, @EquipmentDescription)	<br />
    <br />
    SET @id = SCOPE_IDENTITY()<br />

HTH

Alexandre Kojevnikov
MCP (SQL2K)
Leuven, Belgium

Backup Oracle

Nguyen Huynh Hung13-Feb-03 3:10

Nguyen Huynh Hung

13-Feb-03 3:10

How to backup StoreProcedure, View, database... in Oracle.
Help me please!!!
Thank you.
***Hung***

Re: Backup Oracle

Mark Conger14-Feb-03 0:33

Mark Conger

14-Feb-03 0:33

Oracle has a myriad of ways to backup the database. I suggest you check out the Backup & REcovery Concepts guide for details on all of them.

Unfortunately there is no wasy way for me to tell you how to do this without knowing your environment in detail. Basically your options are: Export, Traditional cold backup, and backup using recovery manager (or EBU if you're on 7.X). The export is the simplest but not real efficient and can be costly in resources. Traditional works fine, but requires downtime and is also costly in resources. Rman is relatively efficient, but fairly complex to use and setup.

If yo need a quick backup, most dbas use export when in a pinch if there aren't any other options.

Mark Conger
Sonork:100.28396

ADO and ADOX

Miguel Segarra12-Feb-03 8:26

Miguel Segarra

12-Feb-03 8:26

I have problems when i use the #import directive to import the msado and msadox DLLs in the same application.

#import <C:\Archivos de programa\Archivos comunes\System\ado\msado15.dll> no_namespace rename ("EOF", "adoEOF")
#import <C:\Archivos de programa\Archivos comunes\System\ado\msadox.dll> no_namespace

Has anyone found this problem? and the solution?

Thanks in advance.

Re: ADO and ADOX

Rickard Andersson²⁰13-Feb-03 0:37

Rickard Andersson²⁰

13-Feb-03 0:37

Don't use < and > between the path, use:

#import "msado15.dll" no_namespace rename("EOF", "adoEOF")

Rickard Andersson@Suza Computing
C# and C++ programmer from SWEDEN!
UIN: 50302279
E-Mail: nikado@pc.nu
Speciality: I love C#, ASP.NET and C++!

DataSet query

Shaun Wilde11-Feb-03 23:46

Shaun Wilde

11-Feb-03 23:46

Hi all

I have a typed DataSet called MyDataSet and I populate it by calling Fill on the adapter eg

MyDataSet mds = new MyDataSet();
myAdapter.Fill(mds);

now I use this DataSet to populate a listbox eg

myListbox.DataSource = mds;
myListBox.DataTextField = "Text";
myListBox.DataValueField = "ID";
myListBox.DataBind();

but I don't really like this - what if my DataSet changes (as would happen early on in a project)
I may lose the ID or Text columns and the only time I would notice would be at runtime.

Is there any Information in the typed dataset that contains my column names? so I could do something like

myListbox.DataSource = mds;
myListBox.DataTextField = mds.Tables[0].colText.ColumnName;
myListBox.DataValueField = mds.Tables[0].colID.ColumnName;
myListBox.DataBind();

so that if say Text is remved from my DataSet schema then on next compilation colText would no longer exists and the compilation would break and then it can be fixed.

I see that the columns are in there but made internal - arrgghhhhh

Please help

Shaun Wilde

Re: DataSet query

Mazdak12-Feb-03 1:10

Mazdak

12-Feb-03 1:10

Shaun Wilde wrote:
Is there any Information in the typed dataset that contains my column names?

mydataset.Tables["mytablename"].Columns[index].ColumnName;

Mazy

"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits

Re: DataSet query

Shaun Wilde12-Feb-03 1:26

Shaun Wilde

12-Feb-03 1:26

true - I did look at this before I posted -

but that still requires me to understand what info is in what column - if my column disapeared or moved (changed index) then it would still compile but it would go wrong at runtime - what I was hoping for was a more strongly typed solution (if one exists) that would be caught at compile time rather than runtime.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.