Scalar select (single row subquery)


I'm trying to speed this up. 88 seconds is far too long. I need to identify if a student belongs to an ACAD_PLAN that has an ACAD_SUB_PLAN with ACAD_SUBPLAN_TYPE = 'AOP'. AOP is Accelerated Online Program. Trying to modify it into a subquery. emplid exist in PS_ACAD_PLAN and PS_ACAD_SUBPLAN.

PS_ACAD_SUBPLN_TBL is just a setup table that contains the attributes of the ACAD_SUBPLAN. Each one if effective dated where I get the MAX(EFFDT)

So an inner query and an outer query, but which is which

Code: sql Code: Original - sql Code SELECT A.EMPLID , C.INSTITUTION , A.ACAD_CAREER , A.EFFDT , A.EFFSEQ , A.ACAD_PLAN , B.ACAD_SUB_PLAN , C.ACAD_SUBPLAN_TYPE FROM PS_ACAD_PLAN A, PS_ACAD_SUBPLAN B , PS_ACAD_SUBPLN_TBL C WHERE A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR AND A.ACAD_PLAN = C.ACAD_PLAN AND B.ACAD_SUB_PLAN = C.ACAD_SUB_PLAN AND A.ACAD_CAREER = 'GRAD' AND C.ACAD_SUBPLAN_TYPE = 'AOP' AND C.EFF_STATUS = 'A' AND A.EFFDT = ( SELECT MAX(AA.EFFDT) FROM PS_ACAD_PLAN AA WHERE A.EMPLID = AA.EMPLID AND A.ACAD_CAREER = AA.ACAD_CAREER AND A.STDNT_CAR_NBR = AA.STDNT_CAR_NBR) AND A.EFFSEQ = ( SELECT MAX(AAA.EFFSEQ) FROM PS_ACAD_PLAN AAA WHERE A.EMPLID = AAA.EMPLID AND A.ACAD_CAREER = AAA.ACAD_CAREER AND A.STDNT_CAR_NBR = AAA.STDNT_CAR_NBR AND A.EFFDT = AAA.EFFDT) AND A.plan_sequence = ( SELECT MIN(AB.plan_sequence) FROM ps_acad_plan AB WHERE A.emplid = AB.emplid AND A.acad_career = AB.acad_career AND A.stdnt_car_nbr = AB.stdnt_car_nbr AND A.effseq = AB.effseq AND A.effdt = AB.effdt) AND B.EFFDT = ( SELECT MAX(BB.EFFDT) FROM PS_ACAD_SUBPLAN BB WHERE B.EMPLID = BB.EMPLID AND B.ACAD_CAREER = BB.ACAD_CAREER AND B.STDNT_CAR_NBR = BB.STDNT_CAR_NBR AND B.ACAD_PLAN = BB.ACAD_PLAN AND B.ACAD_SUB_PLAN = BB.ACAD_SUB_PLAN) AND B.EFFSEQ = ( SELECT MAX(BC.EFFSEQ) FROM PS_ACAD_SUBPLAN BC WHERE B.EMPLID = BC.EMPLID AND B.ACAD_CAREER = BC.ACAD_CAREER AND B.STDNT_CAR_NBR = BC.STDNT_CAR_NBR AND B.ACAD_PLAN = BC.ACAD_PLAN AND B.ACAD_SUB_PLAN = BC.ACAD_SUB_PLAN) AND C.EFFDT = ( SELECT MAX(CC.EFFDT) FROM PS_ACAD_SUBPLN_TBL CC WHERE C.ACAD_PLAN = CC.ACAD_PLAN AND C.ACAD_SUB_PLAN = CC.ACAD_SUB_PLAN AND C.INSTITUTION = CC.INSTITUTION);
Posted On: Monday 31st of December 2012 01:49:50 AM Total Views:  316
View Complete with Replies

RELATED TOPICS OF Oracle PROGRAMMING LANGUAGE




Help with Handling absent data

I have a select Query inside a SSIS. "select name from table where val = 1123". I want this query to return some default value if there is no data found. somthing like NVL or Colaesce function. Could you please help. column "name" is of varchar type.
VIEWS ON THIS POST

147

Posted on:

Wednesday 7th November 2012
View Replies!

mysql like

hello .. i have this query in oracle select * \t\t\t\t\t FROM suppliers \t\t\t\t\t WHERE supplier_id LIKE '500_'; this query will return all field that is 4 digit ,begin with 500 and last digit can be 0-9.ButI want last digit not euqal to ( 0 )
VIEWS ON THIS POST

113

Posted on:

Wednesday 7th November 2012
View Replies!

Microsoft.jet.OLEDB4.0 is not registered on the local machine

, im using below code to read the data from excel file OleDbConnection objConn = new OleDbConnection(connectionString); objConn.Open(); DataTable dbSchema = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); \t\t\t\t\t\t\t string firstSheetName = dbSchema.Rows[0]["TABLE_NAME"].ToString(); \t\t\t\t\t\t\t String strConString = "SELECT * FROM [" + firstSheetName + "]"; \t\t\t\t\t\t\t OleDbCommand objCmdSelect = new OleDbCommand(strConString, objConn); \t\t\t\t\t\t\t OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(); \t\t\t\t\t\t\t objAdapter1.SelectCommand = objCmdSelect; \t\t\t\t\t\t\t objAdapter1.Fill(objDataset1, "ExcelData"); its working fine in local machine but its not wroking in the deployed server and its giving error as "Microsoft.jet.OLEDB4.0 is not registered on the local machine" what is this and how to resolve this issue also, should it work on 64 bit machine
VIEWS ON THIS POST

321

Posted on:

Wednesday 7th November 2012
View Replies!

parameterized query and Date Field

Hi! Using this SQL: SELECT\t\tfac_op_d.cod_art_i,SUM(fac_op_d.cantidad)AStotal,inv_mp_m.nombre,fac_op_d.fec_doc FROM\t\t\t\tfac_op_d,inv_mp_m WHERE\t\tfac_op_d.cod_art_i=inv_mp_m.cod_art_iAND(fac_op_d.cod_art_iLIKE\t+'%')AND(inv_mp_m.almacen=1)AND(fac_op_d.fec_docBETWEEN\tAND ) GROUPBYfac_op_d.cod_art_i,inv_mp_m.nombre,fac_op_d.fec_doc This is a query to an old FoxPro database on a server (Same Network). This work in the execution, but just passing the date values as aaaa/mm/dd. On my page when a pass two DateTime Parameters from two TextBox it throw "Unable to recognize the string as a valid DateTime Value" I'm using Ajax Calendar exterder on the textbox to be more specific. Here, (My Country) the format i'm using es dd/mm/aaaa And when i select the datepicker it comes in this format, and then a get the DateTime error type. I can get the rows in the query builder just passing the date like "2010/01/15" and "2010/01/30" for example. Hay can i force the texbox to get this format aaaa/mm/dd regards
VIEWS ON THIS POST

105

Posted on:

Wednesday 7th November 2012
View Replies!

Migration of Tally to SQL server

