Search Results

Search found 3169 results on 127 pages for 'identity ranges'.

Page 15/127 | < Previous Page | 11 12 13 14 15 16 17 18 19 20 21 22  | Next Page >

  • what does select @@identity do?

    - by every_answer_gets_a_point
    i am connecting to a mysql database through excel using odbc what does this line do? Set rs = oConn.Execute("SELECT @@identity", , adCmdText) i am having trouble updating the database: With rs .AddNew ' create a new record ' add values to each field in the record .Fields("datapath") = dpath .Fields("analysistime") = atime .Fields("reporttime") = rtime .Fields("lastcalib") = lcalib .Fields("analystname") = aname .Fields("reportname") = rname .Fields("batchstate") = "bstate" .Fields("instrument") = "NA" .Update ' stores the new record End With it is ONLY updating .Fields("instrument") = "NA", but for all other fields it is putting NULL values

    Read the article

  • Sql server Identity issue

    - by pranay
    I have query like below declare @str_CustomerID int Insert into IMDECONP38.[Customer].dbo.CustomerMaster ( CustomerName , CustomerAddress , CustomerEmail , CustomerPhone ) values ( ‘werw12e’ , ‘jkj12kj’ , ‘3212423sdf’ , ‘1212121' ) select @str_CustomerID= scope_identity() after execution it returns null in my parameter i want to get value of identity how can i do that

    Read the article

  • Application installer tells that my app reads Phone State and identity :-(

    - by jdekeij
    Hoi, I have a simple app with a surfaceview nothing special one would say. However when installing on my phone I get two warnings. 1 - Phone calls - read phone state and identity 2 - Storage - modify/delete SD card content. My really is nothing more than a simple puzzle and I dont understand why I get these warnings. Any ideas how to avoid this? Help is very much appreciated, Kind regards Jasper de Keijzer.

    Read the article

  • Securing an ADF Application using OES11g: Part 2

    - by user12587121
    To validate the integration with OES we need a sample ADF Application that is rich enough to allow us to test securing the various ADF elements.  To achieve this we can add some items including bounded task flows to the application developed in this tutorial. A sample JDeveloper 11.1.1.6 project is available here. It depends on the Fusion Order Demo (FOD) database schema which is easily created using the FOD build scripts.In the deployment we have chosen to enable only ADF Authentication as we will delegate Authorization, mostly, to OES.The welcome page of the application with all the links exposed looks as follows: The Welcome, Browse Products, Browse Stock and System Administration links go to pages while the Supplier Registration and Update Stock are bounded task flows.  The Login link goes to a basic login page and once logged in a link is presented that goes to a logout page.  Only the Browse Products and Browse Stock pages are really connected to the database--the other pages and task flows do not really perform any operations on the database. Required Security Policies We make use of a set of test users and roles as decscribed on the welcome page of the application.  In order to exercise the different authorization possibilities we would like to enforce the following sample policies: Anonymous users can see the Login, Welcome and Supplier Registration links. They can also see the Welcome page, the Login page and follow the Supplier Registration task flow.  They can see the icon adjacent to the Login link indicating whether they have logged in or not. Authenticated users can see the Browse Product page. Only staff granted the right can see the Browse Product page cost price value returned from the database and then only if the value is below a configurable limit. Suppliers and staff can see the Browse Stock links and pages.  Customers cannot. Suppliers can see the Update Stock link but only those with the update permission are allowed to follow the task flow that it launches.  We could hide the link but leave it exposed here so we can easily demonstrate the method call activity protecting the task flow. Only staff granted the right can see the System Administration link and the System Administration page it accesses. Implementing the required policies In order to secure the application we will make use of the following techniques: EL Expressions and Java backing beans: JSF has the notion of EL expressions to reference data from backing Java classes.  We use these to control the presentation of links on the navigation page which respect the security contraints.  So a user will not see links that he is not allowed to click on into. These Java backing beans can call on to OES for an authorization decision.  Important Note: naturally we would configure the WLS domain where our ADF application is running as an OES WLS SM, which would allow us to efficiently query OES over the PEP API.  However versioning conflicts between OES 11.1.1.5 and ADF 11.1.1.6 mean that this is not possible.  Nevertheless, we can make use of the OES RESTful gateway technique from this posting in order to call into OES. You can easily create and manage backing beans in Jdeveloper as follows: Custom ADF Phase Listener: ADF extends the JSF page lifecycle flow and allows one to hook into the flow to intercept page rendering.  We use this to put a check prior to rendering any protected pages, again calling on to OES via the backing bean.  Phase listeners are configured in the adf-settings.xml file.  See the MyPageListener.java class in the project.  Here, for example,  is the code we use in the listener to check for allowed access to the sysadmin page, navigating back to the welcome page if authorization is not granted:                         if (page != null && (page.equals("/system.jspx") || page.equals("/system"))){                             System.out.println("MyPageListener: Checking Authorization for /system");                             if (getValue("#{oesBackingBean.UIAccessSysAdmin}").toString().equals("false") ){                                   System.out.println("MyPageListener: Forcing navigation away from system" +                                       "to welcome");                                 NavigationHandler nh = fc.getApplication().getNavigationHandler();                                   nh.handleNavigation(fc, null, "welcome");                               } else {                                 System.out.println("MyPageListener: access allowed");                              }                         } Method call activity: our app makes use of bounded task flows to implement the sequence of pages that update the stock or allow suppliers to self register.  ADF takes care of ensuring that a bounded task flow can be entered by only one page.  So a way to protect all those pages is to make a call to OES in the first activity and then either exit the task flow or continue depending on the authorization decision.  The method call returns a String which contains the name of the transition to effect. This is where we configure the method call activity in JDeveloper: We implement each of the policies using the above techniques as follows: Policies 1 and 2: as these policies concern the coarse grained notions of controlling access to anonymous and authenticated users we can make use of the container’s security constraints which can be defined in the web.xml file.  The allPages constraint is added automatically when we configure Authentication for the ADF application.  We have added the “anonymousss” constraint to allow access to the the required pages, task flows and icons: <security-constraint>    <web-resource-collection>      <web-resource-name>anonymousss</web-resource-name>      <url-pattern>/faces/welcome</url-pattern>      <url-pattern>/afr/*</url-pattern>      <url-pattern>/adf/*</url-pattern>      <url-pattern>/key.png</url-pattern>      <url-pattern>/faces/supplier-reg-btf/*</url-pattern>      <url-pattern>/faces/supplier_register_complete</url-pattern>    </web-resource-collection>  </security-constraint> Policy 3: we can place an EL expression on the element representing the cost price on the products.jspx page: #{oesBackingBean.dataAccessCostPrice}. This EL Expression references a method in a Java backing bean that will call on to OES for an authorization decision.  In OES we model the authorization requirement by requiring the view permission on the resource /MyADFApp/data/costprice and granting it only to the staff application role.  We recover any obligations to determine the limit.  Policy 4: is implemented by putting an EL expression on the Browse Stock link #{oesBackingBean.UIAccessBrowseStock} which checks for the view permission on the /MyADFApp/ui/stock resource. The stock.jspx page is protected by checking for the same permission in a custom phase listener—if the required permission is not satisfied then we force navigation back to the welcome page. Policy 5: the Update Stock link is protected with the same EL expression as the Browse Link: #{oesBackingBean.UIAccessBrowseStock}.  However the Update Stock link launches a bounded task flow and to protect it the first activity in the flow is a method call activity which will execute an EL expression #{oesBackingBean.isUIAccessSupplierUpdateTransition}  to check for the update permission on the /MyADFApp/ui/stock resource and either transition to the next step in the flow or terminate the flow with an authorization error. Policy 6: the System Administration link is protected with an EL Expression #{oesBackingBean.UIAccessSysAdmin} that checks for view access on the /MyADF/ui/sysadmin resource.  The system page is protected in the same way at the stock page—the custom phase listener checks for the same permission that protects the link and if not satisfied we navigate back to the welcome page. Testing the Application To test the application: deploy the OES11g Admin to a WLS domain deploy the OES gateway in a another domain configured to be a WLS SM. You must ensure that the jps-config.xml file therein is configured to allow access to the identity store, otherwise the gateway will not b eable to resolve the principals for the requested users.  To do this ensure that the following elements appear in the jps-config.xml file: <serviceProvider type="IDENTITY_STORE" name="idstore.ldap.provider" class="oracle.security.jps.internal.idstore.ldap.LdapIdentityStoreProvider">             <description>LDAP-based IdentityStore Provider</description>  </serviceProvider> <serviceInstance name="idstore.ldap" provider="idstore.ldap.provider">             <property name="idstore.config.provider" value="oracle.security.jps.wls.internal.idstore.WlsLdapIdStoreConfigProvider"/>             <property name="CONNECTION_POOL_CLASS" value="oracle.security.idm.providers.stdldap.JNDIPool"/></serviceInstance> <serviceInstanceRef ref="idstore.ldap"/> download the sample application and change the URL to the gateway in the MyADFApp OESBackingBean code to point to the OES Gateway and deploy the application to an 11.1.1.6 WLS domain that has been extended with the ADF JRF files. You will need to configure the FOD database connection to point your database which contains the FOD schema. populate the OES Admin and OES Gateway WLS LDAP stores with the sample set of users and groups.  If  you have configured the WLS domains to point to the same LDAP then it would only have to be done once.  To help with this there is a directory called ldap_scripts in the sample project with ldif files for the test users and groups. start the OES Admin console and configure the required OES authorization policies for the MyADFApp application and push them to the WLS SM containing the OES Gateway. Login to the MyADFApp as each of the users described on the login page to test that the security policy is correct. You will see informative logging from the OES Gateway and the ADF application to their respective WLS consoles. Congratulations, you may now login to the OES Admin console and change policies that will control the behaviour of your ADF application--change the limit value in the obligation for the cost price for example, or define Role Mapping policies to determine staff access to the system administration page based on user profile attributes. ADF Development Notes Some notes on ADF development which are probably typical gotchas: May need this on WLS startup in order to allow us to overwrite credentials for the database, the signal here is that there is an error trying to access the data base: -Djps.app.credential.overwrite.allowed=true Best to call Bounded Task flows via a CommandLink (as opposed to a go link) as you cannot seem to start them again from a go link, even having completed the task flow correctly with a return activity. Once a bounded task flow (BTF) is initated it must complete correctly  via a return activity—attempting to click on any other link whilst in the context of a  BTF has no effect.  See here for example: When using the ADF Authentication only security approach it seems to be awkward to allow anonymous access to the welcome and registration pages.  We can achieve anonymous access using the web.xml security constraint shown above (where no auth-constraint is specified) however it is not clear what needs to be listed in there….for example the /afr/* and /adf/* are in there by trial and error as sometimes the welcome page will not render if we omit those items.  I was not able to use the default allPages constraint with for example the anonymous-role or the everyone WLS group in order to be able to allow anonymous access to pages. The ADF security best practice advises placing all pages under the public_html/WEB-INF folder as then ADF will not allow any direct access to the .jspx pages but will only allow acces via a link of the form /faces/welcome rather than /faces/welcome.jspx.  This seems like a very good practice to follow as having multiple entry points to data is a source of confusion in a web application (particulary from a security point of view). In Authentication+Authorization mode only pages with a Page definition file are protected.  In order to add an emty one right click on the page and choose Go to Page Definition.  This will create an empty page definition and now the page will require explicit permission to be seen. It is advisable to give a unique context root via the weblogic.xml for the application, as otherwise the application will clash with any other application with the same context root and it will not deploy

    Read the article

  • MS SQL and Indentity column

    - by andrew007
    Hi, I just noticed that if I have an identity column in a table, when I insert new rows MS SQL 2008 is automatically filling up the sequence if there are discontinuity. I mean, if in my identity column I have 1,2,5,6 if I insert other two rows in the table the system puts automatically 3,7 in the identity column. Do you know how to control this behavior? THANKS

    Read the article

  • Accessing Google Account Id /username via Android (pre 2.0)

    - by KawBoy
    How do you access the user's Google Account Id / username in code (pre-2.0)? I am building an application that will call a web service to store data and I want to identify the identity of the person sumitting the data. If no user identity information available (pre 2.0) how about the ability to access phone identity (pre 2.0). Thanks in advance.

    Read the article

  • How can I set default seed for all identities within a SQL Server database?

    - by Brandon DuRette
    Is there a way to tell SQL server to use specific default seed value for IDENTITY columns - assuming the (to be run) CREATE TABLE statements do not specify one? I don't really care about altering existing data or altering the seed values for specific IDENTITY columns. I want the same seed for all newly created identity columns. Assume I cannot modify the individual CREATE TABLE statements in any way.

    Read the article

  • How to get several frequency ranges at the same time in fmod?

    - by Rootosaurus
    After reading documentation and tutorials about fmod I still have a question about frequency ranges. I've found how to get a frequency range using low and high pass DSP at the same time on the main channel, but I can't find how to get several ranges at the same time. For example I want, for a specific phase of my game, to hear range 400 to 600Hz and 1000 to 2000hz of the same sound. I tried to create a channel by range, but it doesn't seem to work (or maybe I do something wrong). How can I do that ? Thank you

    Read the article

  • WCF client endpoint identity - configuration question

    - by Roel
    Hi all, I'm having a strange situation here. I got it working, but I don't understand why. Situation is as follows: There is a WCF service which my application (a website) has to call. The WCF service exposes a netTcpBinding and requires Transport Security (Windows). Client and server are in the same domain, but on different servers. So generating a client results in the following config (mostly defaults) <system.serviceModel> <bindings> <netTcpBinding> <binding name="MyTcpEndpoint" ...> <reliableSession ordered="true" inactivityTimeout="00:10:00" enabled="false" /> <security mode="Transport"> <transport clientCredentialType="Windows" protectionLevel="EncryptAndSign"/> <message clientCredentialType="Windows" /> </security> </binding> </netTcpBinding> </bindings> <client> <endpoint address="net.tcp://localhost:xxxxx/xxxx/xxx/1.0" binding="netTcpBinding" bindingConfiguration="MyTcpEndpoint" contract="Service.IMyService" name="TcpEndpoint"/> </client> </system.serviceModel> When I run the website and make the call to the service, I get the following error: System.ServiceModel.Security.SecurityNegotiationException: Either the target name is incorrect or the server has rejected the client credentials. ---> System.Security.Authentication.InvalidCredentialException: Either the target name is incorrect or the server has rejected the client credentials. ---> System.ComponentModel.Win32Exception: The logon attempt failed --- End of inner exception stack trace --- at System.Net.Security.NegoState.EndProcessAuthentication(IAsyncResult result) at System.Net.Security.NegotiateStream.EndAuthenticateAsClient(IAsyncResult asyncResult) at System.ServiceModel.Channels.WindowsStreamSecurityUpgradeProvider.WindowsStreamSecurityUpgradeInitiator.InitiateUpgradeAsyncResult.OnCompleteAuthenticateAsClient(IAsyncResult result) at System.ServiceModel.Channels.StreamSecurityUpgradeInitiatorAsyncResult.CompleteAuthenticateAsClient(IAsyncResult result) --- End of inner exception stack trace --- Server stack trace: at System.ServiceModel.AsyncResult.End[TAsyncResult](IAsyncResult result) at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result) at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result) .... Now, if I just alter the configuration of the client like so: <endpoint address="net.tcp://localhost:xxxxx/xxxx/xxx/1.0" binding="netTcpBinding" bindingConfiguration="MyTcpEndpoint" contract="Service.IMyService" name="TcpEndpoint"> <identity> <dns /> </identity> </endpoint> everything works and my server happily reports that it got called by the service account which hosts the AppPool for my website. All good. My question now is: why does this work? What does this do? I got to this solution by mere trial-and-error. To me it seems that all the <dns /> tag does is tell the client to use the default DNS for authentication, but doesn't it do that anyway? Thanks for providing me with some insight.

    Read the article

  • Mocking User.Identity in ASP.NET MVC

    - by Trey Carroll
    I need to create Unit Tests for an ASP.NET MVC 2.0 web site. The site uses Windows Authentication. I've been reading up on the necessity to mock the HTTP context for code that deals with the HttpContext. I feel like I'm starting to get a handle on the DI pattern as well. (Give the class an attribute of type IRepository and then pass in a Repository object when you instantiate the controller.) What I don't understand, however, is the proper way to Mock the Windows Principal object available through User.Identity. Is this part of the HttpContext? Does any body have a link to an article that demonstrates this (or a recommendation for a book)? Thanks, Trey Carroll

    Read the article

  • Configure Active Relying Party STS to Trust Multiple Identity Provider STSes

    - by CodeChef
    I am struggling with the configuration for the scenario below. I have a custom WCF/WIF STS (RP-STS) that provides security tokens to my WCF services RP-STS is an "Active" STS RP-STS acts as a claims transformation STS RP-STS trusts tokens from many customer-specific identity provider STSes (IdP-STS) When a WCF Client connects to a service it should authenticate with it's local IdP-STS The reading that I've done describes this as Home Realm Discovery. HRD is usually described within the context of web applications and Passive STSes. My questions is, for my situation, does the logic for choosing an IdP-STS endpoint belong in the RP-STS or the WCF Client application? I thought it belonged in the RP-STS, but I cannot figure out the configuration to make this happen. RP-STS has a single endpoint, but I cannot figure out how to add more than one trusted issuer per endpoint. Any guidance on this would be very appreciated (I'm out of useful keywords to Google.) Also, if I'm way off please offer alternative approaches. Thanks!

    Read the article

  • ERROR! (Using Excel's named ranges from C#)

    - by mcoolbeth
    In the following, I am trying to persist a set of objects in an excel worksheet. Each time the function is called to store a value, it should allocate the next cell of the A column to store that object. However, an exception is thrown by the Interop library on the first call to get_Range(). (right after the catch block) Does anyone know what I am doing wrong? private void AddName(string name, object value) { Excel.Worksheet jresheet; try { jresheet = (Excel.Worksheet)_app.ActiveWorkbook.Sheets["jreTemplates"]; } catch { jresheet = (Excel.Worksheet)_app.ActiveWorkbook.Sheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); jresheet.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetVeryHidden; jresheet.Name = "jreTemplates"; jresheet.Names.Add("next", "A1", true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } Excel.Range cell = jresheet.get_Range("next", Type.Missing); cell.Value2 = value; string address = ((Excel.Name)cell.Name).Name; _app.ActiveWorkbook.Names.Add(name, address, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); cell = cell.get_Offset(1, 0); jresheet.Names.Add("next", ((Excel.Name)cell.Name).Name, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); }

    Read the article

  • :-( A valid signing identity matching this profile could not be found in your keychain

    - by user262325
    Hello everyone I hope to test my app on iPod Touch I created development provisioning profile. I dragged downloaded .mobileprovision file to Organizer There is a yellow triangle warned that "A valid signing identity matching this profile could not be found in your keychain" The others distribution provisioning profiles have no any problem. I checked my connected iPod Touch. Organizer also said that: OS Installed on "interdev"'s iPod 3.1.3 (7E18) Xcode Supported iPhone OS Versions 3.1.1 (7C146) 3.1.1 (7C145) 3.1 (7C144) 3.0.1 (7A400) 3.0 2.2.1 2.2 2.1.1 2.1 2.0.2 (5C1) 2.0.1 (5B108) 2.0 (5A347) 2.0 (5A345) ipod os 3.1.3 xocde 3.1 Do I need to upgrade Xcode? Thanks interdev

    Read the article

  • Create chart using dynamic interactive ranges to select the series in Excel 2007

    - by jhc
    I would like to create a non-VBA based solution to the following question: How do I create a multi-series chart that will allow a user to select from a dropdown to change the data being graphed? I can do this already when the data series is contiguous; however, I'd like to be able to do it for non-contiguous data. Is this possible? My data look something like this: ID Salary Sal Min Sal Mid Sal Max Division Job Grade Job Subgrade Job XXX 10000 5000 15000 25000 North 13 1 Programmer XXX 12000 5000 15000 25000 North 13 1 Programmer XXX 14000 5000 15000 25000 South 13 1 Analyst XXX 11000 5000 15000 25000 South 13 1 Analyst XXX 20000 5000 15000 25000 North 14 1 Super Programmer XXX 25000 5000 15000 25000 North 14 1 Super Programmer XXX 22000 5000 15000 25000 North 14 1 Manager XXX 17000 5000 15000 25000 South 14 1 Manager XXX 19000 5000 15000 25000 South 14 1 Manager I would like to display Salary, Sal Min, Sal Mid, and Sal Max using a line graph. I would like the user to be able to select Job Grade, Division, and/or Job to determine what is charted. Is this possible? Would I somehow be able to do this if I used a pivottable or converted my data into a datatable? Thanks.

    Read the article

  • Regex statements for date ranges <=4/1/2009 and <=10/01/2009

    - by reggiereg
    Hi, I need serious help building two Regex statements for a project. The software we're using ONLY accepts Regex for validation. I need one that fires for any date <4/1/2009 and a second that fires for any date <10/1/2009 My co-worker gave me the following code to check for <=10/01/2010, but it checks leap years and all that stuff. I need something a little more streamlined than this in the MM/DD/YYYY format. Thanks in advance! ^(?:(?:0?[1-9])|(?:1[0-2]))(\/|-|.)(?:0?[1-9]|1\d|2[0-8])(\/|-|.)(?:2[0-9][2-9][0-9])$|^(?:(?:0?[1-9])|(?:1[0-2]))(\/|-|.)(?:0?[1-9]|1\d|2[0-8])(\/|-|.)(?:201[1-9])$|^(?:(?:(?:0?[13578]|1[02])(\/|-|.)31)|(?:(?:0?[1,3-9]|1[0-2])(\/|-|.)(?:29|30)))(\/|-|.)(?:201[1-9])$|^(?:(?:(?:11)(\/|-|.))(?:0?[1-9]|1\d|2[0-9]|30)(\/|-|.))(2010)$|^(?:(?:(?:10|12)(\/|-|.))(?:0?[1-9]|1\d|2[0-9]|30|31)(\/|-|.))(2010)$|^(?:(?:0?[1-9])|(?:1[0-2]))(\/|-|.)(?:0?[1-9]|1\d|2[0-8])(\/|-|.)(?:2[0-9][2-9][0-9])$|^(?:(?:(?:0?[13578]|1[02])(\/|-|.)31)\1|(?:(?:0?[1,3-9]|1[0-2])(\/|-|.)(?:29|30)))(\/|-|.)(?:2[0-9][2-9][0-9])$|^(?:(?:0?[1-9])|(?:1[0-2]))(\/|-|.)(?:0?[1-9]|1\d|2[0-8])(\/|-|.)(?:2011)$|^(?:0?2(\/|-|.)29\3(?:(?:(?:2[0-9][1-9]\d)?(?:0[48]|[2468][048]|[13579][26])|(?:(?:16|[2468][048]|[3579][26])00))))$

    Read the article

  • Setting the Identity/Principal from a MessageInspector in WCF

    - by Robert Wagner
    I am developing a WCF service that receives the user's credentials in the SOAP header. These credentials are read on the server side using a MessageInspector. So far so good. I want to set the Thread.CurrentPrincipal to a custom principal (CustomPrincipal), but when I do this from the MessageInspector, it gets overridden by the time the service is invoked. When is the best time to set the principal? Also what is the best way to pass the principal, identity or credentials from the inspector to that location?

    Read the article

  • Parameters and Ranges of Values

    - by Randy Minder
    I need to create a report in Crystal 2008 and I've haven't developed a report using Crystal in many years. When the user runs the report, I want them to be able to enter a start date and end date. I assume I can do this by using parameters. My question is this. Can I do this with a single parameter value or will it require two parameter values? Ideally what I would like to do is to allow the user to enter a start and end date in one parameter/prompt combination. Is this possible? Thanks - Randy

    Read the article

  • displaying data between date ranges mysql

    - by Kyle Hudson
    Hi Guys, I need to work out a period of 6 months (backwards) from a given date. Example date: 07/06/2010 00:00:00 needs to count back 6 months and display: 07/12/2009 00:00:00 I have been scanning through: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html and non of these functions make sense to me :( Hope this makes sense and any help would be appriciated. Kyle

    Read the article

  • jdbc query - date ranges as parameters

    - by pstanton
    Hi all, I'd like to write a single JDBC statement that can handle the equivalent of any number of NOT BETWEEN date1 AND date2 where clauses. By single query, i mean that the same SQL string will be used to create the JDBC statements and then have different parameters supplied. This is so that the underlying frameworks can efficiently cache the query (i've been stung by that before). Essentially, I'd like to find a query that is the equivalent of SELECT * FROM table WHERE mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? and at the same time could be used with fewer parameters: SELECT * FROM table WHERE mydate NOT BETWEEN ? AND ? or more parameters SELECT * FROM table WHERE mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? AND mydate NOT BETWEEN ? AND ? I will consider using a temporary table if that will be simpler and more efficient. thanks for the help!

    Read the article

  • Separating date ranges into separate segments in SQL

    - by Richard
    I have a table containing 2 date fields and an identifier (id, fromdate and todate) These dates overlap in any and every possible way. I need to produce a list of segments each with a start and end date describing the separate segments in that list. For example: id, FromDate ToDate 1, 1944-12-11, 1944-12-31 2, 1945-01-01, 1945-12-31 3, 1945-01-01, 1945-06-30 4, 1945-12-31, 1946-05-01 5, 1944-12-17, 1946-03-30 Should produce all the segments of all the overlaps: 1, 1944-12-11, 1944-12-16 1, 1944-12-17, 1944-12-31 5, 1944-12-17, 1944-12-31 2, 1945-01-01, 1945-06-30 3, 1945-01-01, 1945-06-30 5, 1945-01-01, 1945-06-30 2, 1945-07-01, 1945-12-09 5, 1945-07-01, 1945-12-09 2, 1945-12-10, 1945-12-31 4, 1945-12-10, 1945-12-31 5, 1945-12-10, 1945-12-31 4, 1946-01-01, 1946-03-30 5, 1946-01-01, 1946-03-30 4, 1946-04-01, 1946-05-01 Or perhaps a diagram might help INPUT 1 <----> 2 <-----------> 3 <-----> 4 <----------> 5 <-----------------> OUTPUT 1 <-> 1 <-> 5 <-> 2 <-----> 3 <-----> 5 <-----> 2 <-> 5 <-> 2 <-> 4 <-> 5 <-> 4 <-> 5 <-> 4 <----> Please help

    Read the article

  • SQL Server 2008: Using Multiple dts Ranges to Build a Set of Dates

    - by raoulcousins
    I'm trying to build a query for a medical database that counts the number of patients that were on at least one medication from a class of medications (the medications listed below in the FAST_MEDS CTE) and had either: 1) A diagnosis of myopathy (the list of diagnoses in the FAST_DX CTE) 2) A CPK lab value above 1000 (the lab value in the FAST_LABS CTE) and this diagnosis or lab happened AFTER a patient was on a statin. The query I've included below does that under the assumption that once a patient is on a statin, they're on a statin forever. The first CTE collects the ids of patients that were on a statin along with the first date of their diagnosis, the second those with a diagnosis, and the third those with a high lab value. After this I count those that match the above criteria. What I would like to do is drop the assumption that once a patient is on a statin, they're on it for life. The table edw_dm.patient_medications has a column called start_dts and end_dts. This table has one row for each prescription written, with start_dts and end_dts denoting the start and end date of the prescription. End_dts could be null, which I'll take to assume that the patient is currently on this medication (it could be a missing record, but I can't do anything about this). If a patient is on two different statins, the start and ends dates can overlap, and there may be multiple records of the same medication for a patient, as in a record showing 3-11-2000 to 4-5-2003 and another for the same patient showing 5-6-2007 to 7-8-2009. I would like to use these two columns to build a query where I'm only counting the patients that had a lab value or diagnosis done during a time when they were already on a statin, or in the first n (say 3) months after they stopped taking a statin. I'm really not sure how to go about rewriting the first CTE to get this information and how to do the comparison after the CTEs are built. I know this is a vague question, but I'm really stumped. Any ideas? As always, thank you in advance. Here's the current query: WITH FAST_MEDS AS ( select distinct statins.mrd_pt_id, min(year(statins.order_dts)) as statin_yr from edw_dm.patient_medications as statins inner join mrd.medications as mrd on statins.mrd_med_id = mrd.mrd_med_id WHERE mrd.generic_nm in ( 'Lovastatin (9664708500)', 'lovastatin-niacin', 'Lovastatin/Niacin', 'Lovastatin', 'Simvastatin (9678583966)', 'ezetimibe-simvastatin', 'niacin-simvastatin', 'ezetimibe/Simvastatin', 'Niacin/Simvastatin', 'Simvastatin', 'Aspirin Buffered-Pravastatin', 'aspirin-pravastatin', 'Aspirin/Pravastatin', 'Pravastatin', 'amlodipine-atorvastatin', 'Amlodipine/atorvastatin', 'atorvastatin', 'fluvastatin', 'rosuvastatin' ) and YEAR(statins.order_dts) IS NOT NULL and statins.mrd_pt_id IS NOT NULL group by statins.mrd_pt_id ) select * into #meds from FAST_MEDS ; --return patients who had a diagnosis in the list and the year that --diagnosis was given with FAST_DX AS ( SELECT pd.mrd_pt_id, YEAR(pd.init_noted_dts) as init_yr FROM edw_dm.patient_diagnoses as pd inner join mrd.diagnoses as mrd on pd.mrd_dx_id = mrd.mrd_dx_id and mrd.icd9_cd in ('728.89','729.1','710.4','728.3','729.0','728.81','781.0','791.3') ) select * into #dx from FAST_DX; --return patients who had a high cpk value along with the year the cpk --value was taken with FAST_LABS AS ( SELECT pl.mrd_pt_id, YEAR(pl.order_dts) as lab_yr FROM edw_dm.patient_labs as pl inner join mrd.labs as mrd on pl.mrd_lab_id = mrd.mrd_lab_id and mrd.lab_nm = 'CK (CPK)' WHERE pl.lab_val between 1000 AND 999998 ) select * into #labs from FAST_LABS; -- count the number of patients who had a lab value or a medication -- value taken sometime AFTER their initial statin diagnosis select count(distinct p.mrd_pt_id) as ct from mrd.patient_demographics as p join #meds as m on p.mrd_pt_id = m.mrd_pt_id AND ( EXISTS ( SELECT 'A' FROM #labs l WHERE p.mrd_pt_id = l.mrd_pt_id and l.lab_yr >= m.statin_yr ) OR EXISTS( SELECT 'A' FROM #dx d WHERE p.mrd_pt_id = d.mrd_pt_id AND d.init_yr >= m.statin_yr ) )

    Read the article

  • Complex Join - involving date ranges and sum...

    - by calumbrodie
    I have two tables that I need to join... I want to join table1 and table2 on 'id' - however in table two id is not unique. I only want one value returned for table two, and this value represents the sum of a column called 'total_sold' - within a specified date range (say one month).. SELECT ta.id, tb.total_sold as total_sold_this_week FROM table_a as ta LEFT JOIN table_b as tb ON ta.id=tb.id AND tb.date_sold BETWEEN ADDDATE(NOW(),INTERVAL -3 WEEK) AND NOW() this works but does not SUM the rows - only returning one row for each id... how do I get the sum from table b instead of only one row??? Please criticise if format of question could use more work - I can rewrite and provide sample data if required - this is a trivialised version of a much larger problem. -Thanks

    Read the article

  • Search for a date between given ranges - Lotus

    - by Kris.Mitchell
    I have been trying to work out what is the best way to search for gather all of the documents in a database that have a certain date. Originally I was trying to use FTsearch or search to move through a document collection, but I changed over to processing a view and associated documents. My first question is what is the easiest way to spin through a set of documents and find if a date stored in the documents is greater than or less than a specified date? So, to continue working I implemented the following code. If (doc.creationDate(0) > cdat(parm1)) And (doc.creationDate(0) < CDat(parm2)) then ... end if but the results are off Included! Date:3/12/10 11:07:08 P1:3/1/10 P2: 3/5/10 Included! Date:3/13/10 9:15:09 P1:3/1/10 P2: 3/5/10 Included! Date:3/17/10 16:22:07P1:3/1/10 P2: 3/5/10 You can see that the date stored in the doc is not between P1 and P2. BUT! it does limit the documents with a date less than P1 correctly. So I won't get a result for a document with a date less than 3/1/10 If there isn't a better way than the if statement, can someone help me understand why the two examples from above are included?

    Read the article

< Previous Page | 11 12 13 14 15 16 17 18 19 20 21 22  | Next Page >