OHDSI Home | Forums | Wiki | Github

[Rabbit-In-a-Hat testing framework] Data Types

CDM Builders
#1

@schuemie - how do you tell Rabbit-In-a-Hat to use a specific data type when inserting records? Right now, when I create a record it is setting the person identifier to a character when it is a BIGINT in the raw schema.

@Chris_Knoll - I think I remember you asking Martijn about this but I don’t remember if @schuemie had a work around.

DBMS:
sql server

Error:
execute JDBC update query failed in dbSendUpdate (Error converting data type varchar to bigint.)

SQL:
INSERT INTO ses_member_detail (aso, bus, cdhp, eligeff, eligend, extract_ym, gdr_cd, group_nbr, pat_planid, patid, product, division, version, yrdob) VALUES ('N', 'COM', '3', '01/01/2000', '12/31/2015', '201603', 'F', 'ZZZPZWZJZ', 'List truncated...', '100', 'POS', 'SOUTH ATLANTIC', '6.0', '1926')
#2

I see in our own test framework code:

  statement <- paste0("INSERT INTO provider (", paste(insertFields, collapse = ", "), ") VALUES ('", paste(insertValues, collapse = "', '"), "');")

The problem I see here is that there’s no way in this line to check to see if the insertValue is numeric or not (and then not include ‘’ around the value). Is there a way to define a function ‘formatValue’ that would be able to take the insertedValue, and return the value with or without quotes, and then the collapse-paste above won’t put quotes around the value (based on some conditional formatting in the formatValue function)?

1 Like
#3

@ericaVoss: what is the error message you’re getting? So far, SQL Server has been intelligent enough to convert strings to whatever the target type is, even when using quotes.

#4

@schuemie - I included the error message in the above.

#5

I’ve seen this: on pdw, it doesn’t translate ‘35’ into an int insertion. A CONVERT would be required.

#6

If I do this on APS:

CREATE TABLE #temp (x BIGINT)
	WITH (LOCATION = USER_DB, DISTRIBUTION = REPLICATE);

INSERT INTO #temp (x) VALUES ('1');

SELECT * FROM #temp;

It works fine. Are you sure you’re haven’t declared test data with a non-int value where an int is expected?

#7

Apologies @schuemie, my recollection was wrong and you are right, as long as the value is parse-able it will insert the value. In the above, we have ‘List Truncated…’ as the pat_planid, and THIS varchar() can’t be converted into an int.

@ericaVoss: you’ll need to change the defaults for that column to something that is numeric using the function that lets you set the defaults:

set_defaults_ses_member_detail(pat_planid = "1").

-Chris

#8

@Chris_Knoll - you might be right. Let me try again from my side setting PAT_PLANID and see if I can replicate the issue again.

#9

Here is what I’m getting now:

Error executing SQL: Error in .local(conn, statement, ...): execute JDBC update query failed in dbSendUpdate (String or binary data would be truncated.)

Error:
execute JDBC update query failed in dbSendUpdate (String or binary data would be truncated.)

SQL:
INSERT INTO ses_lab_results (anlytseq, extract_ym, fst_dt, hi_nrml, labclmid, loinc_cd, low_nrml, pat_planid, patid, proc_cd, rslt_nbr, rslt_txt, source, version) VALUES ('01', '201512', '03/12/2013', '0.0000', '.', 'ZACHARY_RULES', '0.0000', '1', '402', '00450', '0.000000', 'low', 'LC', '6.0')
#10

@Chris_Knoll - NM - this is working - the ZACHARY_RULES thing was killing it. Chris you were right, if I had just fixed PAT_PLANID this problem would have gone away.

t