, \t\t\t\t\t\t I am new to Migrating database . I am having a tally database (or tally data) i want to store it in Sql database usin C# so i dont know how to start the steps . So can anyone guide me hoe to migrate data base .... \t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t
VIEWS ON THIS POST

80

Posted on:

Wednesday 7th November 2012
View Replies!

Get data from MySQL fields

The user puts in there username and password into Login1, the details are checked agaist the database, this works. After "If e.Authenticated = True Then" it starts to fail, I want the program to get the data from the collums "serve" and "box" and use that as the "Login1.DestinationPageUrl", the datatype in the MySQL server is varchar, if this helps, can somone please tell me whats wrong as i am getting very frustrated. ImportsSystem.Data.Odbc PartialClass_Default InheritsSystem.Web.UI.Page ProtectedSubLogin1_Authenticate(ByValsenderAsObject,ByValeAsSystem.Web.UI.WebControls.AuthenticateEventArgs)HandlesLogin1.Authenticate DimstrConnAsString strConn="DRIVER={MySQLODBC3.51Driver};SERVER=uk.mysql5.yourwebservers.com;PORT=3306;DATABASE=boxlogin;"&_ "USER=serv;PASSWORD=b0S35;OPTION=3;" DimMyConnAsNewOdbcConnection(strConn) MyConn.Open() DimcmdAsNewOdbcCommand("Select*fromloginwhereusername=\tandpassword=",MyConn) cmd.Parameters.Add("@username",OdbcType.VarChar) cmd.Parameters("@username").Value=Me.Login1.UserName cmd.Parameters.Add("@password",OdbcType.VarChar) cmd.Parameters("@password").Value=Me.Login1.Password DimdrAsOdbcDataReader dr=cmd.ExecuteReader Ifdr.HasRowsThen e.Authenticated=True EndIf dr.Close() Ife.Authenticated=TrueThen DimservAsNewOdbcCommand("Selectserve,boxfromloginwhereusername=",MyConn) serv.Parameters.Add("@username",OdbcType.VarChar) serv.Parameters("@username").Value=Me.Login1.UserName DimruAsOdbcDataReader ru=serv.ExecuteReader DimservoAsString=ru.GetString(0) DimboxAsString=ru.GetChar(1) Me.Login1.DestinationPageUrl=servo&box ru.Close() EndIf MyConn.Close() EndSub EndClass
VIEWS ON THIS POST

88

Posted on:

Wednesday 7th November 2012
View Replies!

Opening Crystal Report from ASP

I am trying to open a crystal report from an ASP application. Crystal Report refers 2 Databases 1. SQL server 2. Excel. This is my code Set session("oApp") = Server.CreateObject("CrystalRuntime.Application") Call session("oApp").SetMorePrintEngineErrorMessages(0) Call session("oApp").SetMatchLogonInfo(1) Set session("oRpt") = session("oApp").OpenReport("Report1.rpt",1) ' Disable the Error reporting session("oRpt").MorePrintEngineErrorMessages = False session("oRpt").EnableParameterPrompting = False session("oRpt").DiscardSavedData Set oTable0 = session("oRpt").Database.Tables.Item(1) Call oTable0.SetLogOnInfo("Server","Database","UID","Pwd") 'Problem creating Code below Set oTable1 = session("oRpt").Database.Tables.Item(6) session("oRpt").Database.Tables(6).Location = "c:\Temp\TestExcel.xls" session("oRpt").Database.Tables(6).SetLogOnInfo "","","","" I am not sure how i shouldaccess Excel if Crystal Report uses. because the other crytsal report which refers to SQL Server alone works fine. Can someone please help me
VIEWS ON THIS POST

88

Posted on:

Wednesday 7th November 2012
View Replies!

oledb and excel[resolved]

, I'm a french student and I have a problem with asp.net/c# and oledb excel. I open an excel file and write in the first sheet called Sheet1 but my problem is that eveytime i write in the sheet in a specific cell the value is write only on the first line.For exemple if i want to write in the cell G15 the value that I give to the cell is write in the line G1.I show you the code that i have in my file : string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + AppDomain.CurrentDomain.BaseDirectory + "\\controle\\excel\\" + recuperation_fichier.FileName + ";Extended Properties='Excel 12.0 Xml;HDR=NO;FirstRowHasNames=NO;'"; OleDbConnection connExcel = new OleDbConnection(connectionString); connExcel.Open(); OleDbCommand cmdExcel = new OleDbCommand("UPDATE [feuil1$F3:G3] SET F1='milouse',F2='momo'",connExcel); cmdExcel.ExecuteNonQuery(); Someone can help me Excuse me for my english.
VIEWS ON THIS POST

222

Posted on:

Wednesday 7th November 2012
View Replies!

MySql .net connector Membership Provider defaulting MinRequiredNonAlphanumericCharacters

I just downloaded and installed the most recent MySQL .net connector, 6.3.6 and it connects to the DB just fine. My problem is with the web.config settings I have set for it. It seems that the MinRequiredNonAlphanumericCharacters property is hard coded to '1'. I've changed this to 0, 5, or any other number. I even setup a web page to just display the properties of the provider. If I change the values in the web.config, the values update. It seems that MinRequiredNonAlphanumericCharacters won't change. Again, the DB connection works just fine, reason I did not post the connection string. The min password length at 44 was just for testing. protected void Page_Load(object sender, EventArgs e) { MembershipProvider p = Membership.Provider; Literal1.Text = "Min Pass length: " + p.MinRequiredPasswordLength.ToString() + " Required non-alpha: " + p.MinRequiredNonAlphanumericCharacters.ToString() + "App Name: " + p.ApplicationName; } Of the three I only tested, only MinRequiredNonAlphanumericCharacters stays at '1' no matter what I change it to in the web.config. I even changed it to 'abcd' and received no .NET runtime errors. Me thinks it is hard coded some where or I'm not using the right property name in the web.config. I copied the web.config value straight from the example in the documentation that came with the MySQL .net connector.
VIEWS ON THIS POST

113

Posted on:

Wednesday 7th November 2012
View Replies!

Insert and display unicode using MySQL4.1 and ASP.Net

I'm trying to develop page in my language. I'm using unicode font. I have a form that to insert unicode text into MySQL 4.1 database. I'm inserting "\t\t \t\t" using VB.net code: MyCommand.Parameters.Add(New MySqlParameter("titleBN", Encoding.UTF8.GetBytes(txtTitleBN.Text))) After inserting it in database it's looking like "\t�\t \t\t". Now when I'm trying to read this using code: txtTitleBN.Text = Encoding.UTF8.GetString(DataReader.Item("titleBN")) I'm not getting the right value. Can anyone help me
VIEWS ON THIS POST

146

Posted on:

Wednesday 7th November 2012
View Replies!

Reading row ix from Sheet y in a xls book

Hi. I would like to know is there any way that ican read data from acertain XLS sheet starting at a Certain row I am getting spreadsheets and theyhave multiple sheetsand start on a certain row. Now what i want to do is to count the sheets and then for eachSheetstart importing fromthat certain row. Now if someone can please help me with that i would highly apreciate it. Thank You.
VIEWS ON THIS POST

98

Posted on:

