Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

Re^12: SQL query using elements from array

by AllPaoTeam (Acolyte)
on Oct 29, 2014 at 00:10 UTC ( [id://1105397]=note: print w/replies, xml ) Need Help??


in reply to Re^11: SQL query using elements from array
in thread SQL query using elements from array

Hey it worked, below is what it spit out: 
TopsData:dbo:AUDT_AuditChrt:AuditChrt_ID:1:
CREATE DEFAULT D_TableKey AS '0000000000'

:NO:char:10:10:::::::iso_1:::SQL_Latin1_General_CP1_CI_AS:TopsData:dbo
+:T_TableKey:
TopsData:dbo:AUDT_AuditChrt:AuditChrt_RecordState:2:
CREATE DEFAULT D_Zero AS 0

:NO:tinyint:::3:10:0::::::::TopsData:dbo:T_RecordState:
TopsData:dbo:AUDT_AuditChrt:AuditDgphAlias_ID:3:
CREATE DEFAULT D_TableKey AS '0000000000'

:NO:char:10:10:::::::iso_1:::SQL_Latin1_General_CP1_CI_AS:TopsData:dbo
+:T_TableKey:
TopsData:dbo:AUDT_AuditChrt:AuditDgphType_ID:4:
CREATE DEFAULT D_TableKey AS '0000000000'

:NO:char:10:10:::::::iso_1:::SQL_Latin1_General_CP1_CI_AS:TopsData:dbo
+:T_TableKey:
TopsData:dbo:AUDT_AuditChrt:Login_ID:5:
CREATE DEFAULT D_TableKey AS '0000000000'

:NO:char:10:10:::::::iso_1:::SQL_Latin1_General_CP1_CI_AS:TopsData:dbo
+:T_TableKey:
TopsData:dbo:AUDT_AuditChrt:AuditChrt_Description:6:
CREATE DEFAULT D_EmptyString AS ''

:NO:varchar:400:400:::::::iso_1:::SQL_Latin1_General_CP1_CI_AS::::
TopsData:dbo:AUDT_AuditChrt:AuditChrt_Update:7:
CREATE DEFAULT D_Boolean AS 0

:NO:bit:::::::::::::TopsData:dbo:T_Boolean:
TopsData:dbo:AUDT_AuditChrt:AuditChrt_RecordID:8:
CREATE DEFAULT D_TableKey AS '0000000000'

:NO:char:10:10:::::::iso_1:::SQL_Latin1_General_CP1_CI_AS:TopsData:dbo
+:T_TableKey:
TopsData:dbo:AUDT_AuditChrt:AuditChrt_TimeStamp:9::YES:datetime::::::3
+::::::::::
TopsData:dbo:AUDT_AuditChrt:AuditChrt_RowGuid:10:(newid()):NO:uniqueid
+entifier::::::::::::::::
TopsData:dbo:AUDT_AuditChrt:Patient_ID:11:
CREATE DEFAULT D_TableKey AS '0000000000'

:NO:char:10:10:::::::iso_1:::SQL_Latin1_General_CP1_CI_AS:TopsData:dbo
+:T_TableKey:
TopsData:dbo:AUDT_AuditChrt:AuditChrt_Workstation:12:
CREATE DEFAULT D_EmptyString AS ''

:NO:varchar:40:40:::::::iso_1:::SQL_Latin1_General_CP1_CI_AS::::
TopsData:dbo:AUDT_AuditChrt:AuditReason_ID:13:
CREATE DEFAULT D_TableKey AS '0000000000'

:NO:char:10:10:::::::iso_1:::SQL_Latin1_General_CP1_CI_AS:TopsData:dbo
+:T_TableKey:
TopsData:dbo:AUDT_AuditMedication:AuditMedication_ID:1:
CREATE DEFAULT D_TableKey AS '0000000000'

:NO:char:10:10:::::::iso_1:::SQL_Latin1_General_CP1_CI_AS:TopsData:dbo
+:T_TableKey:
TopsData:dbo:AUDT_AuditMedication:AuditMedication_RecordState:2:
CREATE DEFAULT D_Zero AS 0

:NO:tinyint:::3:10:0::::::::TopsData:dbo:T_RecordState:
TopsData:dbo:AUDT_AuditMedication:AuditDgphAlias_ID:3:
CREATE DEFAULT D_TableKey AS '0000000000'

:NO:char:10:10:::::::iso_1:::SQL_Latin1_General_CP1_CI_AS:TopsData:dbo
+:T_TableKey:
TopsData:dbo:AUDT_AuditMedication:AuditDgphType_ID:4:
CREATE DEFAULT D_TableKey AS '0000000000'

:NO:char:10:10:::::::iso_1:::SQL_Latin1_General_CP1_CI_AS:TopsData:dbo
+:T_TableKey:
TopsData:dbo:AUDT_AuditMedication:Login_ID:5:
CREATE DEFAULT D_TableKey AS '0000000000'

:NO:char:10:10:::::::iso_1:::SQL_Latin1_General_CP1_CI_AS:TopsData:dbo
+:T_TableKey:
TopsData:dbo:AUDT_AuditMedication:Pharmacy_ID:6:
CREATE DEFAULT D_TableKey AS '0000000000'

:NO:char:10:10:::::::iso_1:::SQL_Latin1_General_CP1_CI_AS:TopsData:dbo
+:T_TableKey:
TopsData:dbo:AUDT_AuditMedication:AuditMedication_Description:7:
CREATE DEFAULT D_EmptyString AS ''

:NO:varchar:400:400:::::::iso_1:::SQL_Latin1_General_CP1_CI_AS::::
TopsData:dbo:AUDT_AuditMedication:AuditMedication_Update:8:
CREATE DEFAULT D_Boolean AS 0

:NO:bit:::::::::::::TopsData:dbo:T_Boolean:
TopsData:dbo:AUDT_AuditMedication:AuditMedication_RecordID:9:
CREATE DEFAULT D_TableKey AS '0000000000'

:NO:char:10:10:::::::iso_1:::SQL_Latin1_General_CP1_CI_AS:TopsData:dbo
+:T_TableKey:
TopsData:dbo:AUDT_AuditMedication:AuditMedication_TimeStamp:10::YES:da
+tetime::::::3::::::::::
TopsData:dbo:AUDT_AuditMedication:AuditMedication_SendMethod:11:
CREATE DEFAULT D_Zero AS 0

:NO:tinyint:::3:10:0:::::::::::
TopsData:dbo:AUDT_AuditMedication:AuditMedication_RowGuid:12:(newid())
+:NO:uniqueidentifier::::::::::::::::
TopsData:dbo:AUDT_AuditMedication:Patient_ID:13:
CREATE DEFAULT D_TableKey AS '0000000000'

:NO:char:10:10:::::::iso_1:::SQL_Latin1_General_CP1_CI_AS:TopsData:dbo
+:T_TableKey:
TopsData:dbo:AUDT_AuditMedication:AuditMedication_Workstation:14:
CREATE DEFAULT D_EmptyString AS ''

:NO:varchar:30:30:::::::iso_1:::SQL_Latin1_General_CP1_CI_AS::::

[download]
OK now what does this mean in terms of my issue?

Replies are listed 'Best First'.
Re^13: SQL query using elements from array
by mje (Curate) on Oct 29, 2014 at 11:57 UTC

    If you look at your schema you are trying to select rows based on a timestamp but the column AuditChrt_TimeStamp is a char(10). That is not going to work.

    The very first snippet of code you showed was binding '2013-08-01 00:00:00.000' which is also way too big for a char(10). I incorrectly assumed you actually had a timestamp column. In the future it sould save time if you provided the schema at the same time.

[reply]
      The output shown has been reformatted with line breaks, I think the schema is 
      AuditChrt_ID                1  char     10
AuditChrt_RecordState       2  tinyint     
AuditDgphAlias_ID           3  char     10
AuditDgphType_ID            4  char     10
Login_ID                    5  char     10
AuditChrt_Description       6  varchar  400
AuditChrt_Update            7  bit     
AuditChrt_RecordID          8  char     10
AuditChrt_TimeStamp         9  datetime     
AuditChrt_RowGuid           10 uniqueidentifier     
Patient_ID                  11 char     10
AuditChrt_Workstation       12 varchar  40
AuditReason_ID              13 char     10
               
AuditMedication_ID          1  char     10
AuditMedication_RecordState 2  tinyint     
AuditDgphAlias_ID           3  char     10
AuditDgphType_ID            4  char     10
Login_ID                    5  char     10
Pharmacy_ID                 6  char     10
AuditMedication_Description 7  varchar  400
AuditMedication_Update      8  bit     
AuditMedication_RecordID    9  char     10
AuditMedication_TimeStamp   10 datetime     
AuditMedication_SendMethod  11 tinyint     
AuditMedication_RowGuid     12 uniqueidentifier     
Patient_ID                  13 char     10
AuditMedication_Workstation 14 varchar  30

      [download]
      poj
[reply]
[d/l]

        Yes, my mistake, sorry, it does in fact look like it is a datetime and a datetime in MS SQL Server is compatible with an ODBC quoted ts.

        However, it appears I've somewhat mislead you. The SQL Server driver I use does accept ODBC timestamp escapes in bound parameters whereas the MS one appears not to. So, if you are using the ODBC escapes with your driver you'd need to concatenate them into your SQL or pass them as plain timestamp strings in a format MS accepts.

[reply]

In Section Seekers of Perl Wisdom

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?

www.com | www.net | www.org
Node Status?
node history
Node Type: note [id://1105397]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? • Last hour • Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (7)
As of 2024-04-18 16:12 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found