Wednesday 7th November 2012
View Replies!

Writting common Dataaccess layer for MYSQL, Oracle and SQL server by using enterprise library (Parameterized query )

Hi I am developing a application, wherei mwriting a data access layer which is common for sql server, my sql and oracle Technolgies used 1) asp.net 2.0 2) Enterprise library 3.1 Parameter passing for sql query is different in SQL server and MY SQL is there a single way of passing parameter to query, which would work on MYSQl, sQL servere and Oracle database string sqlString; sqlString = " SELECT User.UserId, User.LogOnName, User.Password, From User where User.LogOnName = "; DbCommand dbCommand = database.GetSqlStringCommand(sqlString); database.AddParameter(dbCommand, "@LogOnName", DbType.String, 20, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, logOnName.ToString()); DataSet dsUserCredential = database.ExecuteDataSet(dbCommand);
VIEWS ON THIS POST

222

Posted on:

Wednesday 7th November 2012
View Replies!

Firebird Embedded UDF issue

Im having difficulties getting an ASP.NET website working with an embedded database and accessing external UDF libraries. I have searched everywhere I can think and found suggestions that do not work for me. Ive looked into where the files are placed and permissions. From what I can work out fbembed.dll must be in the c:\windows\system32 directory on the web server. I have tried placing the UDF directory in the system32 directory and in the C:\WINDOWS\system32\inetsrv directory. Ive given the ASPNET users full access to these directories. The message I continually receive is Access to UDF library "rfunc.DLL" is denied by server administrator regardless of where I put the UDF folder or the permissions I set. It would be fantastic to hear from anyone who has successfully set up an embedded Firebird database with UDFs with an ASP.NET website. I have not yet come across a sample and am beginning to wonder if such a beast exists.
VIEWS ON THIS POST

81

Posted on:

Wednesday 7th November 2012
View Replies!

Stored Procedure with Multiple Parameters

Hi , I have a stored procdure I would like to access programmatically. I normally have no problem with this with one parameter. This particular SP receives two parameters. When run, it acts likeits working but it does not execute the SP. I can execute the SP from Oracle when I manaully supply the values, but not from the application. I have a similar SP with only one parameter ahead of this and it works fine; this is why I think it might be the mulitple parameters. I can get the disigner to make it work via GUI, but I want to code this manually. BTW, how does ADO.net know which ASP parameter goes with which SP parameter when more than one Thank you, D. VB/ASP 'oleDataConnection to Oracle Dim conString As String = "Provider=MSDAORA; Data Source=*******; User ID=*******; Password=*******" Dim objConn As New OleDbConnection(conString) objConn.Open() Dim sql As String = "HISTORY_ASSESS_INSERT" Dim objCmd As New OleDbCommand objCmd.Connection = objConn objCmd.CommandText = sql objCmd.CommandType = Data.CommandType.StoredProcedure objCmd.Parameters.Add("V_APPNUMBER", OleDbType.VarNumeric).Direction = Data.ParameterDirection.Input objCmd.Parameters("V_APPNUMBER").Value = "92411" objCmd.Parameters.Add("V_OPERATOR", OleDbType.LongVarChar).Direction = Data.ParameterDirection.Input objCmd.Parameters("V_OPERATOR").Value = "testperson" 'Resource Clean-up objConn.Close() objConn.Dispose() objConn = Nothing objCmd.Dispose() objCmd = Nothing Oracle Stored Procedure create or replace PROCEDURE\t\t\t\t\t HISTORY_ASSESS_INSERT( V_APPNUMBER TBL_PERMIT.APPLICATION_NO%TYPE, V_OPERATOR TBL_EMPLOYEES.EMPLOYEEID%TYPE )AS V_PERMITTYPE TBL_PERMIT.PERMITTYPE%TYPE; V_PERMITWORKCLASS TBL_PERMIT.PERMITWORKCLASS%TYPE; V_PERMITBUILDINGUSE TBL_PERMIT.PERMITBUILDINGUSE%TYPE; V_PERMITNUMBER TBL_PERMIT.FORMATTEDPMT_NO%TYPE; V_CENSUSTRACT TBL_LOCATION.LOCCENSUSTRACT%TYPE; V_QUALIFIER TBL_QUALIFIER.QUALSEQUENCE%TYPE; V_VALUATION TBL_PERMIT.VALUATION_USED%TYPE; V_APPLICATION TBL_PERMIT_FEES.APPLICATIONSEQ%TYPE; V_FEE TBL_PERMIT_FEES.PERMITFEETYPE%TYPE; V_AMOUNTDUE TBL_PERMIT_FEES.AMOUNTDUE%TYPE; V_FLAT TBL_PERMIT_FEES.SUBAMOUNTFLAT%TYPE; V_MIN TBL_PERMIT_FEES.SUBAMOUNTMIN%TYPE; V_TIER TBL_PERMIT_FEES.SUBAMOUNTTIER%TYPE; V_UNIT TBL_PERMIT_FEES.SUBAMOUNTUNIT%TYPE; CURSOR C_TOTAL IS select APPLICATIONSEQ,PERMITFEETYPE, AMOUNTDUE, NVL(SUBAMOUNTFLAT,0) ,NVL(SUBAMOUNTMIN,0) ,NVL(SUBAMOUNTTIER,0) ,NVL(SUBAMOUNTUNIT,0) from PERMITTING.TBL_PERMIT_FEES WHERE APPLICATIONSEQ = V_APPNUMBER AND AMOUNTPAID = 0; BEGIN OPEN C_TOTAL; LOOP FETCH C_TOTAL INTO V_APPLICATION, V_FEE, V_AMOUNTDUE, V_FLAT, V_MIN, V_TIER, V_UNIT; EXIT WHEN C_TOTAL%NOTFOUND; SELECT Z.PERMITTYPE, Z.PERMITWORKCLASS, Z.PERMITBUILDINGUSE, Z.FORMATTEDPMT_NO, L.LOCCENSUSTRACT, Q.QUALSEQUENCE, Z.VALUATION_USED INTO V_PERMITTYPE, V_PERMITWORKCLASS, V_PERMITBUILDINGUSE, V_PERMITNUMBER, V_CENSUSTRACT, V_QUALIFIER, V_VALUATION FROM TBL_PERMIT Z, TBL_LOCATION L, TBL_QUALIFIER Q WHERE APPLICATION_NO = V_APPNUMBER AND L.LOCSEQUENCE = LOCATION_SEQ AND Q.QUALSEQUENCE = QUALIFIER_SEQ; INSERT INTO TBL_FEE_HISTORY (APPLICATIONSEQ,FEETYPE,TRANSACIONTCODE,TRANSDATETIME,PERMITNUMBER,PERMITTYPE,WORKCLASS,BUILDINGUSECODE,CENSUSTRACT,TRANSAMOUNT,QUALIFIERSEQ,VALUATIONAMOUNT,OPERATORINPUTID) VALUES (V_APPNUMBER, V_FEE, 'ASSESS', CURRENT_TIMESTAMP, V_PERMITNUMBER, V_PERMITTYPE, V_PERMITWORKCLASS, V_PERMITBUILDINGUSE, V_CENSUSTRACT, V_AMOUNTDUE, V_QUALIFIER, V_VALUATION, V_OPERATOR); END LOOP; CLOSE C_TOTAL; NULL; END;
VIEWS ON THIS POST

65

Posted on:

Wednesday 7th November 2012
View Replies!

ASP.net and MySql Connectivity problems

I have downloaded the driver "mysql-connector-net-1.0.7" on my c drive. I have the following code to connect to my database but keep getting this error, any help: Compiler Error Message: BC30002: Type 'MySqlConnector' is not defined. The code: Sub Page_Load(sender As Object, e As EventArgs) \t Dim myConnection As MySqlConnection \t Dim myDataAdapter As MySqlDataAdapter \t Dim myDataSet\t\t As DataSet \t Dim strSQL\t\t\t As String \t Dim iRecordCount As Integer \t myConnection = New MySqlConnection("server=localhost; user id=xxxx; password=xxxxxx; database=xxxxxx; pooling=false;") \t strSQL = "SELECT * FROM students;" \t myDataAdapter = New MySqlDataAdapter(strSQL, myConnection) \t myDataSet = New Dataset() \t myDataAdapter.Fill(myDataSet, "mytable") \t MySQLDataGrid.DataSource = myDataSet \t MySQLDataGrid.DataBind() End Sub Simple MySQL Database Query
VIEWS ON THIS POST

243

Posted on:

Wednesday 7th November 2012
View Replies!

Calling Stored Procedure

hi ..... How to call a procedure "with the use of parameters" from asp.net code to mysq\t how to execute the command\t many
VIEWS ON THIS POST

61

Posted on:

Wednesday 7th November 2012
View Replies!

Datasource In Mysql.. Not in Odbc

Hi , i Am new to the MySql Database usage in asp.net , after alot of probleams to set it up , i now having another probleam. fierst of all i cant use the odbc connectorbecuse its shoing me qustion mark , i am using utf-8 and the odbc connector dont support utf8 correctly. i need a way to use the mysql.data as my provider but if i'll try to use it it show me this thing.. Index (zero based) must be greater than or equal to zero and less than the size of the argument list. so pleas help me out here. tnx, and sorry for my english.
VIEWS ON THIS POST

65

Posted on:

Wednesday 7th November 2012
View Replies!

oci.dll problem

I cannot connect to Oracle db 8.1.7.4.0 because of error "Unable to Load (oci.dll)". I am guessing that ASPNET does not have specific rights to the directory where the file resides. However, our Oracle client is a network install, and I cannot get permissions on that drive. Is there another workaround Could I copy specific oracle files to the project directory
VIEWS ON THIS POST

302

Posted on:

Wednesday 7th November 2012
View Replies!

Lexical Parameters

Is there a way to use lexical parameters in a OracleCommand What I am trying to do is set a comma delimited list in the where clause example: where column in (:LIST) :LIST would contain something like 'A', 'B', 'C' etc. in oracle you would do this with & example where column in (&LIST)
VIEWS ON THIS POST

74

Posted on:

Wednesday 7th November 2012
View Replies!

Newbie trying to connect to an Excel spreadsheet

, I am trying to create a program that can read and write data from/to and Excel spreadsheet. I think I have connected to the spreadsheet and filled the dataset (butI am not sure because I don't knowhow to test that at thispoint)with the information from the spreadsheet but now I do not understand how to get the data out of the dataset and into variables, textboxes etc. The spreadsheet is going to hold values and when the application opens it is supposed to read the spreadsheet values and load them into a status area with textboxes and lables. When the application closes it is supposed to take any changes made and update the spread sheet.
VIEWS ON THIS POST

180

Posted on:

Wednesday 7th November 2012
View Replies!

how to increase size of the sql server 2008 database

does any one will tell me how to increase database size coz it only add 200 rows in design view...
VIEWS ON THIS POST

129

Posted on:

Wednesday 7th November 2012
View Replies!

Example

can any one send one example for how to create storedprocedure in mysql
VIEWS ON THIS POST

76

Posted on:

Wednesday 7th November 2012
View Replies!

insert a date in mysql

is there ny data type of date which can hold the date only.....coz i have a field of date in which i have write date manually......i have taken the datatype as DateTime....and its showing "incorrect datetime value"....tell me what to do
VIEWS ON THIS POST

126

Posted on:

Wednesday 7th November 2012
View Replies!

default membership provider not found.

hi I am using mysqlversion 5.1 with .net connector 5.1 in my application. my application is running correctly in mysystem. but when i upload it to my site through hosting while logining in i am geting "DEFAULT PROVIDER NOT FOUND INFORMATION." i am geting this error only when i go through login form. I had used the login control for my login control.but when i enter data from other form i.e. companyform i am able to connect to the database. i am unable to find wats wrong with my application. the hosting provided us 5.0 .Is this the version problem or any other. plz help me to solve this. thanx in advance. sushmitha.
VIEWS ON THIS POST

199

Posted on:

Wednesday 7th November 2012
View Replies!

oracle client on network drive

Hi I am trying to use Visual web dev 2005 express by connecting to an oracle database. I get the error about needing client 8.1.7. it looks like we have the client on a network drive and I was wondering if it is possible to somehow tell VWD 2005 where the files are that it needs - does anyone know here Paul
VIEWS ON THIS POST

194

Posted on:

Wednesday 7th November 2012
View Replies!

Column 'TABLE_SCHEM' does not belong to table Tables.

, I am getting this error when trying to create a SQLDataSource in Visual Web Developer 2005 EE: Database schema could not be retrieved for this connection. Please make sure the connect settings are correct and that the database is online. Column 'TABLE_SCHEM' does not belong to the table Tables. The database I am using is a relatively unknown product named Gupta SQLBase. I have an ODBC driver installed and a system DSN I configured which works just fine with other applications such as the Office 2003 suite. I am pretty sure I am configuring this data source properly, but everytime I try to open the tables I get the "Column 'TABLE_SCHEM' does not belong to the table Tables." error message. However, I am able to use this data connection in the Database Explorer window to view stored procedures.
VIEWS ON THIS POST

229

Posted on:

Wednesday 7th November 2012
View Replies!

Connecting to a Progressive database system question

Hi. I have working on a project that uses visual studio 2005 ASP.net for a web application and a progressive database as a backend. I have built the application using ADO.net as the means to connect and add new records by way on a cmd nonquery() means for various tables. I was told by company IT people that their progressive database was just like a sql serverdatabase so I began programming this as it would work with like a ASP.net application communicating with a sql server 2005 database...I am now two weeks into this project and have looming concerns for the connections and actions such as adding new rows for the various tables....I have this application currently working with the ASP.net/SQL server combination now....Does anyone know of any potential connection, cmd nonquery() differences between SQL server 2005 and the progressive version 6 database....any guidance will be greatly appreciated...
VIEWS ON THIS POST

115

Posted on:

Wednesday 7th November 2012
View Replies!

csv file to dataset

. I have a little problem to get csv import work on ado.net. Here is some code (used datainfo and dynamic schema.ini file): - schemna.ini [test2.csv] ColNameHeader=False CharacterSet=OEM Format=Delimited(,) Code: Dim constr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & DataFile.Directory.FullName & ";" & _ "Extended Properties=""text;HDR=No;IMEX=1;FMT=Delimited""" Dim con As New OleDbConnection Dim da As OleDb.OleDbDataAdapter Dim ds As New DataSet con = New OleDbConnection(constr) con.Open() Dim mycmd As New OleDb.OleDbCommand("SELECT * FROM test2#csv", con) da = New OleDb.OleDbDataAdapter(mycmd) da.Fill(ds) Problem is that i can get right data with delimiter * but cant get that work on ; or , marks, even tryed CSVDelimited on schema.ini. Error mesage says that i use same delimter than text files delimiter and decimals. Any way to get that work on
VIEWS ON THIS POST

160

Posted on:

Wednesday 7th November 2012
View Replies!

Blank shown as  

Hi all, I am using MySQL as my database. I had a record whereby some fields are empty string. But when I retrieve the records, it did not display as empty string but it show " ". May I know how to let it shown as an empty string
VIEWS ON THIS POST

65

Posted on:

Wednesday 7th November 2012
View Replies!

Terminology problem

is there an equvialent to ORACLE Materialized Views in SQL Server 2005
VIEWS ON THIS POST

110

Posted on:

Wednesday 7th November 2012
View Replies!

Deriving parameters using Oracle.DataAccess.Client (ODP.NET)

, In an effort to resolve my other posted issue, I started porting the DAAB to use ODP.NET instead of System.Data.OracleClient. I am wondering if there is any way with Oracle.DataAccess.Client to achieve the System.Data.OracleClient.OracleCommandBuilder.DeriveParameters() functionality of deriving all the parameters in a stored procedure command
VIEWS ON THIS POST

312

Posted on:

Wednesday 7th November 2012
View Replies!

variable setting in .profile

Can anyone tell me the purpose of this variable in the .profile $SERVER $LOGNAME $ORACLE_SID $PWD
VIEWS ON THIS POST

94

Posted on:

Monday 12th November 2012
View Replies!

Validation export dump files

Dear members Can someone tell me a way of validating an export dump file How can I make sure I can recover my entire database from the dump file of a full export\t
VIEWS ON THIS POST

76

Posted on:

Monday 12th November 2012
View Replies!

Unable to archive log / database hang

The key to understanding what is delaying 'log file sync' is to compare average times waited for 'log file sync' and 'log file parallel write': * If they are almost similar, then redo logfile I/O is causing the delay and the guidelines for tuning it should be followed. * If 'log file parallel write' is significantly different i.e smaller, then the delay is caused by the other parts of the Redo Logging mechanism that occur during a COMMIT/ROLLBACK (and are not I/O-related). Sometimes there will be latch contention on redo latches, evidenced by 'latch free' or 'LGWR wait for redo copy' wait events.
VIEWS ON THIS POST

67

Posted on:

Thursday 15th November 2012
View Replies!

sql functions to trim absolute path to display just filename

You can do it this way in perl script. #!/usr/bin/perl ################################################################################ # Author: Malay Biswal # Date : 2009-08-25 # ################### use strict; use constant LOOKUP => "D:/perl/perl_data/file.txt"; my $line;my@x;my $x;my $len;my $len;my $name; MAIN: { open(IN, "
VIEWS ON THIS POST

389

Posted on:

Thursday 15th November 2012
View Replies!

roles and privs

, What view do I query to in order to know what roles and privileges have been granted to users; I know the view v$pw_passwordfile only shows users who have been granted the sysdba and sysoper role, but what about the others
VIEWS ON THIS POST

147

Posted on:

Thursday 15th November 2012
View Replies!

RMAN behaves different with FORMAT cmnd

Target:Oracle 816/NT 4 - RMAN DB:Oracle 816 /NT I am trying to implement RMAN at my site. When trying different options I found different unusual scenario: If I don't specify format command, It generates one backup set with all the datafiles in one piece at default location. I thought I want specific location for my backup, so, If I specify format command with fixed filename, it errors out. Specifying format with variables (_%U), it generates two backup set. Does it mean that format command force it to generate more backup set or does it apply the restriction of filesize and force it to generate more than one files. No other options like, FILESPERSET or SET LIMIT used. For your reference, here is the actual output: ******************* First Case ******************** RMAN> run{ 2> allocate channel d1 type disk; 3> backup 4> (database include current controlfile); 5> } RMAN> list backup; RMAN-03022: compiling command: list List of Backup Sets Key Recid Stamp LV Set Stamp Set Count Completion Time ------- ---------- ---------- -- ---------- ---------- ---------------------- 2688 50 450784014 0 450783988 57 10-JAN-02 List of Backup Pieces Key Pc# Cp# Status Completion Time Piece Name ------- --- --- ----------- ---------------------- ------------------------ 2689 1 1 AVAILABLE 10-JAN-02 D:\ORACLE\ORACLE816\DATABASE\1PDDSQNK_1_1 List of Datafiles Included File Name LV Type Ckp SCN Ckp Time ---- ------------------------------------- -- ---- ---------- ------------- 1 D:\ORACLE\ORADATA\ORCL\SYSTEM\SYSTEM01.ORA 0 Full 3228966 10-JAN-02 2 D:\ORACLE\ORADATA\ORCL\USERDATA\USER01.ORA 0 Full 3228966 10-JAN-02 3 D:\ORACLE\ORADATA\ORCL\TEMPORARY\TEMPORARY01.ORA 0 Full 3228966 10-JAN-02 4 D:\ORACLE\ORADATA\ORCL\RBS\RBS01.ORA 0 Full 3228966 10-JAN-02 RMAN> ******************* Second Case ******************** RMAN> run{ 2> allocate channel d1 type disk; 3> backup 4> format 'd:\oracle\oraclebackup\rman\DATA_011002.bak' 5> (database include current controlfile); 6> } RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03007: retryable error occurred during execution of command: backup RMAN-07004: unhandled exception during command execution on channel d1 RMAN-10035: exception raised in RPC: ORA-19504: failed to create file "d:\oracle\oraclebackup\rman\D ATA_011002.bak" ORA-27038: skgfrcre: file exists OSD-04010: option specified, file already exists RMAN-10031: ORA-19624 occurred during call to DBMS_BACKUP_RESTORE.BACKUPPIECECREATE ******************* Third Case ******************** RMAN> run{ 2> allocate channel d1 type disk; 3> backup 4> format 'd:\oracle\oraclebackup\rman\DATA_%U.bak' 5> (database include current controlfile); 6> } RMAN> list backup; RMAN-03022: compiling command: list List of Backup Sets Key Recid Stamp LV Set Stamp Set Count Completion Time ------- ---------- ---------- -- ---------- ---------- ---------------------- 2708 52 450784468 0 450784456 60 10-JAN-02 List of Backup Pieces Key Pc# Cp# Status Completion Time Piece Name ------- --- --- ----------- ---------------------- ------------------------ 2710 1 1 AVAILABLE 10-JAN-02 D:\ORACLE\ORACLEBACKUP\RMAN\DATA_1SDDSR68_1_1 .BAK List of Datafiles Included File Name LV Type Ckp SCN Ckp Time ---- ------------------------------------- -- ---- ---------- ------------- 1 D:\ORACLE\ORADATA\ORCL\SYSTEM\SYSTEM01.ORA 0 Full 3229065 10-JAN-02 4 D:\ORACLE\ORADATA\ORCL\RBS\RBS01.ORA 0 Full 3229065 10-JAN-02 List of Backup Sets Key Recid Stamp LV Set Stamp Set Count Completion Time ------- ---------- ---------- -- ---------- ---------- ---------------------- 2709 53 450784488 0 450784472 61 10-JAN-02 List of Backup Pieces Key Pc# Cp# Status Completion Time Piece Name ------- --- --- ----------- ---------------------- ------------------------ 2711 1 1 AVAILABLE 10-JAN-02 D:\ORACLE\ORACLEBACKUP\RMAN\DATA_1TDDSR6O_1_1 .BAK List of Datafiles Included File Name LV Type Ckp SCN Ckp Time ---- ------------------------------------- -- ---- ---------- ------------- 2 D:\ORACLE\ORADATA\ORCL\USERDATA\USER01.ORA 0 Full 3229066 10-JAN-02 3 D:\ORACLE\ORADATA\ORCL\TEMPORARY\TEMPORARY01.ORA 0 Full 3229066 10-JAN-02 [Edited by Sharma on 01-10-2002 at 11:23 AM]
VIEWS ON THIS POST

118

Posted on:

Friday 16th November 2012
View Replies!

Recursive Statements and Nested Tables

we have a database here that has a few nested tables and there is a monthly process that the users run that reference (update, select, insert, delete) from Oracle Nested Tables. This monthly process takes an hour to run 10 out of 12 months. For the other 2 months, there's about twice as much data that it needs to process and it takes about 4 hours to run. We did a sql trace on this and then ran it through TKPROF and the tkprof shows that it is spending a LOT of time doing recursive call when compared to non-recursive calls. The recursive calls appear to be Oracle handling Nested Table maintenance. Does anyone have any suggestions on how we can reduce the amount of time Oracle spends in recursive calls when it is working with Nested Tables
VIEWS ON THIS POST

63

Posted on:

Friday 16th November 2012
View Replies!

Raw to UFS

I have been tasked with migrating a database from raw partitions to UFS. I do not want to use an export/import as this will take too long. I was thinking that I could do something with the unix dd command: e.g. dd if=/dev/vx/rdsk/datadg/datab068 count=25601 of=/spdata/volufs/dbb068 bs=4096 dd if=/dev/vx/rdsk/datadg/datab067 count=25601 of=/spdata/volufs/dbb067 bs=4096 where count= the number of blocks from dba_data_files. This appears to work on a small test system. Can anyone think of any hairy areas or gotcha's using this method! Thanx in advance..
VIEWS ON THIS POST

56

Posted on:

Friday 16th November 2012
View Replies!

professional way of database and application study...???

Hi everybody, I need one help from u all. Let me tell u first I am working for one big company in India since last 10 year. I am working as a Project Manager and Oracle DBA. I am certified Oracle 8i and Oracle 9i DBA worked on SCO-UNIX, Linux and NT We have lot of branches in India. Because of some reason we could not centralized database at mumbai and now we are going to do that in short time. Currently all the databases r distributed and all the branches sent only imp data by email and we insert those into main database. Our Development (full in house package) which we are using since last 7/8 years is working fine (we have upgraded with d2k 6i) with all the branches including operation and accounts. Now same we have to centralize. So management had decided that lets study same package from professional people. So we have already appointed some XYZ company to audit our system. We thought we will learn something professional things from them. But I think we got something really funny report. Lets not go in to deep level. I want to talk only on 1 point . We have Oracle 9i Database server with Data Guard Architecture (Stand alone in 8i).We have around 10GB Not so much. We are planning to go for Real Application Cluster. Now important point is xyz company DBA and functional manager who studied our package and database suggested (just in 15 days they took to study we call them professional) that u should use 2 different physical databases one for operation and 2nd for only accounts. As per my experience and my knowledge concern I am totally against it. I feel there will be some major disadvantages; I hope u will also contribute some so I can tell management those
VIEWS ON THIS POST

138

Posted on:

Friday 16th November 2012
View Replies!

passing dynamic values to FOR loop

Ok sorry if this seems simple but zero sleep is catching up I'd like to accomplish the following but I want to dynamically pass EMPNO and EMP to the FOR loop as opposed to hard coding them. The deed result set looks like the following with the values hard coded as below set serveroutput on CREATE OR REPLACE PROCEDURE merge_one AS BEGIN FOR x IN (SELECT empno FROM emp) LOOP dbms_output.put_line(x.empno); END LOOP; END; / exec merge_one; 7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 However, when I try and pass EMPNO and EMP dynamically I am not getting what I want. Here is what I tried set serveroutput on CREATE OR REPLACE PROCEDURE merge_two (p_table_name VARCHAR2) AS var_col_1 VARCHAR2(100); BEGIN SELECT column_name INTO var_col_1 FROM user_tab_cols WHERE table_name = p_table_name AND column_name = 'EMPNO'; FOR x IN (SELECT var_col_1 FROM p_table_name) LOOP dbms_output.put_line(x.var_col_1); END LOOP; END; / Warning: Procedure created with compilation errors. Errors for PROCEDURE MERGE_TWO: LINE/COL ERROR -------- ----------------------------------------------------------------- 6/10 PL/SQL: SQL Statement ignored 6/32 PL/SQL: ORA-00942: table or view does not exist 7/3 PL/SQL: Statement ignored 7/24 PLS-00364: loop index variable 'X' use is invalid I also tried this CREATE OR REPLACE PROCEDURE merge_three (p_table_name VARCHAR2) AS var_col_1 VARCHAR2(100); BEGIN SELECT column_name INTO var_col_1 FROM user_tab_cols WHERE table_name = p_table_name AND column_name = 'EMPNO'; FOR x IN (SELECT var_col_1 FROM user_tab_cols WHERE table_name = p_table_name) LOOP dbms_output.put_line(x.var_col_1); END LOOP; END; / exec merge_three('EMP'); resulting in EMPNO EMPNO EMPNO EMPNO EMPNO EMPNO EMPNO EMPNO any ideas tks steve
VIEWS ON THIS POST

115

Posted on:

Friday 16th November 2012
View Replies!

How to connect over ssl from ASP.net website to Informix 3.5

Setup: ASP.Net web application communicating with remote Informix DB (at the moment we are communicating over TCP and VPN but this has to change to SSL). We do not have root access to the server (shared or virtual host) I have a connection string of: "Database=****;Host=***.**.**.**;Server=*******_ssl;Service=9089;Protocol=onsocssl;UID=*******;Password=*******;". I have a self signed certificate so the website can communicate over SSL to the remote DB but I dont understand how/where to specify that the connection should use the certificate. I am getting a: ERROR [HY000] [Informix .NET provider][Informix]Secure Sockets Layer error: GSK_KEYRING_OPEN_ERROR-Keyring file did not open Any help would be good (preferably from someone who has done this kind of setup before). Also note that as I don't have root I cannot install certificates/sdks in the production environment.
VIEWS ON THIS POST

379

Posted on:

Tuesday 20th November 2012
View Replies!

How can I use GUID as userid when using MySql for Membership?

I have followed this tutorial which provides me with Membership, Role, Profile etc. http://dev.mysql.com/doc/refman/5.1/en/connector-net-tutorials-asp-roles.html Everything is working fine, but I noticed that the userid is in standard INT, not GUID. Is it possible to get around this I want the userid as GUID.
VIEWS ON THIS POST

176

Posted on:

Tuesday 20th November 2012
View Replies!

xml data file size question

I'm not sure if this is the correct forum or not, but here it goes. I'mworking on an app that is usingOracle 9 as the database. I'm connecting to the db using VB2.0 We are running into some performanceissues within theweb app. My question is, we did a data dump from the stored procedure being used and sent the output to an XML file via toad and that file came to the size of 32 mb. Then in the web app, I wrote the dataset out to an XML file as well,and noticed that the XML file from .NET is 55 mb in size and its from the same store procedure that we ran in toad. Does anyone know why there would be a size difference between the xml files
VIEWS ON THIS POST

142

Posted on:

Tuesday 20th November 2012
View Replies!

MySql Class

Long story short (as I'm sure many developers can relate) much of what I do in my apps is very repetitious... so I decided to cut down on how much repetition I'm using. I've created a class (with another on the way... you'll see what I mean) that handles all the repetitions namespace inclusions as well as method calls. What I've implemented so far is for MyODBC. I primarily use ODBC because my apps need to be more portable than what the .NET Connector provides (I also haven't used it in awhile because there were some bugs that did nasty things to my servers a while back that I'm assuming have since been corrected... but time will tell). So aside from "use a different method to chat with the database app"... Thoughs/Opinions/Advice all welcome. ImportsSystem ImportsSystem.Collections NamespaceEasyMySql NamespaceOdbc PublicClassDatabase: ImplementsIDisposable PrivateDim_ConnectionAsNewSystem.Data.Odbc.OdbcConnection(System.Configuration.ConfigurationManager.ConnectionStrings("ConnStr").ConnectionString) PrivateDim_SQLAsString PrivateDim_CommandAsNewSystem.Data.Odbc.OdbcCommand() PrivateDim_DataReaderAsSystem.Data.Odbc.OdbcDataReader PrivateDim_DataReaderRecordAsInteger=0 PrivateDim_DataReaderFieldCountAsInteger=0 PrivateDim_DataReaderResultsAsNewCollection() PublicPropertyConnectionStringAsString Get return_Connection.ConnectionString EndGet Set(ByValValueAsString) _Connection.ConnectionString=Value EndSet EndProperty PublicPropertySQL()AsString Get Return_SQL EndGet Set(ByValValueAsString) _SQL=value _Command=NewSystem.Data.Odbc.OdbcCommand(_SQL,_Connection) EndSet EndProperty PublicWriteOnlyPropertyAppendSQL()AsString Set(ByValValueAsString) _SQL+=value _Command=NewSystem.Data.Odbc.OdbcCommand(_SQL,_Connection) EndSet EndProperty PublicReadOnlyPropertyRead()AsCollection Get FillDataReader() return_DataReaderResults EndGet EndProperty PrivateSubFillDataReader() If_Connection.State=0Then _Connection.Open() _DataReader=_Command.ExecuteReader() _DataReaderFieldCount=_DataReader.FieldCount-1 EndIf DoWhile_DataReader.Read ForiAsInteger=0To_DataReaderFieldCount dimtheItemasNewListItem theItem.Text=_DataReader(i) theItem.Value=_DataReader.GetName(i) _DataReaderResults.Add(theItem) Nexti Loop _DataReader.Close() If_DataReader.IsClosedThen _Connection.Close() EndIf EndSub PublicSubReset() If_Connection.State.ToString().ToUpper()="OPEN"Then_Connection.Close() _SQL=String.Empty _Command=NewSystem.Data.Odbc.OdbcCommand(_SQL,_Connection) _DataReader=Nothing _DataReaderRecord=0 _DataReaderRowCount=0 _DataReaderFieldCount=0 _DataReaderResults=NewCollection() EndSub PublicSubNew() Reset() EndSub PublicSubNew(ByValSQLAsString) _SQL=SQL _Command=NewSystem.Data.Odbc.OdbcCommand(_SQL,_Connection) EndSub PublicSubAddParameter(ByValInputAsObject) _Command.Parameters.Add(NewSystem.Data.Odbc.OdbcParameter("",Input)) EndSub PublicFunctionGetData()AsSystem.Data.Odbc.OdbcDataReader _Connection.Open() return_Command.ExecuteReader(System.Data.CommandBehavior.CloseConnection) EndFunction PublicFunctionGetScalar()AsString _Connection.Open() dimtheValueasstring=Cstr(_Command.ExecuteScalar()) _Connection.Close() returntheValue EndFunction PublicFunctionPostData(OptionalByValReturnInsertedIDAsBoolean=False)AsInteger _Connection.Open() return_Command.ExecuteNonQuery() IfReturnInsertedID=TrueThen diminsertedIDasNewSystem.Data.Odbc.OdbcCommand("SELECT@@IDENTITY",_Connection) returninsertedID.ExecuteScalar() EndIf _Connection.Close() EndFunction ProtectedOverridesSubFinalize() Dispose() EndSub PublicOverloadsSubDispose()ImplementsIDisposable.Dispose Dispose() GC.SuppressFinalize(Me) EndSub EndClass EndNamespace NamespaceMySqlClient 'ComingNext....NETConnector... EndNamespace EndNamespace
VIEWS ON THIS POST

96

Posted on:

Wednesday 21st November 2012
View Replies!

spacing sql

I am wondering if there is a way I can change an sql statement in order to have spaces in it. I am using vb.net and an oracle database this code works sql = "SELECT SERVICED_AREA, SA_NUM, POPULATION_SERVICED from WRMD.SERVICED_AREAS WHERE LGP_NUM =" & mystring & "" conn.Open() myCommand = New OleDbDataAdapter(sql, conn) myCommand.Fill(ds, "WRMD.SERVICED_AREAS") dgServicedArea.DataSource = ds dgServicedArea.DataBind() but this code doesn't due to the spacing sql = "SELECT SERVICED_AREA, SA_NUM, to_char(POPULATION_SERVICED) as [Population Serviced]from WRMD.SERVICED_AREAS WHERE LGP_NUM =" & mystring & ""
VIEWS ON THIS POST

83

Posted on:

Wednesday 21st November 2012
View Replies!

SQL Update

Hi all - I hope I have put this question in the correct place. I am currently attempting to preform an update on an SQL database based on the click of a button. I have already established a connection to the database using DreamWeaver and have performed a select statement to populate a web page. Now I will need to update this data. I am not sure about how I can go about doing this. Here is my select statement for reference. It was built by DreamWeaver: ------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------ Here is the button that should trigger the update: ------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------ I am needing to update BG_BUG_ID with a value that was entered into a select list.
VIEWS ON THIS POST

87

Posted on:

Wednesday 21st November 2012
View Replies!

MySql Date Comparison?

Hi all, How can I get the fields where dateField = this; For example select * from tbl_customers where DOB=11/21/1980; Is this a valid way to compare the dates.I'm getting the EOF. Thanking in anticipation. M.Shahzad
VIEWS ON THIS POST

113

Posted on:

Wednesday 21st November 2012
View Replies!

Help creating stored procedures dynamically!

Does anybody know how to achieve this I tried using both Oracle and MS ODP.NET even tried OleDB and none of them seemed to work, if i do use sql*plus to compile the procedure it works fine, using the code below doesnt seem to work!! Even tried to catch up some exception but there were none. Once i run it and try to execute the SP i get an invalida object error in sql pluss. please help Dim strSql As String strSql = "CREATE OR REPLACE PACKAGE OPSPRUEBAS AS " & vbCrLf strSql = strSql & vbTab & "PROCEDURE consulta (resultado OUT VARCHAR2); " & vbCrLf strSql = strSql & "END OPSPRUEBAS; " & vbCrLf strSql = strSql & "/ " & vbCrLf strSql = strSql & ". " & vbCrLf strSql = strSql & "CREATE OR REPLACE PACKAGE BODY OPSPRUEBAS AS " & vbCrLf strSql = strSql & "PROCEDURE consulta (resultado OUT VARCHAR2) IS " & vbCrLf strSql = strSql & "oc_ref" & vbTab & vbTab & "REF Content_Ontology; " & vbCrLf strSql = strSql & "oc" & vbTab & vbTab & "Content_Ontology; " & vbCrLf strSql = strSql & "ori" & vbTab & vbTab & "role_def; " & vbCrLf strSql = strSql & "ori_ref" & vbTab & vbTab & "ref role_def; " & vbCrLf strSql = strSql & "conce" & vbTab & vbTab & "concept; " & vbCrLf strSql = strSql & "conce_ref" & vbTab & "REF concept; " & vbCrLf strSql = strSql & "b" & vbTab & vbTab & "boolean; " & vbCrLf strSql = strSql & "BEGIN " & vbCrLf strSql = strSql & "b:=false; " & vbCrLf strSql = strSql & "select REF(oi) into ori_ref from rol_table oi where oi.name='Playing'; " & vbCrLf strSql = strSql & "select deref(ori_ref) into ori from dual; " & vbCrLf strSql = strSql & "select REF(oi) into oc_ref from c_ontologies oi where oi.web_source_prop=websource('FilmContentOntology'); " & vbCrLf strSql = strSql & "select deref(oc_ref) into oc from dual; " & vbCrLf strSql = strSql & "if oc.if_has_co_class('Film')='TRUE' AND " & vbCrLf strSql = strSql & "oc.if_has_co_class('theater')='TRUE' AND " & vbCrLf strSql = strSql & "oc.if_has_co_relationship('is_scheduled')='TRUE' AND " & vbCrLf strSql = strSql & "oc.if_has_co_attributes('Film', lista('is_scheduled', 'title', 'actor'))='TRUE' AND " & vbCrLf strSql = strSql & "oc.if_has_co_attributes('theater', lista('theatername', 'address', 'email'))='TRUE' " & vbCrLf strSql = strSql & "then resultado:='FilmContentOntology'; " & vbCrLf strSql = strSql & "else resultado:='No Existe'; " & vbCrLf strSql = strSql & "end if; " & vbCrLf strSql = strSql & "END consulta; " & vbCrLf strSql = strSql & "END OPSPRUEBAS; " & vbCrLf strSql = strSql & "/ " & vbCrLf strSql = strSql & ". " & vbCrLf strSql = strSql & "COMMIT;" & vbCrLf TextBox2.Text = strSql 'Dim myConnectionString As String = "User Id=system;Password=daniel;Data Source=websogo;" 'Dim oraConn As Oracle.DataAccess.Client.OracleConnection = New Oracle.DataAccess.Client.OracleConnection(myConnectionString) Dim myCmd As New System.Data.OleDb.OleDbCommand() Dim MyConnection As New System.Data.OleDb.OleDbConnection() MyConnection.ConnectionString = "Provider=MSDAORA;Data Source=websogo;password=daniel;User ID=system" MyConnection.Open() 'oraConn.Open() myCmd.Connection = MyConnection myCmd.CommandType = CommandType.Text myCmd.CommandText = strSql myCmd.ExecuteNonQuery() 'Dim e As Oracle.DataAccess.Client.OracleException 'Try ' myCmd.ExecuteNonQuery() 'Catch e ' Dim error1 As Oracle.DataAccess.Client.OracleError ' error1 = e.Errors.Item(0) ' Textbox3.Text = error1.Source & " " & error1.Message 'End Try MyConnection.Close() End Sub
VIEWS ON THIS POST

238

Posted on:

Wednesday 21st November 2012
View Replies!

A second trigger

Relevant tables: create table messages ( msgID number(10), fromID number(10), message varchar2(1024), ToUserID number(10) default NULL, ToGroupID number(10) default NULL, dateSent date, constraint messages_pk primary key(msgID), constraint messages_fk1 foreign key(fromID) references profile(userID) on delete cascade, constraint messages_fk2 foreign key(ToUserID) references profile(userID) on delete cascade ); create table messageRecipient ( msgID number(10), userID number(10), constraint messageRecipient_pk primary key(msgID, userID), constraint messageRecipient_fk1 foreign key(msgID) references messages(msgID) on delete cascade, constraint messageRecipient_fk2 foreign key(userID) references profile(userID) on delete cascade ); create table groupMembership ( gID number(10), userID number(10), constraint groupMembership_pk primary key(gID, userID), constraint groupMembership_fk1 foreign key(gID) references groups(gID) on delete cascade, constraint groupMembership_fk2 foreign key(userID) references profile(userID) on delete cascade ); I need to create a trigger that adds corresponding records into messageRecipient when a message is sent to a group. So, for example, the sql statement insert into messages values(1, 2, 'message sent to group 3 from user 2', NULL, 3, currentdate); should add records of the form (1, userID) into messageRecipient for every (3, userID) pair in groupMembership. Here are two triggers I've tried, neither of which works. create or replace trigger SendGroupMessage after insert on messages FOR EACH ROW when (new.ToUserID is NULL) BEGIN insert into messageRecipient(msgID,UserID) select messages.msgID as msgID , groupMembership.userID as UserID from messages,groupMembership where messages.ToGroupID = groupMembership.gID; END; / and create or replace trigger SendGroupMessage after insert on messages FOR EACH ROW when (new.ToUserID is NULL) DECLARE newMsgID number(10); BEGIN newMsgID := :new.msgID; select userID as UserID from groupMembership where messages.msgID = newMsgID and messages.ToGroupID = groupMembership.gID; insert into messageRecipient values(newMsgID, UserID) END; /
VIEWS ON THIS POST

125

Posted on:

Friday 28th December 2012
View Replies!